quote:

---

2) MODIFY EXISTING RECORDS

---

DECLARE @tab TABLE( orders VARCHAR(32),types VARCHAR(32),modelnumber VARCHAR(32))

CREATE PROC usp_SampleInsert
(
 @order  VARCHAR(32),@type VARCHAR(32),@modelnumber VARCHAR(32)
)
AS
SET NOCOUNT ON
BEGIN

	INSERT INTO @tab
	SELECT @order, @type, CASE WHEN @type ='R65' THEN 'QA' WHEN @type ='R55' THEN 'QB' ELSE 'QC' END
	+RIGHT('00' + CAST(number AS VARCHAR(3)), 3)
	FROM master..spt_Values m 
	WHERE TYPE ='p' and m.number>0 and number between 1 AND @modelnumber

END
SET NOCOUNT OFF


CREATE PROC usp_SampleUpdate
(
 @order  VARCHAR(32),@type VARCHAR(32),@modelnumber VARCHAR(32)
)
AS
SET NOCOUNT ON
BEGIN

DELETE FROM @TAB WHERE CAST(RIGHT(modelnumber,3) AS INT) > @modelnumber

END
SET NOCOUNT OFF