Selecting from stored procedures

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

SQL Server Development (2000)

Selecting from stored procedures

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-05-23 : 15:12:58

Is there a way to select information from a stored procedure?

for instance, if i have a stored procedure like:

create procedure sp_test

@TypeID int

AS

select * from table where typeID = @TypeID

is there a way to do:

select * from exec('sp_test @TypeID=1')

thanks,
dan

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-23 : 15:24:19

Not exactly, however you can insert into a table any valid execute statement that returns data (a stored proc). You can then select from this new table.

<O>

Edited by - Page47 on 05/23/2002 15:24:47

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-05-23 : 15:30:56

i tried playing around with it, can't get it to work:

i tried: (with no avail)

exec(sp_test) into #temp1
execute sp_test into #temp1

i got this to work, but i have to create the temp table first(would like to get around that):

insert into #temp1
execute sp_test

more help?

you guys rock.

<--- the idiot.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-23 : 15:40:16

I'm sure you would like to get around that dirty little secret, but the fact remains, the result set must be compatible with the columns in the table or in the <col_list> as defined in the insert statement. I am wondering if maybe you could implement your requirements by using a view to express the superset returned by the proc and a where clause on the view to get specific . . .

<O>

yoursfriend
Starting Member

5 Posts

Posted - 2002-05-25 : 08:13:56

Hi daniel,

If you want to like these type of functionality, Its not possible to using SP. But Its possible to using User Defined Funtion in SQL-2000. Try to do Multi-statement table-valued function. It will give the exact result as per your expectation. Just try it.

Bye

Nazim
A custom title

1408 Posts

Posted - 2002-05-25 : 08:33:47

you can achieve it via a OPENROWSET .

a Quick Forum search gave me this link

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=15384

quote:

is there a way to do:

select * from exec('sp_test @TypeID=1')

--------------------------------------------------------------

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-25 : 11:25:36

see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=7155

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

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-05-26 : 07:21:08

IMHO

If the output of a stored procedure is a table, why not make a view?? Or write a new stored procedure with to output you would like?

What is your problem behind your question? Maybe we can give you new insights. This is how I solved a lot of my problems here.

Henri

~~~
SQL is nothing, writing it everything.

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-05-29 : 14:55:18

thanks for your replies. I may make it a view - isn't there a noticable performance hit when using views?

anyways, I'm trying to use meta-data with the view, that's why i wanted to search from it, it'd just be a hell of a lot easier. this is what i want my view to look like:

select c.ContactID,
FirstName,
max(case metafieldname when 'PrimaryInterest' then metavalue else NULL end) as 'PrimaryInterest',
max(case metafieldname when 'OtherInterest' then metavalue else NULL end) as 'OtherInterest',
max(case metafieldname when 'UserName' then metavalue else NULL end) as 'UserName',
max(case metafieldname when 'Password' then metavalue else NULL end) as 'Password',
max(case metafieldname when 'Publication' then metavalue else NULL end) as 'Publication'
from Contact c,
ContactMeta cm,
MetaField mf
where MetaClassID = 1 and
mf.MetaFieldID = cm.MetaFieldID and
c.ContactID = cm.ContactID
group by
c.ContactID,
c.FirstName

Edited by - danielhai on 05/29/2002 15:19:49

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources