[DUG] debugging store procedure using Delphi 5

[Neven MacEwan]

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: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject: unsubscribe