March 31, 2009 at 7:01 am
I have been tasked with created archive tables each time we delete records from a production database (yeah, the client interface allows a whole host of BAD data)
I came up with the following, but completely stuck at this point
SELECT * INTO @NewTableName FROM CLAttachedFiles WHERE ClaimNumber = @ClaimNumber AND DocID = @Docid
This worked until I tried to put the @NewTableName in there which requires dynamic sql of course so......
SET @dynSQL = 'SELECT * INTO '
SET @dynSQL = @dynsql + @NewTableName
SET @dynSQL = @dynsql + ' WHERE ClaimNumber = @ClaimNumber AND DocID = @DocID'
Now the problem is the @ClaimNumber and @DocID
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@ClaimNumber'.
The variable is declared!!!!! It works fine when not dynamic.
Any help would be greatly appreciated.
March 31, 2009 at 7:11 am
Hi,
Instead try appending the vairables in a string and use it with sp_Executesql.
Regards,
Sriram
Sriram
March 31, 2009 at 7:12 am
This might help.
SET @dynSQL = 'SELECT * INTO '
SET @dynSQL = @dynsql + @NewTableName
SET @dynSQL = @dynsql + ' WHERE ClaimNumber = ' + CAST(@ClaimNumber AS VARCHAR) + ' AND DocID = ' + CAST(@DocID AS VARCHAR)
PS. I do not know if using dynamic SQL is the best way to go, but can not propose a better solution because do not know your environment. I tend to use dynamic SQL as a last resort because you may open yourself for runtime compile errors with it.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 31, 2009 at 7:26 am
Thanks to all. That did the trick. I new I was missing something obvious.
As for our environment, these scripts are run manually from query analyzer, so I dynamic sql an OK alternative in this sense?
March 31, 2009 at 7:29 am
suzanne (3/31/2009)
As for our environment, these scripts are run manually from query analyzer, so I dynamic sql an OK alternative in this sense?
Yes, I think that would be fine.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply