Sql/sqlserver/sp/stored procedure: Ignore optional parameters in update + CRUD functionality in SP

You have a Stored Procedure which can do updates.

One or more of the parameters are optional.

How do you express this?

 

 

The first solution that springs to mind is IFs.

If param1 is null

      Update without param1

else

      Update with param1

 

 

Another more elegant solution could be this:

      Update SomeDB

      SET

      SomeColumn=@SomeParam,

      Antall = ISNULL(@Param1,Antall) --If @Param1 is null, use the exisiting column value, i.e no update

 

Of course if you want to set the column to NULL this solution can't be used.

 

 

Below full stored procedure with full CRUD functionality and optional parameters:

 

 

 

USE [SOMEDB]

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE PROCEDURE [dbo].[HendelserVarslet_siud]

      @FraDato DateTime = null,

      @Tildato DateTime =  null,

      @HendelseTypeID smallint=NULL,

      @HendelseInformasjonID int=NULL,

      @RaadgiverNr nvarchar(50)=NULL,   

      @Antall int=NULL,

      @Funksjon Char(1)='L'

AS

BEGIN

 

SET NOCOUNT ON;

 

      IF(@Funksjon='L' OR @Funksjon='S')

            -- List records

            BEGIN

                  -- set default dates

                  IF @FraDato IS NULL

                        SET @FraDato = dbo.StartOfday(GETDATE())

                  IF @Tildato IS NULL

                        SET @Tildato= dbo.EndOfday(@FraDato)    

           

                  -- Get records

                  SELECT

                        V.HendelseInformasjonID, I.HendelseTypeID,

                        T.HendelseType, V.RaadgiverNr, V.Antall,

                        V.LoggetDato,  I.Beskrivelse, I.RegistrertDato

                  FROM   HendelseVarslet AS V

                        INNER JOIN

                             HendelseInformasjon AS I

                             ON V.HendelseInformasjonID=I.HendelseInformasjonID

                        INNER JOIN

                             HendelseType T

                             ON T.HendelseTypeId = I.HendelseTypeId

             

                  WHERE

                        V.LoggetDato>=@FraDato

                        AND

                        V.LoggetDato<=@TilDato

                        AND        

                        (I.HendelseTypeID=@HendelseTypeID OR @HendelseTypeID IS NULL)

                        AND

                        (V.HendelseInformasjonID=@HendelseInformasjonID OR @HendelseInformasjonID IS NULL)

                        AND

                        (V.RaadgiverNr=@RaadgiverNr OR @RaadgiverNr IS NULL)

                       

                       

                  RETURN  @@ERROR        

           

            END

      ELSE

      IF(@Funksjon='I')

      -- Insert record

            BEGIN

                  IF    (@RaadgiverNr IS NOT NULL AND @HendelseInformasjonID IS NOT NULL AND @Antall IS NOT NULL)

                        AND

                        NOT EXISTS ( SELECT HendelseinformasjonId, RaadgiverNr FROM [PFA].[dbo].HendelseVarslet

                                   WHERE

                                         RaadgiverNr=@RaadgiverNr AND HendelseInformasjonID=@HendelseInformasjonID)

                       

                        AND

                        EXISTS ( SELECT HendelseinformasjonId, RaadgiverTil FROM [PFA].[dbo].Hendelse

                                   Where (RaadgiverTil=@RaadgiverNr OR Eier=@RaadgiverNr)

                                         AND HendelseInformasjonID=@HendelseInformasjonID )

                            

                        BEGIN

                             INSERT INTO [PFA].[dbo].[HendelseVarslet]

                             (

                                   HendelseInformasjonID,

                                   RaadgiverNr,

                                   Antall,

                                   LoggetDato

                             )

                             VALUES

                             (

                                   @HendelseInformasjonID,

                                   @RaadgiverNr,

                                   @Antall,

                                   getdate()

                             )    

                             RETURN  @@ERROR  

                        END

                  ELSE

                        BEGIN

                             RAISERROR(' Invalid parameters or these values already exists in db',11,1)

                             RETURN @@ERROR

                        END

            END

            ELSE

      IF(@Funksjon='U')

      -- update record

            BEGIN

                  IF    (@RaadgiverNr IS NOT NULL AND @HendelseInformasjonID IS NOT NULL)

                        AND

                        (EXISTS ( SELECT HendelseinformasjonId, RaadgiverNr FROM [PFA].[dbo].HendelseVarslet

                                   WHERE

                                         RaadgiverNr=@RaadgiverNr AND HendelseInformasjonID=@HendelseInformasjonID)

                        )

                        BEGIN

                             UPDATE [PFA].[dbo].[HendelseVarslet]

                             SET

                                   HendelseInformasjonID=@HendelseInformasjonID,

                                   RaadgiverNr=@RaadgiverNr,

                                   Antall = ISNULL(@Antall,Antall) --If @Antall IS NULL, don't update Antall column

                             RETURN  @@ERROR

                        END

                            

                  ELSE

                        BEGIN

                             RAISERROR(Invalid parameters or this combination of HendelseInformasjonId and rådgiver is non-existant ',11,1)

                             RETURN @@ERROR

                        END        

            END

           

           

      ELSE

      IF(@Funksjon='D')

      -- Delete record

            BEGIN

                  IF    EXISTS ( SELECT HendelseinformasjonId, RaadgiverNr FROM [PFA].[dbo].HendelseVarslet

                                   WHERE

                                         (RaadgiverNr=@RaadgiverNr AND HendelseInformasjonID=@HendelseInformasjonID)

                        )

                        AND @RaadgiverNr IS NOT NULL AND @HendelseInformasjonID IS NOT NULL

                       

                        BEGIN

                             DELETE FROM [PFA].[dbo].[HendelseVarslet]

                             WHERE

                                   HendelseInformasjonID=@HendelseInformasjonID

                                   AND

                                   RaadgiverNr=@RaadgiverNr

 

                             RETURN  @@ERROR

                        END

                            

                  ELSE

                        BEGIN

                             RAISERROR('Invalid parameters or this combination of HendelseInformasjonId and rådgiver is non-existant,11,1)

                             RETURN @@ERROR

                        END        

            END

      ELSE

      -- Unknown function

            BEGIN

                  RAISERROR('Function unknown,11,1)

                  RETURN @@ERROR

            END

 

     

     

END

 

 

GO