July 9, 2002 at 2:59 pm
I am executing a string but somehow the variable is not known to the server. I think the string has to be parsed in pieces and then being executed.
Any ideas?
mipo
DECLARE@KeepMonthsInDatabase integer
DECLARE @ProcessTable varchar (30)
DECLARE @SelectedColumn varchar (30)
SET @ProcessTable = 'Transactions'
SET @SelectedColumn = 'SelectedField'
SET @KeepMonthsInDatabase = 3
Print @KeepMonthsInDatabase
SET @SelectedColumn = 'transactionDatestamp'
SET @ProcessTable = 'Transactions'
Print @ProcessTable
Print @SelectedColumn
DECLARE @STR nvarchar (255)
DECLARE @Tmp nvarchar (30)
SET @STR = N'IF EXISTS (SELECT 1 FROM Payments) BEGIN DELETE FROM ' + @ProcessTable + N' WHERE ' + @SelectedColumn + N' < DATEADD (m, + convert (varchar (25),-@KeepMonthsInDatabase), GETDATE()) END'
Print @STR
EXEC (@str)
Result:
3
Transactions
transactionDatestamp
IF EXISTS (SELECT 1 FROM Payments) BEGIN DELETE FROM Transactions WHERE transactionDatestamp < DATEADD (m, + convert (varchar (25),-@KeepMonthsInDatabase), GETDATE()) END
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@KeepMonthsInDatabase'.
July 9, 2002 at 3:38 pm
The problem is that you are executing a dynamic SQL statement. That runs in a separate context than the process that calls the EXEC in the first place. The variable is local only to the first process. The second one doesn't know about it. Here's how you can modify your SET statement to avoid this issue:
SET @str = N'IF EXISTS (SELECT 1 FROM Payments) BEGIN DELETE FROM '
+ @ProcessTable + N' WHERE ' + @SelectedColumn
+ N' < DATEADD (m, + convert (varchar (25),-'
+ CAST(@KeepMonthsInDatabase AS varchar) + '), GETDATE()) END'
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 07/09/2002 3:38:52 PM
Edited by - bkelley on 07/09/2002 3:39:23 PM
K. Brian Kelley
@kbriankelley
July 10, 2002 at 4:29 am
Thanks for your reply - what is the purpose of the N' in your statement?
Thanks
Mipo
July 10, 2002 at 7:10 am
When any literal string is preceded by a N, it tells SQL Server to make it an nchar/nvarchar/ntext (Unicode) string.
For instance:
SET @String = 'Is NOT a Unicode string'
SET @String = N'Is a Unicode string'
Hope that helps. SQL Server will typically do an implicit conversion from a non-Unicode to Unicode string, but every so often you see an issue with the conversion.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 10, 2002 at 2:39 pm
quote:
When any literal string is preceded by a N, it tells SQL Server to make it an nchar/nvarchar/ntext (Unicode) string.For instance:
SET @String = 'Is NOT a Unicode string'
SET @String = N'Is a Unicode string'
Hope that helps. SQL Server will typically do an implicit conversion from a non-Unicode to Unicode string, but every so often you see an issue with the conversion.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Thanks for your help
mipo
July 10, 2002 at 3:46 pm
I am getting an error when executing the following:
DECLARE@KeepMonthsInDatabase varchar (25)
DECLARE @ProcessTable varchar (30)
DECLARE @SelectedColumn varchar (30)
SET @ProcessTable = 'Transactions'
SET @KeepMonthsInDatabase = '3'
SET @SelectedColumn = 'transactionDatestamp'
DECLARE @STR nvarchar (255)
SET @STR = N'Select * FROM ' + @ProcessTable + N' WHERE ' + @SelectedColumn + N' > DATEADD (m, + convert (varchar (25), -' + CAST(@KeepMonthsInDatabase as varchar) + '), GETDATE ())'
Print @STR
EXEC (@str)
Result:
Select * FROM Transactions WHERE transactionDatestamp > DATEADD (m, + convert (varchar (25), -3), GETDATE ())
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of dateadd function.
For me it is difficult to find out what I do I have to see by printing out the string before being executed.
Argument 2 must be the number resp. the value used to increment datepart.
Thanks again for your help
mipo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply