July 18, 2006 at 7:16 am
A stored procedure uses a While loop which calls a Select statement, therefore producing multiple result sets. How can the stored procedure combine the results sets without using a temp table or a table variable?
July 18, 2006 at 7:59 am
You'd need to use a temp table or table variable. Is there any reason why you don't want to use one?
I must admit that the use of a While loop that calls a Select statement sounds a bit suspicious (although you might have a good reason). There may be a way of achieving the desired result without using a While loop.
Can you provide a bit more detail on what you're trying to do?
July 18, 2006 at 8:21 am
The overall goal is to use the stored procedure in a SQL statement, for example:
SELECT * FROM OPENQUERY(SERVER, 'EXEC SP_CALL')
The while loop grabs each record of a table. Each record contains an XML blob. The XML document gets flattened and pushed to a batch ETL process. Since there is a need in the stored procedure to call sp_xml_preparedocument and sp_xml_removedocument I can not create a Table-Valued function as it will not allow the stored procedure calls. Also, OPENQUERY does not like temp tables or table variables.
July 18, 2006 at 8:37 am
I've tried to use a temp table with openquery and it works fine for me.
I just did:
insert into #mytemp
select * from openquery (server, 'exec sp_help')
And that worked fine. I'm probably mis-interpreting how you're trying to use the temp table.
July 18, 2006 at 8:51 am
The temp table, in this case, would be used inside the stored procedure to consolidate all the record sets, then pass back one record set that would be feed into another process.
July 18, 2006 at 9:03 am
Ah, I see.
Do you have to use OPENQUERY? I'm assuming you've got a linked server set up, in which case can't you execute the stored procedure with four-part naming?
exec server.database.dbo.sp_call
I just tried this and it works even when sp_call has a temp table being used in it. As you pointed out, it doesn't work when using OPENQUERY. I hadn't come across this behaviour myself before.
July 18, 2006 at 9:25 am
If there is another way to put a stored procedure call in a From clause, I'd love to know. Having this call in a From clause would be useful in several applications here.
July 18, 2006 at 9:33 am
If what you want to do is insert the resultset of stored procedure into a table you could do:
insert into #mytable
exec server.database.dbo.sp_call
What you cannot do is treat it as a derived table on which you can apply a WHERE clause. To do that though you'd just pass in the necessary parameters and filter your final resultset within the procedure.
Or once, it's in the temporary table, select from there accordingly. But that would involve one extra step.
July 18, 2006 at 10:11 am
I wish I could do that but because of the third party software architecture, I have to make the call in the From clause. Looks like the only issue is using the temporary tables in the SP.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply