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.
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)asbegin 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 returnend |
|
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! CoreyCo-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 ..." |
|
|
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. |
|
|
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 |
|
|
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! |
|
|
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 |
|
|
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. |
|
|
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.aspxMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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; endendEND 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 :-). |
|
|
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 |
|
|
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). |
|
|
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 |
|
|
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". |
|
|
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? |
|
|
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 afterExample:I have a table mytableA with columns TransactionID intTransType Varchar(20)MoneyType Varchar(20)Transvalue floatI 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.JimUsers <> Logic |
|
|
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 customerID_PARTNER_ROOT - ID of customer's root customerID_PARTNER_PARENT - ID of customer's parent customerNow, 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 itemOther 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 validFormula 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. |
|
|
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 intTransType Varchar(20)MoneyType Varchar(20)Transvalue floatI need to increase the Transvalue of all english moneytypes by 5% and all others by 10%
Can you give me sollution for your example? |
|
|
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 newValuefrom myTableAGo with the flow & have fun! Else fight the flow |
|
|
Next Page
|
|
|
|
|