sql server 2008 - Retaining existing table values using a Stored Procedure with Optional Parameter Values -

i have scenario ---- stored procedure may given parameter values optionally. if values empty/default retain existing values.

is following way of handling case statements correct? works me there better way this?

create  procedure [updateuser]         (        @userid int,   @userkey varchar(32),       @username varchar(50),        @categoryid int = 0,    )                 begin         set nocount on         update [users]        set     [userkey] =  (case when (len(rtrim(ltrim(@userkey)))>0) @userkey else userkey end )     ,[username] = (case when (len(rtrim(ltrim(@username)))>0) @username else username end )          ,[categoryid] = (case when (@categoryid>0) @categoryid else categoryid end )                  [userid] = @userid        end 

one "better" (in terms of syntax simplicity) way use nullif() , isnull/coalesce instead of case expressions:

update [users]       set    userkey    = coalesce(nullif(@userkey   , ''), userkey   ),   username   = coalesce(nullif(@username  , ''), username  ),   categoryid = coalesce(nullif(@categoryid, 0 ), categoryid)                userid = @userid ; 

to explain absence of ltrim() , rtrim(), trailing spaces ignored in string comparison in transact-sql. means string of (any number of) spaces match string of (any other number of) spaces, empty string. (and, matter, len() function ignores trailing spaces when calculating length of string, , so, instance, len(space(10)) return 0 , not 10.)