capturing values from a stored proc

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)

capturing values from a stored proc

Author

Topic

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2005-06-15 : 17:25:33

I want to say:
usp_get_age is a proc that returns the age, not as a variable via OUTPUT, but as a recordset,
ie. exec usp_get_age will return in the query analyzer window, 21.

I want to capture this value in a variable.
Declare @x INT

set @x = usp_get_age

??

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-06-15 : 17:53:43

Not as pretty as I would imagine a dbaslut is but....

maybe something like this

Declare @x INT
create table #abc(col1 int)

insert into #abc
exec usp_get_age
select @x = col1 from #abc
select @x
drop table #abc

Duane.

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2005-06-15 : 18:04:09

hmm... so I'd have to use a table to capture the value, select that value and then drop the table? .... gotta be a better way...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-06-15 : 18:06:44

why not code a function that returns int - similar to the stored proc?

Duane.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-15 : 18:10:43

What do you have against output param?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-15 : 18:15:36

Yes you must use a temp table if you want to work with the results of a stored procedure. You can not use table variables, since you can't use exec when inserting into one. I'd go with Duane's suggestion about a function if you don't want to use a temp table. However, if you really are only returning one value from your stored procedure, then you should be using an OUTPUT parameter as Nathan is suggesting. That's what they are for.

Tara

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2005-06-16 : 12:26:54