CODEWITHSUNDEEP
sqlsql-server
bheesham sharma
bheesham sharma

Reputation: 11

check value before update in table

I am writing the stored procedure, if account is lock(Y) then unlock (N) But before that i want check,if it already unlock, then i want to give TRUE or FALSE message.

Below is update query

    create procedure sp_test
@lock = char(1)
    as 
    begin
     set nocount on;

    BEGIN TRANSACTION

        update table1 set isLock= @Lock where id=1
    END

Upvotes: 0

Views: 1556

Answers (2)

mordechai
mordechai

Reputation: 849

you can Use OUTPUT Clause, it can apply also on multiple rows 

DECLARE @state TABLE (isLock BIT)
update table1 set isLock= @Lock OUTPUT Deleted.IsLock INTO @state where id=1
-- check @state table variable

Upvotes: 0

Serg
Serg

Reputation: 22811

Update only when current value is different and check if any row was updated

    update table1 
    set isLock= @Lock 
    where id = 1 and (isLock is null or isLock != @Lock);
    if (@@rowcount = 1)
     -- the row was changed
    else
     --  no change

Upvotes: 1

Related Questions