Cursors With Dynamic SQL

Hi!

I'm moving from firebird to MS SQL and have many problems. In firebird, I did many things with FOR...DO..SUSPEND loops. That loop enabled me to scan through the result set, do many calculations and create new result set (defined as variabled in RETURNS part of procedure and actually returned with SUSPEND).

In MS SQL, I can accomplish same thing with cursors. I did read cursors are slow, but I really can't imagine how to perform all this calculations without them (writing software for production planning).

One thing I couldn't do. Declaring cursors with dynamic SQL (in firebird that was for...execute statement(statement) do.

Later I found out about sp_cursoropen and related procedures, but that get's me with "Only functions and extended stored procedures can be executed from within a function."

Here is the code of function with latest modifications to sp_ procedures. Note: This is REALY out-of-context procedures since it is part of 18 procedures which together creates rough production plan.

How to make this function work?



CREATE function [dbo].[plan_narudzbe] (
@koja_narudzba int,
@tip int)

returns @ReturnTable table (
ID_NARUDZBA_STAV int,
ID_ARTIKL VARCHAR (20),
KOLICINA float,
ID_RADNI_PLAN int,
TERMIN_NARUDZBE DATETIME)
as

begin
declare
@U_PROIZVODNJI float, @RASPISANI_PLAN float, @D float, @STR VARCHAR(1000),
@NARUDZBA_PLAN_FINI_PLAN int, @STATUS_NARUDZBE SMALLINT,

@ID_NARUDZBA_STAV int, @ID_ARTIKL VARCHAR (20), @KOLICINA float,
@ID_RADNI_PLAN int, @TERMIN_NARUDZBE DATETIME

SELECT
@NARUDZBA_PLAN_FINI_PLAN = VRIJEDNOST
FROM PARAMETRI
WHERE NAZIV='NARUDZBA_PLAN_FINI_PLAN'


set @NARUDZBA_PLAN_FINI_PLAN=COALESCE(@NARUDZBA_PLAN_FINI_PLAN,0);
if ((@TIP=1) AND (@KOJA_NARUDZBA IS NULL)) return;
set @STR= '
SELECT Q.ID,Q.ID_ARTIKL,
COALESCE(Q.POTVRDA_KOLICINA,Q.KOLICINA,0) AS KOLICINA,
COALESCE(Q.POTVRDA_TERMIN_ISPORUKE,Q.TERMIN_ISPORUKE) AS TERMIN,
N.STATUS ,
SUM(RN.KOLICINA_NALOGA) AS U_PROIZVODNJI
FROM NARUDZBA_STAV Q
LEFT JOIN RADNI_NALOG RN ON (RN.ID_NARUDZBA_STAV=Q.ID)
LEFT JOIN NARUDZBA N ON (Q.ID_NARUDZBA=N.ID)
WHERE Q.STATUS NOT IN (2,5,8,9) ';
if (@KOJA_NARUDZBA IS NOT NULL) set @STR=@STR+' AND Q.ID='+cast(@KOJA_NARUDZBA as varchar)+' ';
set @STR=@STR+' GROUP BY Q.ID,Q.ID_ARTIKL, COALESCE(Q.POTVRDA_KOLICINA,Q.KOLICINA,0),
COALESCE(Q.POTVRDA_TERMIN_ISPORUKE,Q.TERMIN_ISPORUKE), N.STATUS ';

-- Create a dynamc read-only cursor

DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, @STR, 2, 8193

-- Name the cursor
EXEC sp_cursoroption @cursor, 2, 'narudzba_stav'

DECLARE @narudzba_stav CURSOR
EXEC sp_describe_cursor @narudzba_stav out, N'global', 'narudzba_stav'

while (1=1) begin
fetch next
from narudzba_stav
INTO
@ID_NARUDZBA_STAV, @ID_ARTIKL, @KOLICINA, @TERMIN_NARUDZBE, @STATUS_NARUDZBE, @U_PROIZVODNJI
if (@@fetch_status<>0) break;

set @KOLICINA=@KOLICINA-COALESCE(@U_PROIZVODNJI,0);
if (@STATUS_NARUDZBE=12) BEGIN
if (@NARUDZBA_PLAN_FINI_PLAN=1) BEGIN
set @RASPISANI_PLAN=NULL;
SELECT
@RASPISANI_PLAN = SUM(KOLICINA)
FROM NARUDZBA_STAV_PLAN
WHERE ID_NARUDZBA_STAV_PLAN=@ID_NARUDZBA_STAV
set @KOLICINA=@KOLICINA-COALESCE(@RASPISANI_PLAN,0);
END
ELSE set @KOLICINA=0;
END
if (@KOLICINA>0) BEGIN
set @ID_RADNI_PLAN=NULL;
SELECT TOP 1 @ID_RADNI_PLAN = ID
FROM RADNI_PLAN
WHERE ID_ARTIKL=@ID_ARTIKL
ORDER BY AKTIVAN_PLAN DESC,ID DESC

insert @ReturnTable values (
@ID_NARUDZBA_STAV, @ID_ARTIKL, @KOLICINA, @ID_RADNI_PLAN, @TERMIN_NARUDZBE)
END
END
-- Close the cursor
EXEC sp_cursorclose @cursor

return
end