Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cursors with Dynamic SQL

Author  Topic 

mariob
Starting Member

15 Posts

Posted - 2005-09-22 : 08:04:19
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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-22 : 08:30:26
With A title like that... I came here to tell you that you should be shot!

I do have a suggestion if you insist on going forward with this farce...

Maybe you could execute your dynamic sql into a staging table say cursorData... and then open a cursor from that table???

That's all I got on that... but I would STRONGLY suggest that you reevaluate your solution before you get too much further. While you are converting, you are already rewritting parts... now is the time to improve!

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-22 : 08:56:42
instead of showing us your old code and asking "how to convert it?", why don't you show us your tables, some sample data, and what you are trying to accomplish. Many times the worst thing you can do when converting systems is to focus too much on trying to convert line by line the old code; it is often much, much better to ask yourself "never mind what this code IS doing -- what SHOULD it be doing?", make sure that you clearly state the requirements (often, there are questions to answer), and then you'll often find you can rewrite it much simplier and easier from there.

That definitely applies in this case -- I promise you that as long as you database design is properly normalized you will not need cursors or dynamic sql except in the rarest circumstances.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-22 : 09:49:48
while loop can be used like cursors.
but i'd strongly advise to follow the advice from corey and jeff... it's for your own good, believe me

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-22 : 12:40:56
Hmmmmm........do I smell cursors over here?.........

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-09-22 : 12:48:18
I laughed when I saw the title of this thread. I have nothing to add of any value, other than to say thanks for the laugh.



-ec
Go to Top of Page

mariob
Starting Member

15 Posts

Posted - 2005-09-22 : 15:06:11
Ok, I'm lost a bit here.
You all say that 99% of business logic can be put in database (no client-side business logic) without using cursors at all?
If so, I'm REALLY on the wrong track. 99% of business logic in Firebird database is accomplished with FOR..DO..SUSPEND procedures (basically cursors, although Firebird does not have explicit cursor).

I'm afraid I can't easily explain the whole process to which this one procedure belongs, since it is very complicated and would require me to post half database here.
But I will find less complicated example that is using only one or two procedures which I wouldn't know how to write without cursors, so you can show me how to do it.
Obviuosly I'm doing things in very wrong way (although I did make some people laugh - always some good in bad :-)), but that was the way things were done in Firebird.

Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-22 : 22:56:29
Post examples of your table structure and sample data. Then, show us what you are trying to do with the data and what you want the results to look like. We can then help you a lot better.

Take a look at this blog:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

mariob
Starting Member

15 Posts

Posted - 2005-09-23 : 03:13:01
Is there a way to attach a file here?
I made script for couple of tables with some data, but script has 23000 lines (850Kb). A bit to much to post here, I suppose.

Compressed zip is only 50Kb, but I don't know how to put it here.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-23 : 05:27:27
no you can't. but you could post a link to it if you can put it somewhere on the web.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-23 : 08:33:27
The point isn't that we need to see ALL of your data, just enough so that we can get a feel for what is being stored and what situations we need to handle. There's tons of examples of questions here at sqlteam where either the person asking or the person answering the question has written a few INSERT lines to put in some sample data.

The key is to figure out which exceptions live in your data, and what your typical data is like, get a small amount, and work with it. And that applies to your development process as well, not just when you ask questions. Break down things into smaller parts and work on solutions on part at a time; don't test out SQL statements when you are just learning on production data.
Go to Top of Page

mariob
Starting Member

15 Posts

Posted - 2005-09-26 : 07:46:29
Ok, you can get zip file here.

In it you'll find DDL for all the tables needed including data.

Ok, now the procedure.
This is one of the simplest I could find. All other uses more tables and call many other procedures and also go more complicated with nested cursors.
Source of procedure is in Firebird procedure language. It is similar to MSSQL. I'll just add comments.

The idea of procedure is following:
- take all data from PRAO_ULAZ_STAVKE for certain customer (ID_PARTNER) and date period (DATUM).
- for every record, find price (table CJENIK - price can be by customer and parts or only by parts (valid for all customers)), foreign currency (if any, table VALUTA) and make currency conversion to local currency if necessery (table TECAJNA_LISTA).
- insert process data to FAKTURA_STAV.


[b]
CREATE PROCEDURE PRAO_FAKTURA_KOLICINA (
ID_FAKTURA INTEGER,
ID_PARTNER VARCHAR (20) CHARACTER SET WIN1250,
TIP VARCHAR (5) CHARACTER SET WIN1250,
DATUM_OD DATE,
DATUM_DO DATE)
AS
/* Variable declaration */
declare variable tmp_partner varchar(20);
declare variable kolicina double precision;
declare variable vrijednost numeric(18,2);
declare variable vrijednost_valuta numeric(18,2);
declare variable cijena double precision;
declare variable broj_jedinica integer;
declare variable srednji_tecaj double precision;

declare variable id integer;
declare variable id_artikl varchar(20);
declare variable naziv_artikla varchar(50);
declare variable jmj varchar(5);
declare variable id_valuta smallint;

declare variable s varchar(200);

declare variable rabat_postotak double precision;
declare variable rabat_iznos numeric(18,2);

declare variable porez varchar(100);
declare variable porez_posto double precision;
declare variable porez_iznos numeric (18,2);

BEGIN
/* Getting next ID - not importan for MS SQL */
select first 1
id+1
from faktura_stav
order by id desc
into :id;
if (id is null) then id = 1;

/* Selecting discount percentege */
select rabat_postotak
from faktura
where
id_faktura = :ID_FAKTURA
into :rabat_postotak;
if (rabat_postotak is null) then rabat_postotak = 0;

/* Executing procedure to get tax percentege from table PARAMETRI */
execute procedure A_PARAMETAR('POREZ', 0) returning_values porez;
porez_posto = cast(porez as double precision);
if (porez_posto is null) then porez_posto = 0;

if (tip='ULAZ') then begin
/*
Main select for cursor.
Selecting records from prao_ulaz_stavke for ID_PARTNER and
date period between :DATUM_OD and :DATUM_DO.
DATUM_KNJIZENJA, REKLAMACIJA and ID_FAKTURA are indicators */
for
select
pus.id_artikl, a.naziv_artikl, a.jmj, sum(pus.kolicina)
from prao_hijerarhija q
left join prao_ulaz pu on pu.id_partner = q.id_partner
left join prao_ulaz_stavke pus on pus.id_ulaz = pu.id
left join artikl a on a.id = pus.id_artikl
where
(q.id_partner_root = :ID_PARTNER or
q.id_partner = :ID_PARTNER) and
pu.datum between :DATUM_OD and :DATUM_DO and
pu.datum_knjizenja is not null and
pu.reklamacija = 0 and
pu.id_faktura is null
group by pus.id_artikl, a.naziv_artikl, a.jmj
into :id_artikl, :naziv_artikla, :jmj, :kolicina
do begin
id_valuta=null; cijena=null;
/* We look last price for part (ID_ARTIKL) for customer (ID_PARTNER) */
select first 1
id_valuta, cijena
from cjenik
where
id_artikl = :id_artikl and
id_partner = :ID_PARTNER and
vrijedi_od_datuma <= current_date
order by id desc
into :id_valuta, :cijena;

s='';
if (cijena is null) then begin
/* We did not find that price, so we write in description that price is not by customer and we try to find any price for that part (ID_ARTIKL) */
s='Cijena nije po partneru!';
select first 1
id_valuta, cijena
from cjenik
where
id_artikl = :id_artikl and
vrijedi_od_datuma <= current_date
order by id desc
into :id_valuta, :cijena;
end

if (cijena is null) then begin
/* if we did not find price for that part we raise exception */
s='"NEMA_CIJENE","'||id_partner||'","'||id_artikl||'"';
execute procedure A_EXC(s);
end

/* Currency is not null, so we'll have to do some currency conversion */
if (id_valuta is not null) then begin
/* Getting conversion values from conversion table */
select
broj_jedinica, srednji_tecaj
from tecajna_lista
where
id_valuta = :id_valuta and
datum = current_date
into :broj_jedinica, :srednji_tecaj;
/* If no conversion values are found, we raise exception */
if (srednji_tecaj is null) then begin
s='"NEMA_TECAJA","'||id_valuta||'"';
execute procedure A_EXC(s);
end
/* vrijednost_valuta is total value in foreign currency
cijena is price, and kolicina is quantity */
vrijednost_valuta = cijena * kolicina;
cijena = cijena * (srednji_tecaj / broj_jedinica);
end

