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