Why does my Dynamic SQL error out while checking not null condition?

Question

I am trying to pass country_name and country_code column In my country table as a variable. I want to return 1 in case the conditions are true.

SELECT  @Column1 = N'country_name'
SELECT  @Column2 = N'country_code'

SELECT @sql = N'SELECT ' +'1' +' FROM country where' + @Column1 + 'is not null and' + @Column2 + 'is not null'
EXEC sp_executesql @sql

Error Code:

Msg 156, Level 15, State 1, Line 1418 Incorrect syntax near the keyword 'not'.

could someone help me with this?

Answer 1

Looks like you might be missing some spaces before and after @Column1 and @Column2. I.e., you current SQL would look a bit like this right?

'SELECT 1 FROM country wherecountry_nameis not null andcountry_codeis not null'

An update as follows should do the trick I imagine:

SELECT  @Column1 = N'country_name'
SELECT  @Column2 = N'country_code'

SELECT @sql = N'SELECT ' + '1' + ' FROM country where ' + @Column1 + ' is not null and ' + @Column2 + ' is not null'
EXEC sp_executesql @sql