writing an Dynamic query in sqlserver

Question

hi,

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
DECLARE @region varchar(75)
SET @columnList = 'first_name, last_name, city'
SET @city = '''London'''
SET @region = '''South'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM dbo.employee WHERE City = ' + @city   and 'region = '+@region
--and 'region = '+@region
print(@sqlCommand)
EXEC (@sqlCommand)

when i run this command i get an error

Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'and'.

and help would great thank you

Answer 1

'and' must come under single quote

@sqlCommand = 'SELECT ' + @columnList + ' FROM dbo.employee WHERE City = ' +  @city  + 'and region = '  + @region 

Answer 2

Try to replaced your SET @sqlCommand code with this:

SET @sqlCommand = 'SELECT ' + @columnList + ' FROM dbo.employee WHERE City = ' + @city  + ' and  region = '+@region 

Answer 3

A must read for everybody who use dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

Answer 4

I'd recommend using parameterised SQL to help guard against sql injection, and to support execution plan reuse. So assuming @columnList is fully validated and therefore guaranteed to not include anything dodgy:

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
DECLARE @region varchar(75)
SET @columnList = 'first_name, last_name, city'
SET @city = 'London'
SET @region = 'South'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM dbo.employee WHERE City = @city AND region = @region'
print(@sqlCommand)

EXEC sp_executesql @sqlCommand, N'@city varchar(75), @region varchar(75)', @city, @region

I'm assuming that your situation isn't exactly as outlined as it would be better to not use dynamic sql at all otherwise.