how to handle optional parameters in a update stored procedure

I need to write a generic procedure that set value for one column or no of a columns in a table depending on parameters. Any ide开发者_C百科a how to do it.

I would guess you want something like:

CREATE PROC UpdateProc  
@RowID UNIQUEIDENTIFIER,
@Parameter1 NVARCHAR(50) NULL,
@Parameter2 INT NULL

AS

SET NOCOUNT ON
GO

IF @Parameter1 IS NOT NULL
BEGIN 
    UPDATE MyTable
    SET Column1 = @Parameter1
    WHERE ID = @RowID
END

IF @Parameter2 IS NOT NULL
BEGIN
    UPDATE MyTable
    SET Column2 = @Parameter2
    WHERE ID = @RowID
END

It doesn't feel particularly elegant but if you don't know/can't guarantee which parameters will be passed I don't know of a better way than to test them in turn for NULL.