Pages

Men

rh

10/04/2013

how to write cursor with in procedure IN Sql Server


CREATE PROCEDURE UDP_UPDATE_ORDERMASTER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
  
DECLARE @JOBNO INT
DECLARE @NOOFITEMS INT
DECLARE @QTY BIGINT

DECLARE CUR_ERRORDETAILS CURSOR FOR

SELECT JOBNO,NOOFITEMS,QTY FROM  dbo.ERRDETAILS

OPEN CUR_ERRORDETAILS

FETCH NEXT FROM  CUR_ERRORDETAILS INTO  @JOBNO,@NOOFITEMS,@QTY

WHILE @@FETCH_STATUS =0
BEGIN

         UPDATE  dbo.SERVICEMASTER SET AMOUNT = 0 WHERE JOBNO = @JOBNO
UPDATE  dbo.SERVICEMASTER SET AMOUNT = (@NOOFITEMS *@QTY/100)+12 WHERE JOBNO= @JOBNO
FETCH NEXT FROM CUR_ERRORDETAILS INTO @JOBNO,@NOOFITEMS,@QTY


        END

       CLOSE CUR_ERRORDETAILS

       DEALLOCATE CUR_ERRORDETAILS

SELECT * FROM SERVICEMASTER

  
END
GO
EXEC UDP_UPDATE_ORDERMASTER

No comments :

Post a Comment