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
Post a Comment