Set value from SP - 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)

Set value from SP

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2005-04-05 : 13:06:58

How would I grab values from a SP that returns a table?


CREATE PROCEDURE sp_test
@param int

AS

SELECT 1 as param1,1 as param2 ,1 as param3
UNION
SELECT 1 as param1,1 as param2 ,1 as param3

GO

In another SP, i want to set the values like


declare @1 int, @2 int, @3 int

select @1 = SUM(a.param1), @2 = SUM(a.param2), @3 = SUM(a.param3) FROM
sp_test @param = 10 a

print @1
print @2
print @3

But this does not work, anyway to grab the fields of my SP as if it was a table alias and pass in a parameters into the SP in a select statement at the same time?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-05 : 13:20:08

create table #a(param1 int, param2 int param3 int)
insert #a exec sp_test @param = 10

select @1 = SUM(a.param1), @2 = SUM(a.param2), @3 = SUM(a.param3) FROM #a a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2005-04-05 : 13:31:20

Thanks, one other question, say sp_test returns more fields than I want to grab. How would I insert only the fields that I want into my temp table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources