Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > General

Lost Password?

Reply
 
Thread Tools Display Modes
  #1  
Old 03-08-2008, 10:59 PM
digitiger digitiger is offline
Senior Member
 
Join Date: Jan 2001
Posts: 716
Default Translating SP from Interbase to SQL Server 2005

I know that this is not really related to Delphi but still as Interbase has been related to Delphi so I hope that some of you will surely have good expertise with Interbase as well as SQL Server and will be able to help me.

The issue is that I am migrating an old Delphi/Interbase project to Delphi/SQL Server 2005 Express edition. My DB experience has been mainly with Oracle and so several things in Interbase as well as SQL Server as new to me. Thus I would request you gurus to kindly help me translate the following StoredProcedure from Interbase 7.5 to SQLServer2005Express.

================================================== =======

ALTER PROCEDURE "SP_BESADV"
(
"FILNR" VARCHAR(1),
"SOORT" VARCHAR(2),
"DAGEN" INTEGER
)
RETURNS
(
"ARTNR" VARCHAR(9),
"FIL" VARCHAR(1),
"ARTOMS" VARCHAR(30),
"LEVNR" VARCHAR(5),
"ARTFAKNPR" NUMERIC(10, 2),
"ARTFAKPR" NUMERIC(10, 2),
"TYPNR" VARCHAR(15),
"MRKOMS" VARCHAR(15),
"ASGOMS" VARCHAR(15),
"ARTASS" VARCHAR(1),
"ARTKOLLO" INTEGER,
"LEVOMDOOS" VARCHAR(1),
"LEVNAAM" VARCHAR(30),
"LEVBESWYZ" VARCHAR(1),
"ADV_AANT" INTEGER,
"VRD" INTEGER,
"VRD_OPT" INTEGER,
"VRD_AFW" INTEGER,
"TEBEST" INTEGER,
"INBEST" INTEGER,
"VRVRD" INTEGER,
"GERES" INTEGER,
"W1" INTEGER,
"W2" INTEGER,
"W3" INTEGER,
"W4" INTEGER,
"W5" INTEGER,
"NUBEST" INTEGER,
"ARGWNKASS" INTEGER,
"ARGOMS" VARCHAR(15),
"AHGOMS" VARCHAR(15),
"AFDOMS" VARCHAR(15)
)
AS
DECLARE VARIABLE ARTVRK INTEGER;
DECLARE VARIABLE ARTFAKDAT DATE;
DECLARE VARIABLE OPENPR CHAR(1);
DECLARE VARIABLE WNKASS CHAR(1);
BEGIN

IF ((SOORT <> 'VK') AND (SOORT <> 'BE') AND (SOORT <> 'BG') AND (FILNR <> 'T')) THEN
BEGIN
FOR
SELECT EXARV.ARTNR, EXARV.FILNR, EXARV.ARVVRD, EXARV.ARVVRDOPT, EXARV.ARVVRDAFW, EXVRD.ARVVRVRD, EXVRD.ARVGERES,
(EXVRD.INBESTI + EXVRD.INBESTE), EXVRD.TEBEST, EXVRD.WNKASS
FROM EXARV LEFT OUTER JOIN EXVRD ON (EXARV.ARTNR = EXVRD.ARTNR AND EXARV.FILNR = EXVRD.FILNR)
WHERE EXARV.FILNR = :FILNR
AND ((ARVVRVRD + TEBEST + INBESTI + INBESTE < 0 )
OR ((ARVVRVRD + TEBEST + INBESTI + INBESTE) < (ARVVRDOPT - ARVVRDAFW))
OR (EXARV.ARVVRD < (ARVVRDOPT - ARVVRDAFW)))

INTO :ARTNR, :FIL, :VRD, :VRD_OPT, :VRD_AFW, :VRVRD, :GERES, :INBEST, :TEBEST, :ARGWNKASS
DO
BEGIN
NUBEST = 0;
ARTVRK = 0;
ADV_AANT = 0;
W1 = 0;
W2 = 0;
W3 = 0;
W4 = 0;
W5 = 0;
IF (VRD IS NULL) THEN VRD = 0;
IF (VRVRD IS NULL) THEN VRVRD = 0;
IF (VRD_OPT IS NULL) THEN VRD_OPT = 0;
IF (VRD_AFW IS NULL) THEN VRD_AFW = 0;
IF (GERES IS NULL) THEN GERES = 0;
IF (INBEST IS NULL) THEN INBEST = 0;
IF (TEBEST IS NULL) THEN TEBEST = 0;
IF (ARGWNKASS IS NULL) THEN ARGWNKASS = 0;

BEGIN
IF (SOORT = 'VG') THEN
IF ((VRVRD + GERES + TEBEST + INBEST) < (VRD_OPT - VRD_AFW) ) THEN
ADV_AANT = (VRD_OPT - (VRD + TEBEST + INBEST - GERES));

IF (SOORT = 'VE') THEN
IF ((VRVRD + TEBEST + INBEST) < (VRD_OPT - VRD_AFW)) THEN
ADV_AANT = (VRD_OPT - (VRD + TEBEST + INBEST));



IF (ADV_AANT > 0) THEN
BEGIN
BEGIN
SELECT * FROM SP_ARTGEG(:ARTNR)
INTO :ARTOMS, :LEVNR, :ARTFAKNPR, :ARTFAKPR, :TYPNR, :MRKOMS, :ASGOMS, :ARTASS, :ARTKOLLO, :LEVOMDOOS, :LEVNAAM, :LEVBESWYZ, :OPENPR, :ARGOMS, :AHGOMS, :AFDOMS;
END
IF (OPENPR = 'J') THEN
ADV_AANT= 0;

IF ((LEVOMDOOS = 'J') AND (ARTKOLLO <> 0)) THEN
BEGIN
IF (MOD(ADV_AANT, ARTKOLLO) > 0) THEN
NUBEST = (((ADV_AANT/ARTKOLLO) + 1)*ARTKOLLO);
ELSE
NUBEST = ((ADV_AANT/ARTKOLLO)*ARTKOLLO);
END
ELSE
NUBEST = ADV_AANT;

END
END
IF (ADV_AANT > 0) THEN SUSPEND;
END
END


IF (((SOORT = 'BE') OR (SOORT = 'BG')) AND (FILNR <> 'T')) THEN
BEGIN
FOR
SELECT EXART.ARTNR, EXARV.FILNR, EXARV.ARVVRD, EXARV.ARVVRDOPT, EXARV.ARVVRDAFW, EXVRD.ARVVRVRD, EXVRD.ARVGERES,
(EXVRD.INBESTI + EXVRD.INBESTE), EXVRD.TEBEST, EXVRD.WNKASS, EXART.ARTOMS, EXART.LEVNR, EXART.ARTFAKNPR, EXART.ARTFAKPR,
EXART.TYPNR, EXART.MRKOMS, EXART.ASGOMS, EXART.ARTASS, EXART.ARTOPENPR, EXARG.ARGOMS, EXAHG.AHGOMS, EXAHG.AFDOMS, EXARG.ARGWNKASS, EXART.ARTKOLLO

FROM EXART
JOIN EXASG ON (EXART.ASGOMS = EXASG.ASGOMS)
JOIN EXARG ON (EXASG.ARGOMS = EXARG.ARGOMS)
JOIN EXAHG ON (EXARG.AHGOMS = EXAHG.AHGOMS)

LEFT OUTER JOIN EXARV ON (EXART.ARTNR = EXARV.ARTNR AND EXARV.FILNR = :FILNR)
LEFT OUTER JOIN EXVRD ON (EXART.ARTNR = EXVRD.ARTNR AND EXVRD.FILNR = :FILNR)
WHERE ( ((EXARG.ARGWNKASS <> '') and (EXARG.ARGWNKASS is not null) and (EXART.ARTASS <= ARGWNKASS))
OR (ARVVRVRD + TEBEST + INBESTI + INBESTE < 0 )
OR ((ARVVRVRD + TEBEST + INBESTI + INBESTE) < (ARVVRDOPT - ARVVRDAFW))
OR (EXARV.ARVVRD < (ARVVRDOPT - ARVVRDAFW)))

