CODEWITHSUNDEEP
t-sqlstored-procedures
MalcolmK
MalcolmK

Reputation: 31

How do you create a SQL Where Clause using fields in a dynamic query?

This is a small sample of a larger query I am trying to run. It is a dynamic query so I put the entire thing into a string and then execute it. However, it is failing. The user sends in just the month and year. I then execute it. But it is telling me the fields can not be bound.

declare @sqlstr varchar(5000)
set @sqlstr = 'SELECT     FirstName, LastName FROM  MyTable Where '
set @sqlstr = @sqlstr + CONVERT(varchar(2), MyTable.MyDateMonth) + '/1/' + CONVERT(varchar(4), MyTable.MyDateYear)
set @sqlstr = @sqlstr + ' = ''8/1/2006'''

exec(@sqlstr)

Upvotes: 1

Views: 126

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

You're missing some quotes around the CONVERTs

set @sqlstr = @sqlstr + 'CONVERT(varchar(2), MyTable.MyDateMonth) + ''/1/'' + CONVERT(varchar(4), MyTable.MyDateYear)'

Adding print @sqlstr can help you debug this.

Also you're exposing yourself to a SQL injection so you should sp_ExceuteSQL instead of just exec

Upvotes: 3

Related Questions