Exec statement - 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

Transact-SQL (2000)

Exec statement

Author

Topic

misterraj
Yak Posting Veteran

94 Posts

Posted - 2005-04-04 : 05:50:09

When I try to execute this SP, I get the error "Must declare the variable '@return'."

This SP compiles without errors, but while executing I get this error.
The thing is that I am framing a string and dynamically executing thru exec stmt.

can any one throw any ideas on this ..

create proc test
as
begin
declare @string varchar(100)
DECLARE @return varchar(100)

select @string = 'exec test1 @return output'
print @string
EXEC (@string)
end

venkat

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-04 : 06:17:01

use sp_executesql for this.
look it up in BOL.

Go with the flow & have fun! Else fight the flow

misterraj
Yak Posting Veteran

94 Posts

Posted - 2005-04-04 : 06:25:58

How to use this? I tried but i couldnot get it

misterraj
Yak Posting Veteran

94 Posts

Posted - 2005-04-04 : 06:31:01

Tried, but same error.

alter proc test
as
begin
DECLARE @return nvarchar(100)
declare @string nvarchar(100)
declare @ret int

select @return='@return'
select @string =' test1 @return output' -- @return output'
print @string

EXECUTE sp_executesql @string

print @return

end

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-04 : 06:33:23

create proc test
as
begin
declare @string varchar(100)
DECLARE @return varchar(100)

select @string = 'exec test1 ' + @return + 'output'
print @string
EXEC (@string)
end

--------------------
keeping it simple...

misterraj
Yak Posting Veteran

94 Posts

Posted - 2005-04-04 : 06:39:39

No, i tried this, it doesnot work up!, infact executes without errors, but doesnot return anything...

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-04 : 06:52:43

quote:
Originally posted by jen

create proc test
as
begin
declare @string varchar(100)
DECLARE @return varchar(100)

select @string = 'exec test1 ' + @return + 'output'
print @string
EXEC (@string)
end

--------------------
keeping it simple...

Using Jens reply try putting a leading space before output.

select @string = 'exec test1 ' + @return + ' output'

Andy

Beauty is in the eyes of the beerholder

misterraj
Yak Posting Veteran

94 Posts

Posted - 2005-04-04 : 07:03:11

Hmm, i did. (I am not that much dumb also..)

misterraj
Yak Posting Veteran

94 Posts

Posted - 2005-04-04 : 07:09:45

still not working..

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-04 : 07:15:19

Post exactly what you tried this last time. And, just for kicks and grins, post the code for test1 or whatever proc you're calling with this dynamic SQL.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-04 : 07:53:59

i believe this will help you:
http://weblogs.sqlteam.com/brettk/archive/2005/01/27/4029.aspx

Go with the flow & have fun! Else fight the flow

misterraj
Yak Posting Veteran

94 Posts

Posted - 2005-04-04 : 08:25:22

ok, will state the problem once again..
When I try to execute this SP, I get the error "Must declare the variable '@return'."

This SP compiles without errors, but while executing I get this error.
The thing is that I am framing a string and dynamically executing thru exec stmt.

can any one throw any ideas on this ..

create proc test
as
begin
declare @string varchar(100)
DECLARE @return varchar(100)

select @string = 'exec test1 @return output'
print @string
EXEC (@string)
end

test1- sp
---------

CREATE proc test1
(
@RETURN_CODE VARCHAR(5) = null OUTPUT
)
as
begin

select @RETURN_CODE ='test1'

end

Hope I have given everything...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-04 : 09:19:25

Try this

create proc test
as
begin
declare @string varchar(100)
DECLARE @return varchar(100)
select @string = 'exec test1 '+@return+' output'
print @string
EXEC (@string)
end

Madhivanan

Failing to plan is Planning to fail

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 12:06:46

You are encountering the error when the dynamic SQL statement is being executed and within your dynamic SQL statement, you are referencing @return which is not declared within the dynamic statement. Declaring it within the stored procedure is no use because that declaration is not seen by the dynamic statement.

Try this one and see if this will solve your problem and get the desired result:

create proc test
as
begin
declare @string nvarchar(100)
DECLARE @return varchar(100)

select @string = 'exec test1 @return output'

execute sp_executesql @string, N'@return varchar(100) OUT', @return OUT

PRINT @return
end

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-04 : 22:58:51

quote:
Originally posted by misterraj

No, i tried this, it doesnot work up!, infact executes without errors, but doesnot return anything...

coz @return doesn't contain anything, provide a value

--------------------
keeping it simple...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-04 : 23:19:39

Actually, it was because @return hadn't been declared in the context of the dynamic SQL compilation and execution.

Remember, that a local variable is ONLY valid for it's current context. Since dynamic SQL is executed within a seperate context, all variables used must be declared and populated inside the individual statements. That's why '' + @variable + '' works when ' @variable ' does not.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-04 : 23:27:30

Why are you using dynamic SQL, instead of just executing the stored procedure directly? Do you not know what procedure you want to execute?

What is wrong with doing this?

create proc test
as
begin
declare @return varchar(100)
exec test1 @return output
print @return
end

Or just this?

declare @return varchar(100)
exec test1 @return output
print @return

CODO ERGO SUM

misterraj
Yak Posting Veteran

94 Posts

Posted - 2005-04-05 : 04:32:20

rfransisco, Thanks your answer worked out!!!!..Others also thanks for your efforts..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources