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
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment