Dynamic SQL Problem

Question

Post reply

Dynamic SQL Problem

mipo

Ten Centuries

Points: 1346
More actions
July 11, 2002 at 3:28 pm

#79402

I still can't get it to run this simple dynamic sql construct - I still have an error:
DECLARE@KeepMonthsInDatabase varchar (12)
DECLARE @ProcessTable varchar (30)
DECLARE @SelectedColumn varchar (30)
DECLARE @STR nvarchar (255)
SET @ProcessTable = 'Transactions'
SET @KeepMonthsInDatabase = '3'
Print @KeepMonthsInDatabase
SET @SelectedColumn = 'transactionDatestamp'
SET @STR = 'Select * FROM ' + @ProcessTable + ' WHERE ' + @SelectedColumn + ' > DATEADD (m, + convert (varchar (25), ' + CAST (@KeepmonthsInDatabase as varchar) + '), + GETDATE())'
Print @STR
EXEC (@str)
Result:
3
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.
Do I face an incompatibilty between different datatypes or is my approach wrong?
I simply want to use the variable @KeepMonthsInDatabase in this dynamic sql statement.
Maybe someone has a clue for me?
Thanks
mipo

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Robert W Marda SSChampion Points: 13413 More actions · Answer 1

Will this do what you want:

DECLARE @KeepMonthsInDatabase varchar (12)

DECLARE @ProcessTable varchar (30)

DECLARE @SelectedColumn varchar (30)

DECLARE @STR nvarchar (255)

SET @ProcessTable = 'Transactions'

SET @KeepMonthsInDatabase = '3'

Print @KeepMonthsInDatabase

SET @SelectedColumn = 'transactionDatestamp'

SET @STR = 'Select * FROM ' + @ProcessTable + ' WHERE ' + @SelectedColumn + ' > DATEADD (m, + convert (varchar (25), ' + CAST (@KeepmonthsInDatabase as varchar) + '), + GETDATE())'

Print @STR

EXEC (@str)

Robert Marda

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

mipo Ten Centuries Points: 1346 More actions · Answer 2

This is just an example of my sql code. I reduced it down to find the problem how to embed the variable @Keepmonthsindatabase in this dynamic sql statement.

The original idea was counting back (now it is goint into the future with my example) the months according the variable from present and then compare data with it.

mipo

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Try this:

SET @STR = 'Select * FROM ' + @ProcessTable + ' WHERE ' + @SelectedColumn + ' > DATEADD (m, ' + @KeepmonthsInDatabase + ', + GETDATE())'

In my original posting I suggested casting @KeepmonthsInDatabase to varchar, because sometimes you see SQL Server trying to cast character columns to integer when the + is used. One of the drawbacks of not differentiating, like with & and + in Visual Basic and VBScript. In any case, the problem is the extra convert you've added within the quotes.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley