Dynamic sql statement
-
Hi,
I have a table named Table1 with a column called FixedFilterData which holds a T-SQL syntax filter.
I want to be able to retrieve this filter and make it part of my where clause in a dynamic sql statement.
Example:
DECLARE @FixedFilterData VARCHAR(255)
DECLARE @PassedDate DATETIME
DECLARE @strSQL NVARCHAR(1000)
SET @PassedDate = GETDATE()
SELECT @FixedFilterData = FixedFilterData FROM dbo.Table1
Result: DateDiff(hh,TransactionDate, CONVERT(VARCHAR(20),@PassedDate,120)<24
NOW when I build and execute the follwing statement:
SET @strSQL = 'SELECT * ' + CHAR(13) + 'FROM Table1' + CHAR(13)
SET @strSQL = @strSQL + CHAR(13) + 'WHERE ' + @FixedData
I get "Must declare the variable '@PassedDate'"
HOWEVER when I set the
@FixedFilterData = 'DateDiff(hh,TransactionDate,'+ ''''+ CONVERT(VARCHAR(20),@PassedDate,120)+''')>24' it executes fine.
Why and how can i amend the code to parse and set the @PassedDate to its value? I have looked at previous threads on this topic and in BOL and still have not solved the problem.
Thanks in advance for your help.
LeopoldNjampa
-
use sp_executesql to passing @PassedDate.
Here is the modified code:
DECLARE @FixedFilterData VARCHAR(255)
DECLARE @PDate DATETIME
DECLARE @strSQL NVARCHAR(1000)
SET @PDate = GETDATE()
SELECT @FixedFilterData = FixedFilterData FROM dbo.Table1
SET @strSQL = 'SELECT * ' + CHAR(13) + 'FROM Table1' + CHAR(13)
SET @strSQL = @strSQL + CHAR(13) + 'WHERE ' + @FixedData
exec sp_executesql @strSQL, N'@PassedDate datetime', @PDate
-
Thanks wz700.
This is another way to 'solve' the problem. Simply replace the "unparsed" @PassedDate parameter in the @FixedFilterData with the parsed value of the input parameter.
DECLARE @XDate VARCHAR(30)
DECLARE @PassedDate DATETIME
SELECT @XDate = CHAR(39)+CONVERT(VARCHAR(20),@PassedDate,120)+CHAR(39)
SELECT @FixedFilterData = FixedFilterData FROM dbo.Table1 WHERE Rowid=1
-- now the replace
SELECT @FixedFilterData = REPLACE(@FixedFilterData,'@PassedDate',@XDate)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply