writing an Dynamic query in sqlserver

Question

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

A G · Accepted Answer · 2010-03-30 07:30:16Z

2

'and' must come under single quote

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

answered Mar 30, 2010 at 7:30

A G

22.9k12 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ercan · Accepted Answer · 2010-03-30 07:37:37Z

2

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

answered Mar 30, 2010 at 7:37

ercan

1,7171 gold badge20 silver badges34 bronze badges

Comments

AdaTheDev · Accepted Answer · 2010-03-30 07:55:15Z

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.

Jojo Sardez · Accepted Answer · 2010-03-30 07:31:22Z

0

Try to replaced your SET @sqlCommand code with this:

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

answered Mar 30, 2010 at 7:31

Jojo Sardez

8,5633 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

writing an Dynamic query in sqlserver

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related