Add the result of a stored procedure to a previously executed stored procedure
I have a stored procedure that takes a single parameter that contains a single value. I want to pass a set of values to a procedure one by one and get the combined result into one table. Is it possible?
I am currently using a cursor to loop through the procedure, but only the result of going through the first value in the collection is obtained.
declare @clientid varchar(10)
create table #tmpp(secid varchar(10))
insert into #tmpp values(2319)
insert into #tmpp values(2855)
insert into #tmpp values(1303)
declare cur CURSOR LOCAL for
select secid from #tmpp
open cur
fetch next from cur into @seclientid
while @@FETCH_STATUS = 0 BEGIN
exec getReportforclient @clientid
fetch next from cur into @clientid
END
close cur
deallocate cur
drop table #tmpp
If this is too confusing / unclear / stupid, can someone please provide me with an alternative? Any help is greatly appreciated. Thank.
source to share
There is probably a way to this without using cursors, but without taking a look at your stored procedure, we cannot deal with it. You can create a table (temporary or not) that has the same structure as the results of your sp and insert the results into it. Something like that:
DECLARE @clientid VARCHAR(10)
CREATE TABLE #tmpp(secid VARCHAR(10))
INSERT INTO #tmpp VALUES(2319)
INSERT INTO #tmpp VALUES(2855)
INSERT INTO #tmpp VALUES(1303)
CREATE TABLE #Results(col1 INT, col2.... -- create the table that will hold your
-- results
DECLARE cur CURSOR LOCAL for
SELECT secid
FROM #tmpp
OPEN cur
FETCH NEXT FROM cur INTO @seclientid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Results
exec getReportforclient @clientid
FETCH NEXT FROM cur INTO @clientid
END
CLOSE cur
DEALLOCATE cur
DROP TABLE #tmpp
SELECT *
FROM #Results
source to share
Loop without CURSOR
IF OBJECT_ID('tempdb.dbo.#tmpp') IS NOT NULL DROP TABLE #tmpp
CREATE TABLE #tmpp(Clientid varchar(10))
INSERT #tmpp values(2319), (2855), (1303)
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results(--output columns of procedure)
DECLARE @Clientid int = (SELECT MIN(Clientid) FROM #tmpp)
WHILE (@Clientid IS NOT NULL)
BEGIN
INSERT INTO #Results
EXEC getReportforclient @Clientid
SELECT @Clientid = MIN(Clientid) FROM #tmpp WHERE Clientid > @Clientid
END
Simple SQLFiddle Example
source to share