Stored procedure for case?

[charanch]

Hello there, I'm using the following in active server pages that calculate prices on lumber when orders are created. The user adds an item and quantity and the calculation is made. I'm not proficient with stored procedures, but would like to know if it would make more sense to write a stored procedure to do the same thing. If so, how would I apply it? Many thanks for your thoughts.

strSql="SELECT Price,calcMethod FROM myTable WHERE itemNumber='" selectedItem & "'"
.......

price = rs("Price")
calcMethod = rs("calcMethod")

SELECT CASE calcMethod
   CASE "SF"
        'perform SF calculation on price variable         
   CASE "MBF"
        'perform MBF calculation on price variable
   CASE "CSF"
        'perform CSF calculation on price variable
END SELECT

 

[JohnDTampaBay]

You should be able to easily convert this to a stored procedure, depending on the calculation done. Assuming its just straight math with no other lookups, your stored procedure might look something like this:

CREATE PROC uspCalcItemPrice
  @ItemNumber varchar(x) -- or other appropriate datatype
AS 
  SELECT CASE calcMethod
           WHEN 'SF'
                 THEN price * 2 --perform SF calculation
           WHEN 'MBF'
                 THEN price * 3 --perform MBF calculation
           WHEN 'CSF'
                 THEN price * 4 --perform CSF calculation
         END AS CalcPrice
  FROM myTable
  WHERE itemNumber = @ItemNumber
  .......

Call the procedure and pass selectedItem as the parameter value. You should get back a result set containing the calculated price for the item number selected. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[charanch]

Many thanks, John. I'll give it a try and we'll see how smart I am, ha!