SQL Server – How to Validate Multiple Parameters in a Stored Procedure

sql serversql-server-2008sql-server-2008-r2stored-procedures

On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values.

I need to evaluate if one of these parameters contain a null value before performing the insert or update into table.

Example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8),
  @parameter2 char (20),
  @parameter3 char (20),
  @parameter4 char (20),
  @parameter5 char (20),
  @parameter6 char (20),
  @parameter7 char (20),
  @parameter8 char (40),
  @parameter9 char (15),
  @parameter10 char (15) ,
  @parameter11 char (15)...

I have a possible solution for this, which is performing evaluation parameter by parameter and validate if is null add some value, but I'm looking for a better solution for this.

Best Answer

There are a few options that you could use for this. The first would be to just set default values within the procedure declaration to guarantee that there would never be NULL values passed in for these parameters:

CREATE PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8) = 'test',
  @parameter2 char (20) = 'test2'....

The second alternative to checking them one at a time is to just have a single statement that will set them based on a NULL value. Something along the lines of 

  ALTER PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8),
  @parameter2 char (20),
  @parameter3 char (20),
  @parameter4 char (20),
  @parameter5 char (20),
  @parameter6 char (20),...

  Select @parameter1 = CASE when @parameter1 is null then 'test' else @parameter1 end
  ,@parameter2 = Case when @parameter2 is null then 'test2' else @parameter2 end...

The other alternative is that if you just want to see if any of the values is NULL before performing the action, you could pretty easily do a single if statement that checks for that sort of thing

alter PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8) = NULL,
  @parameter2 char (20) = NULL....

  as 
  if @parameter1 is null or @parameter2 is null.....
  begin
        print 'You passed in a NULL value for one of your parameters'
  end
  Else
  begin
        --your statement here
  end

Depending on the value of the CONCAT_NULL_YIELDS_NULL setting, you could also use:

IF (@Parameter1 + @Parameter2 + @Paramater3 ...) IS NULL
BEGIN
    RAISERROR (N'One or more parameters is NULL', 1, 1);
END
ELSE
    -- other statements that will run if all @parameters are NOT null.
BEGIN

I am sure there are other alternatives as well, but I would think one of these would get you where you need to go.

Related Question