March 5, 2003 at 12:46 pm
Can one SP call another and get a Recordset back for further processing? My customer needs to run a SP every night on his server to pick up data from my server. He calls his own SP, which makes a series of calls to different SPs on my server. Some of the data is naturally one record each, and we use output parameters to pass the answers back. Another set of data is a variable number of records, I'd like to pass the recordset back. No problem from my end (just do a Select statement in the SP), but what will his SP do with that recordset?
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
March 5, 2003 at 12:51 pm
You can do an:
INSERT INTO #temptable EXEC your_sp
and then use the #temptable in the other procedure.
However, there have been performance problems noted with recompilations of INSERT INTO ... EXEC procedures. See today's threads on Performance Tuning for more info.
March 5, 2003 at 12:58 pm
Yes, that's my fallback plan. Just hoping someone knows a direct method. Thanks for the quick post, though.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
March 5, 2003 at 1:56 pm
Consider replacing a stored procedure with a user-defined function that returns a table. It may satisfy your need. Something like this:
CREATE FUNCTION fn_abc ( <parameter list> )
AS TABLE
BEGIN
...
END
March 5, 2003 at 2:19 pm
quote:
Consider replacing a stored procedure with a user-defined function that returns a table...
Good suggestion. Bob, are you on SQL2K, though?
March 5, 2003 at 2:24 pm
User Defined Functions don't exist until 2000, which we and our customer are not on.
But your comment implies that "table" is a valid data type. I wonder if I can do this:
Create Proc dbo.GetData @MyTable table Output, @OtherVar int, etc.
Set @MyTable = (Select * from AnyTable)
If this works, we're home free....
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
March 5, 2003 at 2:29 pm
You can create a variable of the type TABLE only in SQL Server 2000, not in version 7.
Edited by - mromm on 03/05/2003 2:30:01 PM
March 5, 2003 at 2:43 pm
Thanks mromm, I was just figuring that out for myself. Now I have exactly two reasons to move to 2000, the UDFs and the table variable type.
Looks like we're going to have to do the Insert... Exec and then process the rows from there. Maybe not such a bad way after all. Since the Create, Insert, Drop can all take place inside one SP and the table will have very few rows, perhaps SQL won't ever even write to the disk.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
March 5, 2003 at 4:59 pm
Just make sure you do not get too excited about UDF. It is a good replacement for parameterized views not supported by SQL Server. However if you start putting UDF in your SELECT clauses, WHERE clauses, etc., then you will make your queries very slow and resource intensive. The data would be processed not in sets but one row at a time.
But this is a different subject.
March 6, 2003 at 2:13 am
Just be aware that you cannot nest insert ... exec statements.
What this means is that procedure a cannot use an insert #table exec procedure b if procedure b uses an insert #table2 exec procedure c.
Some of my procedures are nested 6 or 7 deep and I have found that the restriction on nesting insert ... exec applies if any of the nested procedures has an insert ... exec statement.
To overcome this, I have had to return a cursor from the nested procedure. For example
create procedure proc_a
declare @output_cursor cursor
exec proc_b @output_cursor output
/* Normal cursor statements - fetch next, while @@fetch_status etc */
create procedure proc_b @output_cursor cursor varying output
set @output_cursor for
select ....
from table
open @ouput_cursor
I developed this on SQL7 and have since migrated to SQL 2000 and it still works.
Hope this helps.
March 6, 2003 at 4:55 am
Not sure if compatible with SQL7, but another way to do it is with Openrowset.
Andy
March 6, 2003 at 6:38 am
Thanks Jeremy, Andy for the lastest suggestions. As I indicated initially, SP_A is on my customer's machine, SP_B is on ours. I thought about returning a cursor, but since it's a remote call via linked servers I "assumed" that I wouldn't like the performance. I have to admit, I haven't tried it out.
The OpenRowSet (which is supported in 7) will return a recordset, but in raw recordset form, I think I'd just have to insert it into a temporary table for further processing. It also would mean having to give up Select permissions to the customer, right now they have to use the SPs I give them, and I Like That.
I'm going with the Insert..Exec into a temporary table first. That should be one trip for records. A Local temporary table (#MyTemp) is dropped automatically at the end of the SP. I would think (assumption here) that a small one shouldn't ever even hit the hard disk. I may try the cursor just for the heck of it, it is after all a small recordset (like 1 to 10 records). If these both suck performance-wise, I'll setup a view and try the OpenRowSet method which I've used before with good performance. I really don't like the idea that you have to embed the name and password in the SP.
Thanks to all, I'll try to post back with results in a week or so.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
March 6, 2003 at 6:42 am
I have used the openquery syntax to accomplish this task on linked servers. You use the openquery in place of the table name in the query in your customers sp.
March 6, 2003 at 11:07 am
From what I can tell, this is a perfect use for a cursor. Considering that this process runs at night and you have a limited result set, you will never see any performance problems.
I keep hearing how bad cursors are, and to never use them, but I have several instances where they improved performance, and made the code more readable. Use what works, get the job done.
March 6, 2003 at 11:19 am
Here is an example of a proc returning a cursor in case you don't have one:
Create proc x
@rs cursor varying output
as
set @rs = cursor for select name from sysobjects
open @rs
go
-- to call this proc
declare @name char(128)
declare @results cursor
exec x @rs=@results output
fetch next from @results into @name
while (@@fetch_status <> -1)
begin
print @name
fetch next from @results into @name
end
close @results
deallocate @results
go
drop proc x
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply