SQL Server stored procedure multiple IF statements -


i have sql server stored procedure 3 conditions. currently, first if block execute no matter parameters:

set ansi_nulls on go set quoted_identifier off go  /* description: assigns/reassigns orders in [gis].[dbo].[qcorders] parameters:     @fldrinitkey    varchar(30) = null,     @username       varchar(20) = null,     @qctrack        decimal(6,2),     @wf             bit,     @idqc           int         = null returns:     @rc 0 (success), 1 (failure) example:     exec spqcassignorders '889g1pt', 'arajendran', '2016.10', '0', null modifications:     03/10/2016  a.rajendran created */  alter procedure [dbo].[spqcassignorders]     @fldrinitkey varchar(30) = null,     @username    varchar(20) = null,     @qctrack     decimal(6,2),     @wf          bit,     @idqc        int = null     set nocount on     set rowcount 0     set xact_abort on  begin try     /* declare , initialize variables */     declare @rc int, @trancount int      select          @rc = 0, @trancount = @@trancount      if @trancount = 0      begin transaction         if @idqc not null         begin             update [gis].[dbo].[qcorders]             set username = @username,                 assigneddte = case                                    when @username null                                      null                                     else getdate()                               end,                 unable = null,                 error = null,                 completeddate = null,                 admincheck = null                              idqc = @idqc         end          if @fldrinitkey not null         begin             update [gis].[dbo].[qcorders]             set username = @username,                 assigneddte = getdate()                                qctrack = @qctrack                 , fldrinitkey = @fldrinitkey                 , wellsfargo = @wf                 , username null                 , unable <> 1         end          if @fldrinitkey null         begin             update [gis].[dbo].[qcorders]             set username = @username,                 assigneddte = getdate()                                qctrack = @qctrack                 , wellsfargo = @wf                 , username null                 , unable <> 1         end          if @trancount = 0 , (xact_state()) = 1             commit transaction     end try     begin catch         if (xact_state()) = -1              rollback transaction          exec [tod].[dbo].[sprethrowerror]         set @rc = 1     end catch      set nocount off     set rowcount 0      return @rc   grant execute on [gis].[dbo].[spqcassignorders] db_allowexec 

if want execute this:

exec spqcassignorders '889g1pt', 'arajendran', '2016.10', '0', null 

the second if statement should execute, doesn't. why?

clarification

second if statement:

if @fldrinitkey not null begin     update [gis].[dbo].[qcorders]      set username = @username,         assigneddte = getdate()               qctrack = @qctrack         , fldrinitkey = @fldrinitkey         , wellsfargo = @wf         , username null         , unable <> 1 end 

you simplify this. 3 update statements can turned single update statement. should equivalent of three.

update [gis].[dbo].[qcorders]                 set                      username    = @username,                     assigneddte =                      case                             when @idqc not null , @username null null                         else getdate()                     end                qctrack         = @qctrack                     , fldrinitkey = isnull(@fldrinitkey, fldrinitkey)                     , wellsfargo  = @wf                     , username null                     , unable <> 1 

Comments