dynamic sql error - SQL Server Forums

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

SQL Server Development (2000)

dynamic sql error

Author

Topic

purisqlserver
Yak Posting Veteran

73 Posts

Posted - 2003-03-10 : 10:10:20

hi,

I am try to work with code below, to store value in the variable
@var_Last_Sl_No1 dynamically.

Declare @var_Last_Sl_No1 char(5),@x nvarchar(1000)
Set @x='Select @var_Last_Sl_No1=Last_Sl_No from xx.dbo.Serial_Number Where Type='+''''+'S'+''''
Select @x
Exec(@x)
Select @var_Last_Sl_No1

but eroor is given 'must declare the variable @var_Last_Sl_No1

thanx

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-10 : 10:39:45

If you want to pass variable into or out of a dynamic sql batch, you must use the master.dbo.sp_executesql system stored procedure.

Read Books Online for more.

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-10 : 10:58:03

And for my usual question in these cases:

Why are you using dynamic SQL ?

I don't follow your logic with the two variables and what you are trying to return, but why not something like:

Declare @var_Last_Sl_No1 char(5)

SET @var_Last_S1_No1 = (SELECT Last_S1_No FROM xx.dbo.Serial_Number WHERE Type = 'S')

- Jeff

Edited by - jsmith8858 on 03/10/2003 10:58:44

purisqlserver
Yak Posting Veteran

73 Posts

Posted - 2003-03-10 : 13:45:21

thanx for the reply.

I am actually passing the database name dynamically and hence the qry.
something like this:

Declare @var_Last_S1_No1 char(5) ,@ven char(6),@x varchar(1000)
set @ven='test'
set @x='SELECT Last_Sl_No FROM '+@ven+'.dbo.Serial_Number WHERE Type ='+''''+'S'+''''
Select @x
SET @var_Last_S1_No1=exec(@x)
select @var_Last_S1_No1

It gives error near exec, how do i trap the value.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-10 : 13:54:30

quote:

It gives error near exec, how do i trap the value.

Read my post.

Jay White
{0}

purisqlserver
Yak Posting Veteran

73 Posts

Posted - 2003-03-11 : 01:36:50

thanx for help, got the code .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources