Execute a dynamic sql statement with exec

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Execute a dynamic sql statement with exec

Author

Topic

ureyes84
Starting Member

4 Posts

Posted - 2006-05-29 : 19:02:25

HI, please help needed !!!

Can somebody tell me why the next lines:

declare @MONTH_QTY varchar (300)
set @vSQL = 'select @MONTH_QTY = '+ char(39)+ 'Value'+ char(39)
print @vSQL
Exec (@vSQL)

give the next error:

select @MONTH_QTY = 'Value'
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@MONTH_QTY'.

I need dynamic sql statements, that's why I'm using EXEC (@var) instead of a regular statement.

THANKS !!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-29 : 19:15:07

@MONTH_QTY is not declare within exec() scope. If you need to use dynamic SQL with variable, use sp_executesql instead


declare @MONTH_QTY varchar (300)
set @vSQL = 'select @MONTH_QTY = '+ char(39)+ 'Value'+ char(39)
print @vSQL
Exec (@vSQL)
exec sp_executesql @vSQL, N'@MONTH_QTY varchar (300) OUTPUT', @MONTH_QTY OUTPUT

ureyes84
Starting Member

4 Posts

Posted - 2006-05-29 : 19:25:04

Hey thank you so much for your quick reponse, but when I ran:

DECLARE @vSQL varchar (300)
declare @MONTH_QTY varchar (300)
set @vSQL = 'select @MONTH_QTY = '+ char(39)+ 'Value'+ char(39)
print @vSQL
exec sp_executesql @vSQL, N'@MONTH_QTY varchar (300) OUTPUT', @MONTH_QTY OUTPUT

I Got:

select @MONTH_QTY = 'Value'
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 5
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

I'm sorry I'm new and kind of stressed I need help

THANKS !!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-29 : 19:43:31

define the @vSQL as nvarchar(1000)

ureyes84
Starting Member

4 Posts

Posted - 2006-05-29 : 19:47:00

It's working,

Khtan you rock !!!!

I just would like to know why nvarchar instead of varchar?

Why 1000?

Thank you so much !!!!!!!!

ureyes84
Starting Member

4 Posts

Posted - 2006-05-29 : 20:00:59

Well I read the documentation, I think I got it.

Thanks Khtan