Declare variable for dynamic SQL

[avu]

Hello,

Can someone please help me with this problem? Below is my sample code:

DECLARE @dynVar1 varchar(2)
DECLARE @dynVar2 varchar(2)
DECLARE @dynVar3 varchar(2)
DECLARE @runSQL varchar(500)
DECLARE @myVariable varchar(50)

SET @runSQL = ('SELECT @dynVar1=SomeField2, @dynVar2=SomeField2, @dynVar3=SomeField3
FROM MyDB.dbo.' + @dynTBL + ' WHERE myField = ''' + @myCriteria + '''')
EXEC(@runSQL)
IF @dynVar1 = '' --IS NULL
SET @myVariable = 'something'
ELSE
BEGIN
IF @dynVar2 = '' --IS NULL
SET @myVariable = 'something_else1'
ELSE
BEGIN
IF @dynVar3 = '' --IS NULL
SET @myVariable = 'something_else2'
ELSE
BEGIN
IF @dynVar3 = '01'
SET @myVariable = 'something_else3'
END
END
END


When I tried to run this from Query Analyzer (MS SQL 2000), I'd get error:
"Must declare the variable '@dynVar1'."

(The error must be at line "IF @dynVar1 = '' --IS NULL", I think)

Can someone please point out what my errors are in the code? I have to use "SET @runSQL ..." and "EXEC(@runSQL)" because the table to call from is dynamic and will be defined at run time.

Any help will be appreciated.

Thanks.

Allan

 

[jbenson001]

You are declaring your variables outside of the dynamic sql you are running. You need to include it in your @runSQL var


delcare @runSQL NVarChar(2000)
@runSQL = 'DECLARE @dynVar1 varchar(2) ..etc
.. your code .... '

then execute it.

To execute your dynamic SQL it must be of type NVarChar.

Check BOL for more info and examples

Jim

 

[avu]

Thanks Jim, but that's still not right. I've changed the script to:

SET @runSQL = ('DECLARE @dynVar1 varchar(2), @dynVar2 varchar(2), @dynVar3 varchar(2) ' +
SELECT @dynVar1=SomeField2, @dynVar2=SomeField2, @dynVar3=SomeField3
FROM MyDB.dbo.' + @dynTBL + ' WHERE myField = ''' + @myCriteria + '''')

but it still gives me the error "Must declare variable @dynVar1"

Any other suggestions?

 

[jbenson001]

You are missing qutoes when constructing your string

ex: you have no quotes around Select .....

Construct the string, then before executing it, print it out to make sure it is correct.

Jim

 

[avu]

Sorry, I do have the quote on my actual code.

SET @runSQL = ('DECLARE @dynVar1 varchar(2), @dynVar2 varchar(2), @dynVar3 varchar(2) ' +
'SELECT @dynVar1=SomeField2, @dynVar2=SomeField2, @dynVar3=SomeField3
FROM MyDB.dbo.' + @dynTBL + ' WHERE myField = ''' + @myCriteria + '''')

I still get the error though.

Any other thoughts?

Thanks.

 

[jbenson001]

This @dynTBL is not declared.

 

[jbenson001]

Also you can declare like this:

'DECLARE @dynVar1 varchar(2), @dynVar2 varchar(2), @dynVar3 varchar(2)

You need a separate DECLARE for each variable

Declare @dynVar1 varchar(2),
Declare @dynVar2 varchar(2),
.... etc

 

[jbenson001]

oops ignore my last post.. you can do that.. sorry