Sql server stored procedure

Question

I have to write a update stored procedure. I will be asking paramters for all the columns ( Except id). User won’t be sending all the paramaters all the times. He wants to send paramter value NULL so don’t update that column, the columns he wants to update there will be valid value. How do I Write a stored proc for this. Basically I will have to check each parameter value and if it’s not null then only update that column. Thanks in advance!!

Answer 1

Create Procedure MyProc ( @Param1 int,
@Param2 varchar(20),
@Param3 int = NULL)
As
Begin

-- the sp

End

See @Param3 for how you specify a default value.

Answer 2

Use COALESCE, which will set the column to a new value if it is NOT NULL, or back to the same original value otherwise:

UPDATE  MyTable

SET     MyColumn1 = COALESCE(@MyColumn1, MyColumn1),
        MyColumn2 = COALESCE(@MyColumn2, MyColumn2),
        MyColumn3 = COALESCE(@MyColumn3, MyColumn3)

WHERE   ID = @ID

Answer 3

try:

CREATE PROCEDURE YourUpdate
(
    @Param_PK    int
   ,@Param1      varchar(10)=NULL
   ,@Param2      int=NULL
   ,@Param3      datetime=NULL
)


UPDATE YourTable
    SET col1=COALESCE(@Param1,col1)
       ,col2=COALESCE(@Param2,col2)
       ,col3=COALESCE(@Param3,col3)
    WHERE PK=@Param_PK

go3