SELECT Query on Stored Procedure Output

Question

Post reply

SELECT Query on Stored Procedure Output

nns80

Mr or Mrs. 500

Points: 524
More actions
March 23, 2004 at 5:53 pm

#107803

I am trying to query the output of a stored procedure. However I am getting a syntax error.
E.x:
SELECT * FROM (EXEC proc_A(1,2,'Test'))
Please suggest the correct syntax to achive the above result.
Thanks,
-NS

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

insert into tablename exec proc_A(1,2,'Test), then query the tablename

or

convert proc_A to table function and query directly from the function.

eswara Valued Member Points: 63 More actions · Answer 2

Hope this helps

declare @procOut int

exec @procOut = proc_A(1,2,'Test)

select @procOut

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Search the script library or google. Pretty likely someone else has done this before.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

eswara Valued Member Points: 63 More actions · Answer 4

its the same every stored proc. the procOut returns integer,zero meaning successful,anything > 0 some error.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Up to you but here is a simple example to demonstrate how it works.

SET NOCOUNT ON

CREATE TABLE #tblWho (

spid int primary key,

ecid int,

status varchar(30),

loginame varchar(128),

hostname varchar(128),

blk int,

dbname varchar(128),

cmd varchar(16)

)

INSERT INTO #tblWho EXEC sp_who

SELECT * FROM #tblWho

SELECT * FROM #tblWho WHERE blk != 0 OR spid IN (SELECT blk FROM #tblWho WHERE blk != 0)

DROP TABLE #tblWho

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Afraid not at least not that way. You could create a linked server to itself and try

SELECT INTO #tblWho FROM OPENQUERY(MYSELF,'EXEC sp_who')

But I don't like that method because of how it does connections and the time they take to timeout.

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 7

Not with stored procedures. Unfortanely, you will have to create manually the table. The columns doesn't have to have the same name as the recordset that the sp returns, but yes the same data types.