INTO :ARTNR, :FIL, :VRD, :VRD_OPT, :VRD_AFW, :VRVRD, :GERES, :INBEST, :TEBEST, :ARGWNKASS,
:ARTOMS, :LEVNR, :ARTFAKNPR, :ARTFAKPR, :TYPNR, :MRKOMS, :ASGOMS, :ARTASS, :OPENPR, :ARGOMS, :AHGOMS, :AFDOMS, :WNKASS, :ARTKOLLO



DO
BEGIN
NUBEST = 0;
ARTVRK = 0;
ADV_AANT = 0;
W1 = 0;
W2 = 0;
W3 = 0;
W4 = 0;
W5 = 0;
IF (VRD IS NULL) THEN VRD = 0;
IF (VRVRD IS NULL) THEN VRVRD = 0;
IF (VRD_OPT IS NULL) THEN VRD_OPT = 0;
IF (VRD_AFW IS NULL) THEN VRD_AFW = 0;
IF (GERES IS NULL) THEN GERES = 0;
IF (INBEST IS NULL) THEN INBEST = 0;
IF (TEBEST IS NULL) THEN TEBEST = 0;
IF (ARGWNKASS IS NULL) THEN
IF ((ARTASS <= WNKASS) AND (WNKASS IS NOT NULL) ) THEN ARGWNKASS = 1; ELSE ARGWNKASS = 0;

IF (FIL IS NULL) THEN FIL = :FILNR;


BEGIN

IF (SOORT = 'BE') THEN
IF (VRVRD + TEBEST + INBEST - ARGWNKASS - VRD_OPT < 0) THEN
ADV_AANT = (VRD_OPT - (VRD + TEBEST + INBEST - ARGWNKASS));

IF (SOORT = 'BG') THEN
IF (VRVRD + GERES + TEBEST + INBEST - ARGWNKASS - VRD_OPT < 0) THEN
ADV_AANT = (VRD_OPT - (VRD + TEBEST + INBEST - GERES - ARGWNKASS));


IF (ADV_AANT > 0) THEN
BEGIN
BEGIN
SELECT LEVOMDOOS, LEVNAAM, LEVBESWYZ FROM EXLEV
WHERE LEVNR = :LEVNR
INTO :LEVOMDOOS, :LEVNAAM, :LEVBESWYZ;
END
IF (OPENPR = 'J') THEN
ADV_AANT= 0;

IF ((LEVOMDOOS = 'J') AND (ARTKOLLO <> 0)) THEN
BEGIN
IF (MOD(ADV_AANT, ARTKOLLO) > 0) THEN
NUBEST = (((ADV_AANT/ARTKOLLO) + 1)*ARTKOLLO);
ELSE
NUBEST = ((ADV_AANT/ARTKOLLO)*ARTKOLLO);
END
ELSE
NUBEST = ADV_AANT;

END
END
IF (ADV_AANT > 0) THEN SUSPEND;
END
END



IF ((SOORT = 'VK') AND (FILNR <> 'T')) THEN
BEGIN
FOR
SELECT ARTNR, KASOMZFIL
FROM EXVRH
WHERE KASOMZFIL = :FILNR
AND VRHSRT = 'V'
AND ARTFAKDAT >= CAST('TODAY' AS DATE) - AGEN
GROUP BY ARTNR, KASOMZFIL
INTO :ARTNR, :FIL
DO
BEGIN
NUBEST = 0;
ADV_AANT = 0;
VRVRD=0;
GERES=0;
INBEST=0;
TEBEST=0;
ARGWNKASS=0;
W1 = 0;
W2 = 0;
W3 = 0;
W4 = 0;
W5 = 0;
ARTVRK = 0;
VRVRD = 0;
BEGIN
SELECT ARVVRD, ARVVRDOPT, ARVVRDAFW
FROM EXARV
WHERE ARTNR = :ARTNR
AND FILNR = :FILNR
GROUP BY ARVVRD, ARVVRDOPT, ARVVRDAFW
INTO :VRD, :VRD_OPT, :VRD_AFW;
END

