CODEWITHSUNDEEP
sql-server
happysmile
happysmile

Reputation: 7777

writing an Dynamic query in sqlserver

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

Upvotes: 1

Views: 852

Answers (4)

AdaTheDev
AdaTheDev

Reputation: 147224

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.

Upvotes: 1

ercan
ercan

Reputation: 1716

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

Upvotes: 2

Jojo Sardez
Jojo Sardez

Reputation: 8558

Try to replaced your SET @sqlCommand code with this:

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

Upvotes: 0

A G
A G

Reputation: 22559

'and' must come under single quote 

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

Upvotes: 2

Related Questions