Default parameter values in SQL Server 2008 stored procedure

Question

I want to use a stored procedure that can make some parameters with null value and then test it if the parameter is null or not and do some coding : I just want to know if there is any problem in this stored procedure

This is the stored procedure

   create proc rechercherGIACetAffiche @nomgiac varchar(20),@nom varchar(30) = null,@par varchar(50) = null
           as
            begin
            IF @nom is null and @par is null
                begin
                    select [ID_Dossier] as 'ID_Dossier'
                      ,[ID_Entreprise] as 'ID_Entreprise'
                      ,[Date_Depot] as 'Date_Dépôt'
                      ,[Type_Etude] as 'Type_Etude'
                      ,[Dernier_Type] as 'Dernier_Type'
                      ,[Eligibile] as 'Eligibilité'
                      ,[Fiche_Information] as 'Fiche_Information'
                      ,[Buletin_Adhesion] as 'Bulletin_d’adhésion'
                      ,[Fiche_Renseignment] as 'Fiche_Renseignment'
                      ,[Attestation] as 'Attestation'
                      ,[Date_Debut] as 'Date_Début'
                      ,[Date_Fin] as 'Date_Fin'
                      ,[ID_Cabinet] as 'ID_Cabinet'
                      ,[Montant_Demander] as 'Montant_Demander'
                      ,[Duree] as 'Durée'
                      ,[Porcentage_Taux] as 'Pourcentage,Taux' from Dossier where Nom_Giac = @nomgiac
                      return 
                end
            if  @par is not null and @nom='CNSS' 
              begin
              select d.[ID_Dossier] as 'ID_Dossier'
              ,d.[ID_Entreprise] as 'ID_Entreprise'
              ,[Date_Depot] as 'Date_Dépôt'
              ,[Type_Etude] as 'Type_Etude'
              ,[Dernier_Type] as 'Dernier_Type'
              ,[Eligibile] as 'Eligibilité'
              ,[Fiche_Information] as 'Fiche_Information'
              ,[Buletin_Adhesion] as 'Bulletin_d’adhésion'
              ,[Fiche_Renseignment] as 'Fiche_Renseignment'
              ,[Attestation] as 'Attestation'
              ,[Date_Debut] as 'Date_Début'
              ,[Date_Fin] as 'Date_Fin'
              ,[ID_Cabinet] as 'ID_Cabinet'
              ,[Montant_Demander] as 'Montant_Demander'
              ,[Duree] as 'Durée'
              ,[Porcentage_Taux] as 'Pourcentage,Taux' 
              from dbo.Dossier d inner join entreprise e on d.ID_Entreprise=e.ID_Entreprise
              where CNSS_Entreprise=@par and d.Nom_Giac=@nomgiac 
              return 
              end

            else if @par is not null and @nom='RS'  
              begin
              select [ID_Dossier] as 'ID_Dossier'
              ,[ID_Entreprise] as 'ID_Entreprise'
              ,[Date_Depot] as 'Date_Dépôt'
              ,[Type_Etude] as 'Type_Etude'
              ,[Dernier_Type] as 'Dernier_Type'
              ,[Eligibile] as 'Eligibilité'
              ,[Fiche_Information] as 'Fiche_Information'
              ,[Buletin_Adhesion] as 'Bulletin_d’adhésion'
              ,[Fiche_Renseignment] as 'Fiche_Renseignment'
              ,[Attestation] as 'Attestation'
              ,[Date_Debut] as 'Date_Début'
              ,[Date_Fin] as 'Date_Fin'
              ,[ID_Cabinet] as 'ID_Cabinet'
              ,[Montant_Demander] as 'Montant_Demander'
              ,[Duree] as 'Durée'
              ,[Porcentage_Taux] as 'Pourcentage,Taux'  
              from dbo.Dossier 
              where  Nom_Giac=@nomgiac and ID_Entreprise in( select ID_Entreprise
                                                             from dbo.Entreprise
                                                             where Raison_Social=@par) 
                                                             return                                            
              end
           else if @par is not null and @nom ='Date'
            begin
             declare @v smalldatetime,@b smalldatetime
              set @b=SUBSTRING(@par,1,4)
              set @v=SUBSTRING(@par,5,8)
            select [ID_Dossier] as 'ID_Dossier'
              ,[ID_Entreprise] as 'ID_Entreprise'
              ,[Date_Depot] as 'Date_Dépôt'
              ,[Type_Etude] as 'Type_Etude'
              ,[Dernier_Type] as 'Dernier_Type'
              ,[Eligibile] as 'Eligibilité'
              ,[Fiche_Information] as 'Fiche_Information'
              ,[Buletin_Adhesion] as 'Bulletin_d’adhésion'
              ,[Fiche_Renseignment] as 'Fiche_Renseignment'
              ,[Attestation] as 'Attestation'
              ,[Date_Debut] as 'Date_Début'
              ,[Date_Fin] as 'Date_Fin'
              ,[ID_Cabinet] as 'ID_Cabinet'
              ,[Montant_Demander] as 'Montant_Demander'
              ,[Duree] as 'Durée'
              ,[Porcentage_Taux] as 'Pourcentage,Taux'  
            from Dossier 
            where Date_Depot between @b and @v and Nom_Giac like @nomgiac
           return 
            end 
            end

Answer 1

If you want to Make Parameter Optional So use it Sorry i didn't Edit your code jst bcz of time

CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL
AS
SELECT *
FROM AdventureWorks.Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'
GO

Answer 2

Looks like it might have some problems when @nomgiac is NULL. In that case, nothing will ever satisfy = @nogiac

Because each of your cases ends with a RETURN, there is no need to use the else, and that might help readability (you seemed to use that technique for the first one and then switched to using the else in the later cases).

I don't generally like the technique of reusing a parameter with a type selector - especially since you are using it as a date in one case (so what if the conversion fails). If you are going to have named/optional parameters, just add more parameters and let them be NULL.

And although it's not always the best performing in execution plan, you could probably write this entire thing as a single query (in which case it is then a candidate for an inline table-valued function, which can be great for code re-use as it can itself be used like a view or a table in joins etc.)

I'm not going to re-write your query, but the basic idea behind the technique is something like this:

SELECT *
FROM tbl
WHERE (@param1 IS NULL OR @col1 = @param1)
    AND (@param2 IS NULL OR @col2 = @param2)

The only tricky thing when combining different things that have joins and don't is that you might need to turn an explicit inner join into a left join and then have a where clause which effectively turns it into an inner join for certain parameters and not for others.

Answer 3

The code looks OK, too - I can't run the stored procedure, but from what I see, I don't see any obvious mistakes or issues with it.

Give it a try! If you have trouble, come back with the error message(s) and ask again!