Variable SQL statement 1

[WDfog]

I have a stored procedure that will be receiving parameters to build the WHERE clause of the SQL statement. I'm allowing users to select more than one item for each column so am having to use the IN clause. My statement looks like the following:

EXEC (N'SELECT col1, col2 FROM Table1 WHERE col1 IN ('+@var1+')')

Both col1 and col2 are varchars. I've managed to get this to work. The problem is when I throw in a date field.

EXEC (N'SELECT col1, col2 FROM Table1 WHERE col1 IN ('+@var1+') AND col3 >= ' + @varDate)

If I leave the varDate variable as a smalldatetime datatype, I receive the error: Incorrect syntax near '¬'. If I change varDate to a varchar, the data is ignored. I receive data prior to the date (less than) in the variable.

I've tried forcing the issue with a CONVERT function to no avail. Anyone have any ideas?

Thank you very much,
WDFog

 

[TomSark]

Hi,

I use double quotes for the string and single quotes for the date. What I would do I I were you would be to completely build the string prior to including it in the EXECUTE statement.

You cannot call functions in an EXEC (i.e., convert) in order to produce the string, but you can embed them within the quotes. Here's a simple example for this that you can run:

use pubs
go

declare @sql_str varchar(256)
set @sql_str = "select * from titles where pubdate >= '11/13/1998'"

execute(@sql_str)

Hope this helps...

Tom

 

[TomSark]

Just to avoid any confusion, try this:

use pubs
go

declare @sql_str varchar(256),
@date_var smalldatetime
set @date_var = '11/13/1998'


set @sql_str = "select * from titles where pubdate >= '" + convert(varchar(30),@date_var) + "'"

select @sql_str


execute(@sql_str)

 

[WDfog]

Excellent! The quotes work positively as a varchar. Plus, I stopped executing my string as a N'....' and switched to executing a variable that stores the full SQL statement.

Thank you *very* much!

 

[foxdev]

This subject seems to come up frequently, and I can think of no one more qualified to FAQ this issue than....

TOM SARK!

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Thanks... I appreciate the Kudos...

Name a child after me or something... LOL


Tom

 

[TomSark]

One more thing.. the N' simple tells SQL Server to treat the string as a unicode string... you may or may not want to include this.. I don't usually use it because I always deal with English... but it doesn't really hurt anything to use it...

Tom