Procedure to capture multiple parameters

[willdevelope]

Ex here is my code

@ITEM nvarchar(25)allows me input one parameter at time

HERE IS THE PROBLEM: There is over 5000 parameter I would have to enter each time.. TOO MANY


How can I write script to capture data from this table as parameters that need to be entered without me typing each item #

HERE is example of the table that has the parameter

Test[Field] Item
E0073201
E0156402
E0182901
E02297C0
E0270201



CREATE PROCEDURE [dbo].[MR_UWTITIUMUPDATE]
(
@ITEM nvarchar(25)


)
AS
BEGIN




--- UPDATE USER_DEF7 = USER_DEF7 * CS CONVERSION QTY -----
UPDATE ITEM_UNIT_OF_MEASURE
SET ITEM_UNIT_OF_MEASURE.USER_DEF7 = ITEM_UNIT_OF_MEASURE.USER_DEF7 * TB11.CONVERSION_QTY
FROM ITEM_UNIT_OF_MEASURE
INNER JOIN ITEM_UNIT_OF_MEASURE AS TB11 ON ITEM_UNIT_OF_MEASURE.ITEM = TB11.ITEM AND
TB11.QUANTITY_UM = 'CS'
WHERE ITEM_UNIT_OF_MEASURE.QUANTITY_UM IN ('CS','PL') AND
ITEM_UNIT_OF_MEASURE.ITEM = @ITEM

--- UPDATE PL ENTRY PL CONVERSIO QTY / CS CONVERSION QTY -----
UPDATE ITEM_UNIT_OF_MEASURE
SET ITEM_UNIT_OF_MEASURE.CONVERSION_QTY = ITEM_UNIT_OF_MEASURE.CONVERSION_QTY / TB11.CONVERSION_QTY
FROM ITEM_UNIT_OF_MEASURE
INNER JOIN ITEM_UNIT_OF_MEASURE AS TB11 ON ITEM_UNIT_OF_MEASURE.ITEM = TB11.ITEM AND
TB11.QUANTITY_UM = 'CS'
WHERE ITEM_UNIT_OF_MEASURE.QUANTITY_UM = 'PL' AND
ITEM_UNIT_OF_MEASURE.ITEM = @ITEM

--- UPDATE CS CONVERSION QTY TO 1 -----
UPDATE ITEM_UNIT_OF_MEASURE SET CONVERSION_QTY = 1
WHERE ITEM_UNIT_OF_MEASURE.QUANTITY_UM = 'CS' AND
ITEM_UNIT_OF_MEASURE.ITEM = @ITEM

--- DELETE PK UNIT OF MEASURE -----
DELETE FROM ITEM_UNIT_OF_MEASURE
WHERE ITEM_UNIT_OF_MEASURE.QUANTITY_UM = 'PK' AND
ITEM_UNIT_OF_MEASURE.ITEM = @ITEM

--- UPDATE ITEM USER_DEF7 = IUM USER_DEF7 -----
UPDATE ITEM
SET ITEM.USER_DEF7 = ITEM_UNIT_OF_MEASURE.USER_DEF7
FROM ITEM
INNER JOIN ITEM_UNIT_OF_MEASURE ON ITEM.ITEM = ITEM_UNIT_OF_MEASURE.ITEM AND
ITEM_UNIT_OF_MEASURE.QUANTITY_UM = 'CS'
WHERE
ITEM_UNIT_OF_MEASURE.ITEM = @ITEM

--- UPDATE ITEM_CLASS -----
UPDATE ITEM
SET ITEM.ITEM_CLASS =
CASE ITEM_CLASS
WHEN '1 HI (PK,CS,PL)' THEN '1 HI (CS,PL)'
WHEN '2 HI (PK,CS,PL)' THEN '2 HI (CS,PL)'
WHEN '3 HI (PK,CS,PL)' THEN '3 HI (CS,PL)'
WHEN '4 HI (PK,CS,PL)' THEN '4 HI (CS,PL)'
WHEN '5 HI (PK,CS,PL)' THEN '5 HI (CS,PL)'
END
WHERE
ITEM.ITEM = @ITEM
END

 

[SQLSister]

First don't reuse that code if you need to enter 5000 items, create set based code instead. It will take far lesss time to process than if you cursor through 5000 items one at time.
How do you know what the 5000 items are? are they in a table somewhere? CAn they be put into a table?

If so then join to that table for your updates.

If you are determined to use this silly one record ata time code, then use the table items as the select for a cursor and call the code repetively in that cursor. It is bad for performance, so I would really rewrite unless this is a one time deal.

"NOTHING is more important in a database than integrity." ESquared

 

[willdevelope]

Yes, the data is in table and this is just a one time thing

Could you give me a sample to look at how you would write it?