June 29, 2006 at 2:38 am
I am trying to create a dynamic where clause in an SPROC but i am doing something wrong with the parameters that i am passing but can't figure out what it is, my example seems to follow all the guidelines in the documentation and examples on line but it will not substitute out variable for it's contents. Any help would be greatly appreciated.
Here is the section of my code:
set @sql = N'insert into #temp
select distinct gdb.GD_Banner_ID, Bannername retailer
from gd_banners gdb left join gd_banner_countries gdc
on gdb.gd_banner_id = gdc.gd_banner_id
left join gd_banner_formats gdf
on gdb.gd_banner_id = gdf.gd_banner_id @where order by bannername'
set @param1 = N'@where nvarchar(4000)'
EXECUTE sp_executesql @sql, @param1, @where = @whereclause
@whereclause has my pre-built where clause
the error i keep getting is :
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@where'.
Can anyone shed any light on this?
Dan
June 29, 2006 at 3:42 am
You cannot use a variable for the where clause, you must concatenate the variable with the dynamic T-SQL. Example:
set @sql = N'insert into #temp
select distinct gdb.GD_Banner_ID, Bannername retailer
from gd_banners gdb left join gd_banner_countries gdc
on gdb.gd_banner_id = gdc.gd_banner_id
left join gd_banner_formats gdf
on gdb.gd_banner_id = gdf.gd_banner_id '+@whereclause
+' order by bannername'
You will also have trouble with the #temp temporary table as it does not exist in the context of the EXEC, so use:
set @sql = N'select distinct gdb.GD_Banner_ID, Bannername retailer
from gd_banners gdb left join gd_banner_countries gdc
on gdb.gd_banner_id = gdc.gd_banner_id
left join gd_banner_formats gdf
on gdb.gd_banner_id = gdf.gd_banner_id '
+@whereclause+' order by bannername'
insert into #temp EXEC (@sql)
Andy
June 29, 2006 at 4:07 am
Hi Andy thanks for the response. I was trying to use this way to ensure the query plan was cached so that hopefully there would be a performance gain. Even though i know you can't do it for my personal interest do you know why you cannot use a variable for the where clause?
Thanks again
Dan
June 29, 2006 at 5:34 am
Open the master..sp_executesql stored procedure in EM, and look at the code to see why. EXEC is the same.
Use QA and try and run your select with a variable where clause, nope does not work there either.
For a better explaination I think that the SQL development team would have to comment.
It would be "nice" if it would work, but even if it would, I doubt if this would "help" the execution plan.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply