Multiple IF blocks in SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple IF blocks in SP

Author

Topic

Ogriv
Starting Member

40 Posts

Posted - 2011-11-08 : 09:06:18

Greetings,

I am writing a stored procedure where I would like to be able to process 1, 2, or 3 passed parameters.
I thought that I could just create 3 seperate IF blocks but it is not working the way I intended.

Expected execution:
Each of the three passed variables are sequentially checked for a value other than NULL.
If any of the three values contain a valid value then the appropriate UPDATE query is ran.
This allows for a single call from the .NET code instead of using three seperate SP calls.

Actual execution:
When passing any one of the three parameters it works as intended.
Like this:


EXEC
	[dbo].[usp_EditSubmittedCharge]
	@PatientURN = N'9876543210',
	@CurrentDateOfCharge = '2011-06-06',
	@CurrentTimeOfCharge = '06:06:00',
	@NewDateOfCharge = NULL,
	@NewTimeOfCharge = '06:10:00',
	@NewCPTcode = NULL
GO

When more than one parameter is passed, then only the first IF statement that is TRUE will be processed.
I assumed that every IF statement would be evaluated sequentially, but it seems as though as soon as any
of the IF conditions is met, the procedure terminates.

In this example, only @NewTimeOfCharge will update


EXEC
      [dbo].[usp_EditSubmittedCharge]
      @PatientURN = N'9876543210',
      @CurrentDateOfCharge = '2011-06-06',
      @CurrentTimeOfCharge = '06:06:00',
      @NewDateOfCharge = NULL,
      @NewTimeOfCharge = '06:10:00',
      @NewCPTcode = '12345'
GO

Explanation of Passed Parameters :

@PatientURN varchar(10) -- Use to identify record to edit
@CurrentDateOfCharge as date -- Use to identify record to edit
@CurrentTimeOfCharge as time(0) -- Use to identify record to edit
@NewDateOfCharge as date -- New value or NULL
@NewTimeOfCharge as time(0) -- New value or NULL
@NewCPTcode as varchar(10) -- New value or NULL


CREATE PROCEDURE [dbo].[usp_EditSubmittedCharge] 
   @PatientURN varchar(10)
  ,@CurrentDateOfCharge as date
  ,@CurrentTimeOfCharge as time(0)
  ,@NewDateOfCharge as date 
  ,@NewTimeOfCharge as time(0)
  ,@NewCPTcode as varchar(10)

AS


-- If a new charge date was passed
IF @NewDateOfCharge IS NOT NULL 
BEGIN
       UPDATE SubmittedCharges
          SET DateOfCharge=@NewDateOfCharge
        WHERE SubmittedCharges.URN = @PatientURN 
              AND SubmittedCharges.DateOfCharge = @CurrentDateOfCharge
              AND SubmittedCharges.TimeOfCharge = @CurrentTimeOfCharge
END

-- If a new charge time was passed
IF @NewTimeOfCharge IS NOT NULL 
BEGIN
       UPDATE SubmittedCharges
          SET TimeOfCharge=@NewTimeOfCharge
        WHERE SubmittedCharges.URN = @PatientURN 
              AND SubmittedCharges.DateOfCharge = @CurrentDateOfCharge
              AND SubmittedCharges.TimeOfCharge = @CurrentTimeOfCharge
END

-- If a new CPT code was passed
IF @NewCPTcode IS NOT NULL 
BEGIN
       UPDATE SubmittedCharges
          SET ChargeCode=@NewCPTcode
        WHERE SubmittedCharges.URN = @PatientURN 
              AND SubmittedCharges.DateOfCharge = @CurrentDateOfCharge
              AND SubmittedCharges.TimeOfCharge = @CurrentTimeOfCharge
END

This is the script to create the test table that I am working with.


