rferguson (1/18/2009)
Hi Grant, the stored procedure already uses the sp_executesql function. What I'm essentially trying to acheive is to create a dynamic SQL statement, but in which the WHERE clause is different in each case and also uses variables for the date ranges. The sticking point for me is to sending the variable dates to the stored procedure within the "text" of the dynamic SQL statement.Starting to find ow this becomes increasingly difficult without adding a second line of code to set these variables first as EXECUTE won't allow expressions.
Richard.
But using sp_executesql you can also pass it variables. You simply write the query, dynamic or not, like this:
DECLARE @sql NVARCHAR(MAX), @paramdef NVARCHAR(MAX)
SET @sql = 'SELECT * FROM MyTable WHERE Date BETWEEN @Var1 and @Var2'
SET @paramdef = '@Var1 DATETIME, @Var2 DATETIME'
EXEC sp_executesql @sql,@paramdef,@Var1='1/1/2009',@Var2='1/31/2009'
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning