CODEWITHSUNDEEP
sqlsql-servert-sql
GeoVIP
GeoVIP

Reputation: 1564

Update table from stored procedure

Have stored procedure where I update my table :

UPDATE myTbl
SET [first_name] = @first_name,  
    [second_name] = @second_name,  
    [faculty] = @faculty,
    [age] = @age
    WHERE [id] = @id

When I call a stored procedure and when passed all of the parameters that update works. Now I want to update the table so if I do not enter all the values ​​to update, updated, only those which I introduced, and which are not introduced remained the same.

Upvotes: 0

Views: 129

Answers (3)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

Right before you run the update, you can run a SELECT statement to extract all of the values from the table.

Also, you verify all of the values passed to the stored procedure, and if they're null, you use the selected values in your update statement.

Upvotes: 0

Serge
Serge

Reputation: 6712

UPDATE myTbl
SET
    [first_name] = ISNULL(@first_name,  [first_name])
    , [second_name] = ISNULL(@second_name,  [second_name])
    , [faculty] = ISNULL(@faculty, [faculty])
    , [age] = ISNULL(@age, [age])
WHERE [id] = @id

Upvotes: 3

Shan Plourde
Shan Plourde

Reputation: 8726

UPDATE myTbl
SET [first_name] = IsNull(@first_name, first_name),  
    [second_name] = IsNull(@second_name, second_name),  
    [faculty] = IsNull(@faculty, faculty),
    [age] = IsNull(@age, age),
    WHERE [id] = @id

Upvotes: 5

Related Questions