BEGIN
SELECT EXVRD.ARVVRVRD, EXVRD.ARVGERES, (EXVRD.INBESTI + EXVRD.INBESTE), EXVRD.TEBEST, EXVRD.WNKASS
FROM EXVRD
WHERE FILNR = :FILNR
AND ARTNR = :ARTNR
INTO :VRVRD, :GERES, :INBEST, :TEBEST, :ARGWNKASS;
END
FOR
SELECT ARTFAKDAT, ARTVRK
FROM EXVRH
WHERE KASOMZFIL = :FILNR
AND ARTNR = :ARTNR
AND VRHSRT = 'V'
AND ARTFAKDAT >= CAST('TODAY' AS DATE) - AGEN
INTO :ARTFAKDAT, :ARTVRK
DO
BEGIN
IF (ARTFAKDAT >= CAST('TODAY' AS DATE) - 7) THEN W1 = W1 + ARTVRK;
ELSE
IF (ARTFAKDAT >= CAST('TODAY' AS DATE) - 14) THEN W2 = W2 + ARTVRK;
ELSE
IF (ARTFAKDAT >= CAST('TODAY' AS DATE) - 21) THEN W3 = W3 + ARTVRK;
ELSE
IF (ARTFAKDAT >= CAST('TODAY' AS DATE) - 28) THEN W4 = W4 + ARTVRK;
ELSE
IF (ARTFAKDAT >= CAST('TODAY' AS DATE) - 35) THEN W5 = W5 + ARTVRK;


IF (ARTVRK <> 0) THEN
BEGIN
ADV_AANT = ADV_AANT + ARTVRK;
END
END

IF (VRD IS NULL) THEN VRD = 0;
IF (VRVRD IS NULL) THEN VRVRD = 0;
IF (VRD_OPT IS NULL) THEN VRD_OPT = 0;
IF (VRD_AFW IS NULL) THEN VRD_AFW = 0;
IF (GERES IS NULL) THEN GERES = 0;
IF (INBEST IS NULL) THEN INBEST = 0;
IF (TEBEST IS NULL) THEN TEBEST = 0;
IF (ARGWNKASS IS NULL) THEN ARGWNKASS = 0;

IF (ADV_AANT <> 0) THEN
BEGIN
BEGIN
SELECT * FROM SP_ARTGEG(:ARTNR)
INTO :ARTOMS, :LEVNR, :ARTFAKNPR, :ARTFAKPR, :TYPNR, :MRKOMS, :ASGOMS, :ARTASS, :ARTKOLLO, :LEVOMDOOS, :LEVNAAM,
:LEVBESWYZ,:OPENPR, :ARGOMS, :AHGOMS, :AFDOMS;
END
IF (OPENPR = 'J') THEN
ADV_AANT= 0;

IF ((LEVOMDOOS = 'J') AND (ARTKOLLO <> 0)) THEN
BEGIN
IF (MOD(ADV_AANT, ARTKOLLO) > 0) THEN
NUBEST = (((ADV_AANT/ARTKOLLO) + 1)*ARTKOLLO);
ELSE
NUBEST = ((ADV_AANT/ARTKOLLO)*ARTKOLLO);
END
ELSE
NUBEST = ADV_AANT;
END
IF (ADV_AANT <> 0) THEN SUSPEND;

END
END


IF (FILNR = 'T') THEN
BEGIN
FOR
SELECT EXARV.ARTNR, EXARV.FILNR, EXARV.ARVVRD, EXARV.ARVVRDOPT, EXARV.ARVVRDAFW, EXVRD.ARVVRVRD, EXVRD.ARVGERES, (EXVRD.INBESTI + EXVRD.INBESTE), EXVRD.TEBEST, EXVRD.WNKASS
FROM EXARV LEFT OUTER JOIN EXVRD ON (EXARV.ARTNR = EXVRD.ARTNR AND EXARV.FILNR = EXVRD.FILNR)
WHERE ((ARVVRVRD + TEBEST + INBESTI + INBESTE < 0 )
OR ((ARVVRVRD + TEBEST + INBESTI + INBESTE) < (ARVVRDOPT - ARVVRDAFW))
OR (ARVVRD < (ARVVRDOPT - ARVVRDAFW)))


