0

I have a Stored Procedure in Sybase that selects a bunch of columns. The result set contains multiple rows. I want to modify one of the columns in the result set before the Store Procedure returns. Specifically, I want to apply another Stored Procedure to the column for each row.

I think that the only way to do this is to use a cursor (which I know of but do not how how to use) to iterate over each row of the result set, and in the body of the cursor modify the column and then....

I am not sure what to do next. Do I need to insert the modified column back into the result set? I believe that the result set lives inside a temporary table called DUAL, but I am not 100% sure about this.

This is my code, to give you some context:

CREATE PROCEDURE dbo.ivr_get_product_details
@skn            VARCHAR(6),
@calling_country VARCHAR(20)
AS
    BEGIN

        select  p.SKU, size.DESP as SIZE_DESC,colour.DESP as COLOUR_DESC 
        into #COLOUR_AND_SIZE_DESCRIPTIONS
        from PRODUCT p, PRODUCT_MATRIX size, PRODUCT_MATRIX colour 
        where p.DEPARTMENT = size.DEPARTMENT and p.DEPARTMENT =  colour.DEPARTMENT and substring(p.SKU,7,3) = colour.MATRIX and substring(p.SKU,10,3) = size.MATRIX
        and p.SKN = @skn and colour.TYPE = '1' and size.TYPE ='2'


        select  p.CLIENT, p.SKU, p.TTS_NAME, s.QTY, case when @calling_country = "AUSTRALIA" then p.PRICE else p.NON_GST_PRICE end as PRICE,
                p.PART_PAY, p.NO_INSTALS, p.MATRIX_FLAG, p.STS, p.DEPARTMENT, cs.COLOUR_DESC, cs.SIZE_DESC
        from PRODUCT p, STOCK s, #COLOUR_AND_SIZE_DESCRIPTIONS cs
        where p.SKN = @skn  and p.SKU = s.SKU and p.PRICE > 0.5 and p.IVR_ACTIVE = "Y" and s.WAREHOUSE = 1 and p.STS not in ("K","B")
        and p.SKU = cs.SKU

    END

And this is the code of the other Stored Procedure:

CREATE PROCEDURE dbo.get_foreign_price

 @a_country         VARCHAR(30) ,
 @a_local_amt       NUMERIC(10,2) ,
 @a_foreign_price   NUMERIC(10,2) OUTPUT

AS

  SET NOCOUNT ON 
  DECLARE 
    @l_rate             NUMERIC(12,6) ,
    @l_foreign_price    NUMERIC(10,2),
    @l_remain           NUMERIC(10,2)

Begin 

    EXEC web_get_foreign_price @a_country = @a_country, @a_local_amt = @a_local_amt,
                  @a_foreign_price = @a_foreign_price OUTPUT 

    Set @l_foreign_price = @a_foreign_price
    Select @l_foreign_price From DUAL


END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.