Vikas
Short Answer is No, Its only when you have to do this sort of thing
you realise how good the Delphi IDE is. Use IsqlW (the command console)
to create and debug your SP's here is one of mine which shows
how i do it,
/* Task can be
'WebOrder' @@OrderID is JI_WebOrder.WebOrderID
'SalesOrder' @@OrderID is SO_Main.InvoiceID
*/
IF NOT OBJECT_ID('spJIOrderFreight') IS NULL DROP PROCEDURE spJIOrderFreight
go
CREATE PROCEDURE spJIOrderFreight @@Task VARCHAR(30),
@@OrderID VARCHAR(20)
AS BEGIN
DECLARE
@DebtorID VARCHAR(20),
@Error VARCHAR(255),
@Weight REAL,
@Freight REAL,
@Freight1 REAL,
@Freight2 REAL,
@Value REAL,
@Policy VARCHAR(50),
@FreightInvID VARCHAR(20)
-- SET @Time = GETDATE()
SET @Error = NULL
-- SET @Today = CONVERT(SMALLDATETIME, FLOOR(CAST(GETDATE() AS FLOAT)))
--
IF @@Task = 'WebOrder' BEGIN -- @@OrderID is JI_WebOrder
-- Calculate the weight and value of the order
SELECT
@DebtorID = MAX(a.DebtorID),
@Value =
SUM(b.Quantity*b.Price*CAST(dbo.fMWKGetCustomValue('IN_Main',
c.InventoryID, 'PackSize') AS REAL)),
@Weight =
SUM(b.Quantity*c.Weight*CAST(dbo.fMWKGetCustomValue('IN_Main',
c.InventoryID, 'PackSize') AS REAL))
FROM
JI_WebOrder a JOIN
JI_WebOrderItem b ON b.WebOrderID = a.WebOrderID JOIN
IN_Main c ON c.InventoryID = b.InventoryID
WHERE
a.WebOrderID = @@OrderID AND
ISNUMERIC(dbo.fMWKGetCustomValue('IN_Main', c.InventoryID,
'PackSize')) = 1
END
IF @@Task = 'SalesOrder' BEGIN -- @@OrderID is SO_Main.InvoiceID
-- Calculate the weight and value of the order
SELECT
-- c.*
@DebtorID = MAX(a.DebtorID),
@Value = SUM(c.QuantityOrdered*c.ItemPrice),
@Weight = SUM(c.QuantityOrdered*d.Weight)
FROM
SO_Main a JOIN
SO_History b ON b.InvoiceID = a.InvoiceID JOIN
SO_Lines c ON c.InvoiceHistoryID = b.InvoiceHistoryID JOIN
IN_Main d ON d.InventoryID = c.InventoryID
WHERE
a.InvoiceID = @@OrderID AND
b.HistoryNo = 1 AND
c.TypeKitRounding = 0 AND
c.KittingStatus < 2
-- select top 1 * FROM SO_Main where InvoiceNo = '556505'
END
SET @Policy = 'Default Freight'
-- If the debtor has a freight part no use that, either on weight or value
SET @FreightInvID = dbo.fMWKGetCustomValue('Debtor', @DebtorID,
'FreightPartNo')
IF EXISTS (SELECT 1 FROM IN_Main WHERE InventoryID = @FreightInvID) BEGIN
PRINT 'Using Debtor Freight Part No'
SELECT @Policy = DebtorPartNo
FROM IN_DebtorPartNumbers a
WHERE
a.InventoryID = @FreightInvID AND
a.DebtorID = @DebtorID
-- Calculate by weight
EXEC spJIWebPrice @DebtorID, NULL, @FreightInvID, @Weight, @Freight1
OUTPUT
print @Freight1
-- Calculate by value
EXEC spJIWebPrice @DebtorID, NULL, @FreightInvID, @Value, @Freight2
OUTPUT
print @Freight2
IF @Freight1 < @Freight2 SET @Freight = @Freight1 ELSE SET @Freight
= @Freight2
GOTO OutputFreight
END
SET @Freight =
CASE
WHEN @Weight <= 5.00 THEN 7.50
ELSE ((FLOOR((@Weight-5.001)/5.00)+1)*2.5) + 7.50
END
IF @Freight > 25.00 SET @Freight = 25.00
IF DB_NAME() = 'JIF_AU' SET @Freight = 0.00
OutputFreight:
SELECT @Freight AS Freight, @Weight AS Weight, @Value AS Value,
@Policy AS Policy
RETURN
PRINT 'Invalid task'
END
go
EXEC spJIOrderFreight 'WebOrder', '1EEC7A90BB024F08BE3B'
go
--EXEC spJIOrderFreight 'SalesOrder', '0D2801C6BEF44B3E8CD3'
go
--EXEC spJIOrderFreight 'SalesOrder', 'CBDB370358144FC49D65'
Hi,
I have one query, I am using Delphi 5 and sql server 2000 as back end
I want to know is there any way i can debug my store procedure from
Delphi 5
well i am ignorant in thsi case sicne i dont find a way to debug SP in
sql server 2000 apart from using print statement
Please anyone can help me in this
Regards
Vikas
--
vikas
------------------------------------------------------------------------
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: [email protected]
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: [email protected]
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
