Output of "exec" into a variable in a Stored Proc

[uday murthy]

Hi all!
Can someone tell me what is wrong with this proc....I really HAVE to
code it this way. It seems that assigning the output of "exec" to a
local variable is just not allowed. Please help

________________________________________________________
CREATE PROCEDURE TEST @strDates varchar(500), @instead varchar(5) AS
DECLARE @teststr varchar(255), @second varchar(255)

set @teststr = "SELECT REPLACE(@strDates,'ABA',@Instead)"
set @second = exec (@teststr)
print @teststr

Error 156: Incorrect syntax near the keyword 'exec'
________________________________________________________

Regards

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[praveen maddali]

Hi Uday,

You can't assign the output of "exec" statement to a local variable.

Instead You can specify OUTPUT Parameters for a stored procedure and
assign it to a variable.

In your case , you can simplify your code as

CREATE PROCEDURE TEST @strDates varchar(500), @instead varchar(5) AS
DECLARE @teststr varchar(255), @second varchar(255)

SELECT @teststr = REPLACE(@strDates,'ABA',@Instead)
print @teststr

Praveen Maddali
MCDBA

[Dan Guzman]

You can use sp_executesql to assign output values with dynamic SQL. For
example:

EXEC sp_executesql
N'SELECT @second = REPLACE(@strDates, ''ABA'', @Instead)',
N'@second varchar(255) OUTPUT, @strDates varchar(500), @Instead
varchar(5)',
@second OUTPUT, @strDates = @strDates, @Instead = @Instead

There may be better ways of accomplishing the desired result, though.
I'm not sure what you mean by "I really HAVE to code it this way". Can
you elaborate?

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"uday murthy" <uday...@yahoo.com> wrote in message
news:3c6b6f8b$0$68185$7586...@news.frii.com...

[David Russ, CNE]

What about....

CREATE PROCEDURE TEST @strDates varchar(500), @instead varchar(5),
@teststr varchar(500) OUTPUT AS
SET NOCOUNT ON

DECLARE @second varchar(255)

set @teststr = REPLACE(@strDates,'ABA',@Instead)
set @second = @teststr

SELECT @teststr

This way you get it as an output parameter and as a recordset.
Whichever you prefer. In ADO you would get the output param using the
command object. Or you can use the recordset object to get it.

David Russ, CNE, DBA
http://fdruss.no-ip.com/consult/
http://home.earthlink.net/~fdruss/
Author of SQLSchemaCheck 2.0!

[uday murthy]

Thanks Pravin,
But remember I need the output of the "replace" function into a
variable. You are suggesting me to just print it out on the console.
That won't do the trick for me. David Russ's solution worked like
magic.

Lessons learnt:
Never use exec to assign the value to a variable.
stored procs can return more than just integer type data as output.

Regards
Uday

[uday murthy]

David You're a S*T*A*R

Thanks a lot for your help. Your solution works really and its so simple
too!

Lessons learnt:
Never use exec to assign the value to a variable.
stored procs can return more than just integer type data as output.

Regards

[David Russ, CNE]

Your welcome! BTW, you can get rid of the @second line, if you want.
All that is doing is swapping the contents of local variable to another.
The results of the REPLACE can be set directly into the output variable.
I just kept that in because you had it that way. That would be the only
thing I would change.

David Russ, CNE, DBA
http://fdruss.no-ip.com/consult/
http://home.earthlink.net/~fdruss/
Author of SQLSchemaCheck 2.0!

*** Sent via Developersdex http://www.developersdex.com ***

[uday murthy]

Yes David,
I replaced that extra variable already. Thanks again

Regards
Uday