Stored Procedure With Optional "Where" Parameters

optional parameters in SQL Server stored proc?

You can declare like this

CREATE PROCEDURE MyProcName
    @Parameter1 INT = 1,
    @Parameter2 VARCHAR (100) = 'StringValue',
    @Parameter3 VARCHAR (100) = NULL
AS

/* check for the NULL / default value (indicating nothing was passed */
if (@Parameter3 IS NULL)
BEGIN
    /* whatever code you desire for a missing parameter*/
    INSERT INTO ........
END

/* and use it in the query as so*/
SELECT *
FROM Table
WHERE Column = @Parameter

How can I use optional parameters in a T-SQL stored procedure?

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

Add OPTION (RECOMPILE) onto your query, see Erland's article, and SQL Server will resolve the OR from within (@LastName IS NULL OR LastName= @LastName) before the query plan is created based on the runtime values of the local variables, and an index can be used.

This will work for any SQL Server version (return proper results), but only include the OPTION(RECOMPILE) if you are on SQL 2008 SP1 CU5 (10.0.2746) and later. The OPTION(RECOMPILE) will recompile your query, only the verison listed will recompile it based on the current run time values of the local variables, which will give you the best performance. If not on that version of SQL Server 2008, just leave that line off.

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))
        OPTION (RECOMPILE) ---<<<    END

Stored Procedure with optional WHERE parameters

One of the easiest ways to accomplish this:

SELECT * FROM table 
WHERE ((@status_id is null) or (status_id = @status_id))
and ((@date is null) or ([date] = @date))
and ((@other_parameter is null) or (other_parameter = @other_parameter))

etc.
This completely eliminates dynamic sql and allows you to search on one or more fields. By eliminating dynamic sql you remove yet another security concern regarding sql injection.

Stored procedure with optional parameter

Make the parameter nullable (optional) and change and d.codigo=@sucursal to deal with the null:

ALTER PROCEDURE Fichada_por_sucursal (
    @fecha1 date,
    @fecha2 date,
    @sucursal int = NULL) 

    AS

    select CASE DATENAME(dw, a.fecha )
    WHEN 'Monday' then 'Lunes'
    WHEN 'Tuesday ' then 'Martes'
    WHEN 'Wednesday ' then 'Miércoles'
    WHEN 'Thursday' then 'Jueves'
    WHEN 'Friday' then 'Viernes'
    WHEN 'Saturday' then 'Sábado'
    ELSE 'Domingo' END
    as Fechadia,
    a.legajo,c.nombres,
    e.abrv,CONVERT (char(10), a.fecha, 103) as Fecha,
    f.entrada as Hora_IN,
    a.hora as ENTRADA,
    f.salida as Hora_out,
    b.hora as SALIDA,
    DATEDIFF(HOUR,a.hora,b.hora) as Horas_trabajadas,
    c.hor_x_jor Horas_jornada,
     DATEDIFF(HOUR,a.hora,b.hora) -hor_x_jor as Diferencia

from fichadas_in a, fichadas_out b, empleados c,
    sucursales d,Clasificacion e, grupo_horario f
    where a.Legajo=b.Legajo
    and a.fecha=b.fecha
    and a.fecha between @fecha1 and @fecha2
    and d.codigo=ISNULL(@sucursal, d.codigo)
    and a.legajo=c.legajo
    and c.CCO=d.Codigo
    and e.Codigo=c.Clasif
    and c.grupo_h=f.codigo
    Order by a.fecha,legajo

Call it:

EXEC Fichada_por_sucursal '2018-05-01','2018-05-05','9'
OR
EXEC Fichada_por_sucursal '2018-05-01','2018-05-05'

Stored procedure w/ multiple optional parameters

Having a single stored procedure that can do completely different things is a very bad design. It is like creating a method in dotnet that does something completely different depending on the parameters being passed. Just like your programming code you want your procedure to do one thing and do it really well.

This boils down to performance. An execution plan will be created for a stored procedure and stored in the cache. When you have multiple execution paths like this it totally destroys the engines ability to consistent generate an efficient execution plan. So even though the plan is great for one set of parameters it will be horrible for another. But it will use the plan that is already in cache if it exists. If anything you can create a procedure as a "driver" and have it call various other procedures based on the parameters. This allows a good plan for each path.

Optional parameters in stored procedure in SQL Server

First of all you should provide default values of parameters and then compare those to column values or to null:

create procedure somename
@p1 int,
@p2 int = null,
@p3 int = null
as
begin

    select * from sometable
    where col1 = @p1 and
          (col2 = @p2 or @p2 is null) and
          (col3 = @p3 or @p3 is null)

end

Then you can call the proc like:

exec somename @p1 = 1
exec somename @p1 = 1, @p2 = 2
exec somename @p1 = 1, @p2 = 2, @p3 = 3

And it will work as you expect. With your approach you will actually need 2^N IF checks where N is number of such parameters. Imagine that for 5 parameters you will need to do 2^5 = 32 IF checks. With provided approach you will have all in one query and only 5 checks.

optional parameters in stored procedure

If the parameters are optional in the stored procedure then you can choose whether to add them. For example, if you remove this line (above)

cmd1.Parameters.AddWithValue("@AccountName", AccountName);

then the stored procedure will use the default value of @AccountName.

That means you just need a way to determine whether or not to pass each parameter. How you do that is tougher to answer because it depends on the behavior of your application.

You could, for example, do this:

if(AccountName != null)
    cmd1.Parameters.AddWithValue("@AccountName", AccountName);

In other words, if a value wasn't provided for AccountName then don't send one to the procedure. That works as long as you don't need to be able to set AccountName to null using this method.

You could pass a parameter to the method indicating what the account type is, and use that to determine which parameters to add and which to leave out.

I noticed that you have separate variables for each value (Account, Address, etc.) It might be easier to work with if you put them into a class, like

public class ClinicUpdate
{
    public string AccountId {get;set;}
    public string Address {get;set;}
    //etc
}

That can make it a lot easier if you decide to rearrange your code. For example, you might decide to make two methods - one that adds the parameters for one type of account, and another to add parameters for the other type. That's a lot easier if you're passing around a single object instead of a big list of parameters.

It's good practice anyway. Opinions vary but I recommend a maximum of four parameters per method call. If you find yourself passing a similar list of parameters to many methods then you probably need a class for them.

Ways to create a procedure with optional parameters?

You can do this by DEFAULT operator or '='

PROCEDURE ADD(NUMBER n1, NUMBER n2, NUMBER n3 DEFAULT 0)....

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CHDFADII

If you want to use it without explicit parameter naming in calls then you have to place your optional parameters at the end of the procedure signature list. With explicit parameter naming in call they can be placed anywhere

Stored procedure with optional parameter that doesn't show in SQL Server Management Studio execute stored procedure or script stored procedure

You could wrap your stored procedure with another:

CREATE PROCEDURE dbo.my_orig_proc
    @id INT
   ,@some_param_default INT = 10

AS
BEGIN
...
END

Wrapper:

CREATE PROCEDURE dbo.my_wrapper_proc
   @id INT
 AS
 BEGIN
     EXEC dbo.my_orig_proc @id;
 END

I would also restrict access to orignal procedures if necessary.

---

Another way is to add check and don't allow specific user to override value:

CREATE PROCEDRUE dbo.my_orig_proc
   @id INT,
   ,@some_param_default INT = 10
AS
BEGIN
   IF USER_NAME() = 'user_name' AND @some_param_default <> 10
      RAISERROR('You cannot change @some_param_default value' ,16,1);
END

Drawback: You need to change parameter value in two places and if user has impersonate privilige he still can execute it.

---

Related Topics