USE [MobileChargeCapture]
GO
/****** Object:  Table [dbo].[SubmittedCharges]    Script Date: 11/08/2011 08:58:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SubmittedCharges](
	[URN] [varchar](15) NOT NULL,
	[DateOfCharge] [date] NOT NULL,
	[TimeOfCharge] [time](0) NOT NULL,
	[ChargeCode] [varchar](20) NOT NULL,
	[SubmittedByUser] [varchar](6) NOT NULL,
	[ChargeComplete] [varchar](1) NOT NULL,
	[DateTimeStamp] [datetime2](0) NOT NULL,
 CONSTRAINT [PK_Charges] PRIMARY KEY CLUSTERED 
(
	[URN] ASC,
	[DateOfCharge] ASC,
	[TimeOfCharge] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SubmittedCharges] ([URN], [DateOfCharge], [TimeOfCharge], [ChargeCode], [SubmittedByUser], [ChargeComplete], [DateTimeStamp]) VALUES (N'9876543210', CAST(0x55340B00 AS Date), CAST(0x00B8560000000000 AS Time), N'66666', N'ISMEB', N'N', CAST(0x00156400E9340B0000 AS DateTime2))
INSERT [dbo].[SubmittedCharges] ([URN], [DateOfCharge], [TimeOfCharge], [ChargeCode], [SubmittedByUser], [ChargeComplete], [DateTimeStamp]) VALUES (N'9876543210', CAST(0xE9340B00 AS Date), CAST(0x00782D0000000000 AS Time), N'36620', N'ISMEB', N'N', CAST(0x00616500E9340B0000 AS DateTime2))
INSERT [dbo].[SubmittedCharges] ([URN], [DateOfCharge], [TimeOfCharge], [ChargeCode], [SubmittedByUser], [ChargeComplete], [DateTimeStamp]) VALUES (N'9876543210', CAST(0xE9340B00 AS Date), CAST(0x00D0B60000000000 AS Time), N'31500', N'ISMEB', N'N', CAST(0x0088B700E9340B0000 AS DateTime2))
INSERT [dbo].[SubmittedCharges] ([URN], [DateOfCharge], [TimeOfCharge], [ChargeCode], [SubmittedByUser], [ChargeComplete], [DateTimeStamp]) VALUES (N'9876543210', CAST(0xE9340B00 AS Date), CAST(0x00A0B90000000000 AS Time), N'31500', N'ISMEB', N'N', CAST(0x002CB800E9340B0000 AS DateTime2))

Am I missing something? Can I have multiple IF's without nesting, or utilizing ELSE IF or ELSE

Thank you,
Mike

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 09:19:58

don't know why it's not working (it should) but you could have a single statement.

Put in some trace statements and log to a table to see what it is doing.

single statement

UPDATE SubmittedCharges
SET DateOfCharge=coalesce(@NewDateOfCharge,DateOfCharge)
TimeOfCharge=coalesce(@NewTimeOfCharge,TimeOfCharge)
ChargeCode=coalesce(@NewCPTcode,ChargeCode)
WHERE SubmittedCharges.URN = @PatientURN
AND SubmittedCharges.DateOfCharge = @CurrentDateOfCharge
AND SubmittedCharges.TimeOfCharge = @CurrentTimeOfCharge
and coalesce(@NewDateOfCharge, @NewTimeOfCharge, @NewCPTcode) is not null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ogriv
Starting Member

40 Posts

Posted - 2011-11-08 : 10:12:15

Your alternative works great.

I did receive a error and had to change my variable types to varchar but it works fine now. I will definately be researching 'coalsce'.


CREATE PROCEDURE [dbo].[usp_EditSubmittedChargeTEST] 
   @PatientURN varchar(10)
  ,@CurrentDateOfCharge varchar(10)
  ,@CurrentTimeOfCharge varchar(10)
  ,@NewDateOfCharge varchar(10)
  ,@NewTimeOfCharge varchar(10)
  ,@NewCPTcode varchar(10)

AS

UPDATE SubmittedCharges
   SET SubmittedCharges.DateOfCharge=coalesce(@NewDateOfCharge,DateOfCharge)
      ,SubmittedCharges.TimeOfCharge=coalesce(@NewTimeOfCharge,TimeOfCharge)
      ,SubmittedCharges.ChargeCode=coalesce(@NewCPTcode,ChargeCode)
 WHERE SubmittedCharges.URN = @PatientURN 
       AND SubmittedCharges.DateOfCharge = @CurrentDateOfCharge
       AND SubmittedCharges.TimeOfCharge = @CurrentTimeOfCharge
       AND coalesce(@NewDateOfCharge, @NewTimeOfCharge, @NewCPTcode) is not null

Thank you for such a quick response.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 10:16:25

quote:
Originally posted by Ogriv

Your alternative works great.

I did receive a error and had to change my variable types to varchar but it works fine now. I will definately be researching 'coalsce'.
Thank you for such a quick response.

see

http://msdn.microsoft.com/en-us/library/ms190349.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ogriv
Starting Member

40 Posts

Posted - 2011-11-08 : 10:28:20

I now understand how/why you used COALESCE. That is a cool tool.

Your solution is much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources