write stored procedure for this table

selastin bel

12y1.1k

->please send the update query Stored Procedure
its my table
ID name task Shift Status
1 Selastin DragAndDrop Morning Pending
2 Rasim CopyPaste   Evening
3 Robin updateAntivirus Morning Pending
4 Dileep insertTask Morning
5 Binu Automaticupdation Evening

SAMPLE OUTPUT
ID name task Shift Status

1 Rasim DragAndDrop Evening Pending
2   Rasim CopyPaste Evening pending
3    Binu   updateAntivirus Evening
4   Binu   insertTask Evening
5 Binu Automaticupdation Evening

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Khan Abrar Ahmed12y ago
Hi Try this

DECLARE @ShiftType VARCHAR(64)='Evening' --Change this or pass the parameter as per shift

DECLARE @table AS TABLE (ID INT,NAME VARCHAR(64),task VARCHAR(64),[Shift] VARCHAR(64),[Status] VARCHAR(64) NULL)
DECLARE @tablePending AS TABLE (ID INT,NAME VARCHAR(64),task VARCHAR(64),[Shift] VARCHAR(64),[Status] VARCHAR(64) NULL)
INSERT INTO @table
        ( ID, NAME, task, Shift, Status )
VALUES
(1 ,      'Selastin' ,          'DragAndDrop'        ,                       'Morning' ,      'Pending' ) ,
(2 ,      'Rasim' ,           'CopyPaste'   ,                                 'Morning',      'Pending'   ) ,
(3 ,      'Robin' ,             'updateAntivirus' ,                            'Evening',NULL),
(4 ,     'Dileep' ,           'insertTask' ,                                   'Evening',NULL),
(5 ,     'Binu',                'Automaticupdation' ,                       'Evening',null)
SELECT * FROM @table
IF(@ShiftType ='Evening')
BEGIN
DELETE from @tablePending
INSERT INTO @tablePending
   SELECT * FROM @table WHERE Shift='Evening'

UPDATE @table SET NAME=tp.NAME,Shift=tp.Shift
From @tablePending AS tp
LEFT JOIN @table AS t ON t.Status='Pending'
END
Else IF(@ShiftType ='Morning')
BEGIN

UPDATE @table SET Shift='Morning' WHERE Status='Pending'
DELETE from @tablePending
INSERT INTO @tablePending
   SELECT * FROM @table WHERE Shift='Morning'

UPDATE @table SET NAME=tp.NAME,Shift=tp.Shift
From @tablePending AS tp
LEFT JOIN @table AS t ON t.Status='Pending'
END

SELECT * FROM @table
selastin bel12y ago
my problem is morning pending task update the evening of user. (condition:morning user not assign the shift of evening) please try Stored procedure
sudipta sanyal12y ago
i thing Khan Abrar Ahmed was already give the answer on this same topic.

please describe your problem briefly.

http://www.c-sharpcorner.com/Forums/Thread/256907/update-query-in-stored-procedure.aspx