Assigning results of an EXEC to a variable

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)

Assigning results of an EXEC to a variable

Author

Topic

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-11-28 : 13:43:44

Can anyone explain to me why this doesn't work:

declare @a char(1) 
set @a = exec('select ''a''')
select @a

If I want to run an EXEC at some point in a proc and then store the returned value for use later in the proc (in this case a count(*) with a dynamic DB name), is the only solution to use a temp table?

Thanks in advance
Michael

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-28 : 14:01:33

,

Not sure the technical explanation, but the solution is to use output paramaters and sp_executesql. Here are a couple of example links:

[url]http://sqlteam.com/Forums/topic.asp?TOPIC_ID=10756&FORUM_ID=5&CAT_ID=3&Topic_Title=Setting+results+of+Dyanamic+sql+to+variables&Forum_Title=Developer[/url]

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10791[/url]

-------------------
It's a SQL thing...

izaltsman
A custom title

1139 Posts

Posted - 2001-11-28 : 14:05:27

If you do it with EXEC -- temp table is the only way. But you could use sp_executesql and then you can use @a as your output parameter...

DECLARE @a CHAR (1)

exec sp_executesql N'select @a = ''a''', N'@a char(1) OUTPUT', @a OUTPUT

Damn it!!! Mark snipes me again!

Edited by - izaltsman on 11/28/2001 14:22:20

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-28 : 15:01:48

quote:
Damn it!!! Mark snipes me again!

-------------------
It's a SQL thing...

Edited by - AjarnMark on 11/28/2001 15:02:20

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-11-28 : 15:10:52

Thanks -- I tried to reply earlier, but I've been having connection problems.

inrsence
Starting Member

48 Posts

Posted - 2001-11-28 : 16:09:34

I managed to use EXEC SP OUTPUT without using a temp table. It did require that the name of the parameter being returned was the same as one existing in the calling SP though.

Something like this works:
CREATE PROCEDURE Test
@a int
AS
RETURN @a+1

CREATE PROCEDURE Test2
@a int
AS
EXEC Test @a=1 OUTPUT
PRINT @a

Could be me.. my server was abducted by aliens :)

izaltsman
A custom title

1139 Posts

Posted - 2001-11-28 : 16:25:34

Yeah, EXEC returns OUTPUT parameters just fine when you are calling stored procedures. Dynamic SQL though is another story (and that's what the original question was about)... Have to use sp_executesql in that case.

inrsence
Starting Member

48 Posts

Posted - 2001-11-28 : 16:33:44

Ahh, my bad..

But here's a follow up question then..

can you do something like:

SELECT @a = EXEC Test @a=1

Thanks,
Greg

izaltsman
A custom title

1139 Posts

Posted - 2001-11-28 : 19:11:23

That looks like you are want to capture a RETURN value from executing Test stored procedure. Then your syntax should be:

EXEC @a = Test @a=1

Edited by - izaltsman on 11/28/2001 19:11:56

nr
SQLTeam MVY

12543 Posts

Posted - 2001-11-28 : 22:39:04

Usually return codes are saved for success/failure or similar.
for an output parameter

create procedure t1
@i int output
as
...
go

declare @j int
set @j = 1
exec t1 @j output
print @j

You can also return output parameters from dynamic sql

-- setting a variable from dynamic sql

declare @i int
exec sp_executesql N'select @i = 999', N'@i int output', @i output
select @i

-- setting output parameter from dynamic stored procedure call

declare @OutputParameter varchar(100) ,
@error int ,
@SPName varchar(128) ,
@SPCall nvarchar(128) ,
@rc int
select @SPCall = 'exec ' + @SPName + ' @OutputParameter output'
exec @rc = sp_executesql @SPCall, N'@OutputParameter varchar(100) output', @OutputParameter output
select @Error = @@error

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources