Question:

I got the following Script and i would like to use an existin stroredprocedure instead of an SELECT-Statement. And i hope that i’m not have to use an temp-Table?!

Thanx all in Adv.

DECLARE @sperrDatum_from date, @sperrDatum_to date, @sperrDatum_description varchar, @typID int,
@deguSperrDatumRel_fkID int, @myID int

DECLARE allSperrdatum CURSOR FOR
EXECUTE spAppPromisDeguSperrdatenPerPlace @typ = 5, @fkID = NULL, @year = 2007
–select * from tappactivity

OPEN allSperrdatum

FETCH NEXT FROM allSperrdatum
INTO @sperrDatum_from, @sperrDatum_to, @sperrDatum_description, @typID, @deguSperrDatumRel_fkID

WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRANSACTION

INSERT INTO tAppDeguSperrDatum
(sperrDatum_from, sperrDatum_to, sperrDatum_description)
VALUES (
dateadd(year, 1, @sperrDatum_from), dateadd(year, 1, @sperrDatum_to), @sperrDatum_description
);
SELECT @myID = SCOPE_IDENTITY();

/* Zuordnung speichern */
INSERT INTO tAppDeguSperrDatumRel
(deguSperrDatumRel_typID, deguSperrDatumRel_sperrDatenID, deguSperrDatumRel_fkID)
VALUES (
@typID, @myID, @deguSperrDatumRel_fkID
)

COMMIT TRANSACTION

FETCH NEXT FROM allSperrdatum
INTO @sperrDatum_from, @sperrDatum_to, @sperrDatum_description, @typID, @deguSperrDatumRel_fkID
END

CLOSE allSperrdatum
DEALLOCATE allSperrdatum

Solution:

in order to reuse the recordset from a sp, u must use temp tables

1. create a temptable of the same structure the recordset returned by the sp , idf the sp returns 2 records, then the temp table must contain 2 column

create table #temp ( i int , j int)

INSERT INTO #Temp
EXECUTE spAppPromisDeguSperrdatenPerPlace @typ = 5, @fkID = NULL, @year = 2007

DECLARE allSperrdatum CURSOR FOR
SELECT * FROM #Temp

OPEN allSperrdatum

FETCH NEXT FROM allSperrdatum
INTO @sperrDatum_from, @sperrDatum_to, @sperrDatum_description, @typID, @deguSperrDatumRel_fkID

WHILE @@FETCH_STATUS = 0
BEGIN