INTO :ARTNR, :FIL, :VRD, :VRD_OPT, :VRD_AFW, :VRVRD, :GERES, :INBEST, :TEBEST, :ARGWNKASS

DO
BEGIN
ARTVRK = 0;
ADV_AANT = 0;
NUBEST = 0;
W1 = 0;
W2 = 0;
W3 = 0;
W4 = 0;
W5 = 0;
IF (VRD IS NULL) THEN VRD = 0;
IF (VRVRD IS NULL) THEN VRVRD = 0;
IF (VRD_OPT IS NULL) THEN VRD_OPT = 0;
IF (VRD_AFW IS NULL) THEN VRD_AFW = 0;
IF (GERES IS NULL) THEN GERES = 0;
IF (INBEST IS NULL) THEN INBEST = 0;
IF (TEBEST IS NULL) THEN TEBEST = 0;
IF (ARGWNKASS IS NULL) THEN ARGWNKASS = 0;


IF (SOORT = 'VG') THEN
IF ((VRVRD + GERES + TEBEST + INBEST) < (VRD_OPT - VRD_AFW) ) THEN
ADV_AANT = (VRD_OPT - (VRD + TEBEST + INBEST - GERES));

IF (SOORT = 'VE') THEN
IF ((VRVRD + TEBEST + INBEST) < (VRD_OPT - VRD_AFW)) THEN
ADV_AANT = (VRD_OPT - (VRD + TEBEST + INBEST));

IF (SOORT = 'BG') THEN
IF (VRVRD + GERES + TEBEST + INBEST < 0) THEN
ADV_AANT = (VRD_OPT - (VRD + TEBEST + INBEST - GERES));

IF (SOORT = 'BE') THEN
IF (VRVRD + TEBEST + INBEST < 0) THEN
ADV_AANT = (VRD_OPT - (VRD + TEBEST + INBEST));

IF (ADV_AANT > 0) THEN
BEGIN

BEGIN
SELECT * FROM SP_ARTGEG(:ARTNR)
INTO :ARTOMS, :LEVNR, :ARTFAKNPR, :ARTFAKPR, :TYPNR, :MRKOMS, :ASGOMS, :ARTASS, :ARTKOLLO, :LEVOMDOOS, :LEVNAAM, :LEVBESWYZ,:OPENPR, :ARGOMS, :AHGOMS, :AFDOMS;
END
IF (OPENPR = 'J') THEN
ADV_AANT= 0;

IF ((LEVOMDOOS = 'J') AND (ARTKOLLO <> 0)) THEN
BEGIN
IF (MOD(ADV_AANT, ARTKOLLO) > 0) THEN
NUBEST = (((ADV_AANT/ARTKOLLO) + 1)*ARTKOLLO);
ELSE
NUBEST = ((ADV_AANT/ARTKOLLO)*ARTKOLLO);
END
ELSE
NUBEST = ADV_AANT;

END

IF (ADV_AANT > 0) THEN SUSPEND;

END
END
END

Thanking you for your time and help in advance.

Kind regards
================================================== ======
Reply With Quote
  #2  
Old 03-09-2008, 11:29 PM
Nate1 Nate1 is offline
Senior Member
 
Join Date: Apr 2006
Posts: 284
Default RE: Translating SP from Interbase to SQL Server 2005

Been a while since I read up on the different types of sps in SQL2005 but you don't need the returns statement, but you can use it.

basic layout

Create procedure [dbo].[sp_besadv]
(
ARTVRK INT,
ARTFAKDAT DATE,
OPENPR CHAR(1),
WNKASS CHAR(1)
)

Do begin
SELECT
EXARV.ARTNR as ARTNR,....


WHERE CONDITIONS


End

Personally Id simplify that where you can, its pretty messy then test the output as you build it, and lookup inline stored procedures (I think) the syntax should be alot easier to read than the version you've given. THe Select Statement will envoke the return of data, and you can call the function using something like


exec sp_besadv @ARTVRK=123, @ARTFAKDAT=#Dateinfo# ...


Hope that helps
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT. The time now is 01:59 AM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.