/* vrijednost is total amount in local currency */
vrijednost = cijena * kolicina;
/* Calculating discount value */
rabat_iznos = (rabat_postotak / 100) * vrijednost;
/* Calculating total value */
vrijednost = vrijednost - rabat_iznos;
/* Calulating tax */
porez_iznos = vrijednost * (porez_posto / 100);



/* Inserting data into faktura_stav */
insert into faktura_stav (
id, id_faktura, id_artikl, opis, jmj, kolicina, cijena, cijena_za_komada,
vrijednost, vrijednost_u_valuti, napomena, rabat_postotak, rabat_iznos, porez)
values (
:id, :id_faktura, :id_artikl, :naziv_artikla, :jmj, :kolicina, :cijena, 1,
:vrijednost, :vrijednost_valuta, :s, :rabat_postotak, :rabat_iznos, :porez_iznos);

id = id + 1;
end
end
END


Also, I got a chance to look at source code of one accounting software written in Slovenia. Massive use of cursor. I guess I'm not the only one thinking on that (wrong) way :-).
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-26 : 08:28:13
what software?
i'll tell you if it's ok or not, since i'm from slovenia...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

mariob
Starting Member

15 Posts

Posted - 2005-09-26 : 08:47:23
I think it's called Pantheon or something like that.
It got quite high marks in one of accounting software comparison here (as for capabilites and features).
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-26 : 08:53:45
that's datalabs no?
yeah... i got a friend who works there...
when they started it noone knew much about set based thinking i believe, so cursors felt more procedural...
i do have to say it's a preety ok program.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

mariob
Starting Member

15 Posts

Posted - 2005-09-27 : 01:49:57
Yes, DataLabs, right.
Can you make a software like that without cursors?

We also have accounting module in our software (not that general, but more specific) and wouldn't know how to do it without cursors. And procedures for production planning are 10 times more complex.

So, can anyone write cursor-free solution for above procedure? I would really like to see how you guys think when you write procedures. I'm willing to learn, but someone has to teach, not just say "Cursors are bad, avoid them".
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-27 : 08:39:16
>>So, can anyone write cursor-free solution for above procedure? I would really like to see how you guys think when you write procedures. I'm willing to learn, but someone has to teach, not just say "Cursors are bad, avoid them".

YOU need to teach US and tell us what that procedure is supposed to be doing, as I explained earlier. We can't do your job and figure out what this cursor-code is accomplishing -- you need to know that before you or anyone else an rewrite it. Tell us, in a couple of sentences, with examples as necessary, a detailed and specific description of what the procedure needs to do, and we'll help you. Again, forget about how it is currently written or what it currently does, and do not try to rewrite it line-by-line -- make sure that you know and can explain what needs to happen. If you don't, then you are not approaching this the right way; the first step, before writing a single line of code, is understanding what your goal is and what your code needs to do. Makes sense, right?

Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-09-27 : 10:36:11
Maybe this will help.

Forget for a second firebird,cursors,functions and statements.

Start at the begining.
This is what jeff and the others are after

Example:

I have a table mytableA with columns
TransactionID int
TransType Varchar(20)
MoneyType Varchar(20)
Transvalue float

I need to increase the Transvalue of all english moneytypes by 5% and all others by 10%

We can get into why cursors are bad later.


Jim
Users <> Logic
Go to Top of Page

mariob
Starting Member

15 Posts

Posted - 2005-09-28 : 02:03:01
Ok, I'll try to rewrite explanation once more:

First, I have hierarhical data od customers in table PRAO_HIJERARHIJA.
Table contains following fields:
ID_PARTNER - ID of customer
ID_PARTNER_ROOT - ID of customer's root customer
ID_PARTNER_PARENT - ID of customer's parent customer

Now, I have to pull all data from one root customer (which has many child customers) for period of time. This data is used to make an invoice.

Needed data is in two tables.
Master table:
- PRAO_ULAZ (it contains:
ID_PARTNER - ID of customer,
DATUM - date of document,
DATUM_KNJIZENJA - date of confirming the document,
REKLAMACIJA - weather the document is reclamation or not
ID_FAKTURA - ID of invoice

Detail table:
- PRAO_ULAZ_STAVKE which contains
ID_ARTIKL - item's ID
KOLICINA - quantity of item

Other important tables:
ARTIKL - contains information about items
ID - item ID
NAZIV_ARTIKL - name of item
JMJ - measurment unit (kg, cm ...)

CJENIK - contains prices of items for customers
ID_PARTNER - ID of customer for which the price is defined
ID_ARTIKL - ID of item for which price is defined
CIJENA - price

TECAJNA_LISTA - contains monetary conversion values (USD->Kn, EUR->Kn ....)
ID_VALUTA - foreign monetary unit
BROJ_JEDINICA - conversion quantity value (example, 1 EUR, 1 USD, 100 SIT)
SREDNJI_TECAJ - conversion value to convert foreign monetary unit to local.
DATUM - date for which above values are valid
Formula to convert foreign unit is: FOREIGN_PRICE * (SREDNJI_TECAJ / BROJ_JEDINICA)


The goal is to create invoice details from table PRAO_ULAZ_STAVKE.
Invoice details table FAKTURA_STAV:
ID - unique identifier
ID_FAKTURA - foreign key to master invoice table FAKTURA
ID_ARTIKL - ID of item
OPIS - description
JMJ - quantity unit
KOLICINA - quantity
CIJENA - price of item in local monetary unit
VRIJEDNOST - value of item in local monetary unit
VRIJEDNOST_U_VALUTI - value of item in foreign monetary unit
RABAT_POSTOTAK - discount percentege
RABAT_IZNOS - discount value
POREZ - tax value


So, I need data from PRAO_ULAZ_STAVKE for all customers that belong to root customer, for certain period of time, where REKLAMACIJA = 0, DATUM_KNJIZENJA IS NOT NULL and ID_FAKTURA IS NULL.
For every item pulled from PRAO_ULAZ_STAVKE I have to find the price on the following way:
- 1. I read ID_VALUTA, CIJENA from table CJENIK for given ID_ARTIKL and ID_PARTNER. If value does not exists, then
- 2. I read table CJENIK for given ID_ARTIKL (in this case, I don't look ID_PARTNER). If I find the value, I must write to description "Price is not by customer" message.
If no value is found, I must raise exception.

After I have price, I need to make monetary conversion if necessery. It is necessery if ID_VALUTA field is not null.
To make monetary conversion:
1. I read BROJ_JEDINICA, SREDNJI_TECAJ from table TECAJNA_LISTA where ID_VALUTA is value from CJENIK (got it in above step), and DATUM is current date.
If SREDNJI_TECAJ is NULL, I must raise exception (conversion values MUST exists if ID_VALUTA in table CJENIK is not null).

If I have information, I calculate local monetary value.

I also need to calcule discount value and tax value. Discount percentege is written in master invoice table (FAKTURA), and tax percentege is written in table PARAMETRI (I execute procedure A_PARAMETAR('POREZ') to get tax percentege from that table).

When all calculations are done, I insert those data to invoice detail table (FAKTURA_STAV)

I really don't know how else do describe what I want to do. Please ask concrete questions for further details.
Go to Top of Page

mariob
Starting Member

15 Posts

Posted - 2005-09-28 : 02:37:32
quote:
Originally posted by JimL


I have a table mytableA with columns
TransactionID int
TransType Varchar(20)
MoneyType Varchar(20)
Transvalue float

I need to increase the Transvalue of all english moneytypes by 5% and all others by 10%




Can you give me sollution for your example?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-28 : 04:18:50
select case when MoneyType = 'english' then Transvalue*1.05 else Transvalue*1.10 end as newValue
from myTableA

Go with the flow & have fun! Else fight the flow
Go to Top of Page
    Next Page

- Advertisement -