CODEWITHSUNDEEP
sql-serverstored-procedures
Sandeep Kumar
Sandeep Kumar

Reputation: 315

Execute Stored Procedure with less Input Parameters

I have a stored procedure which needs 4 parameters to execute. But I want to execute it with passing only 3 parameters. How can I achieve that?

For example: my stored procedure usually executes like this. 

EXEC usp_my_sp @fname = 'Sandeep', 
               @lname = 'Kumar', 
               @age = '26', 
               @city = 'New Delhi'**

It will return all people named Sandeep Kumar, Age = 26 and living in New Delhi.

But I want to get all people named Sandeep Kumar of age 26, from all cities. Here I need to omit the 4th parameter.

How can I achieve that?

Thanks, Sandeep

Upvotes: 2

Views: 1034

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

Create parameters that have null default values, and write your Where clause predicates with coalesce(), (or IsNUll()).

 Create procedure MySP
    @fName varchar(50) = null,
    @lName varchar(50) = null,
    @age byte = null,
    @city varchar(30) = null
 as
        Select -- stuff
        From MyTable
        Where firstName = coalesce(@fName, firstName )
          and lastName = coalesce(@lName, LastName)
          and City = coalesce(@city , City )
          and lastName = coalesce(@lName, LastName)

Upvotes: 3

SqlZim
SqlZim

Reputation: 38023

You are describing a catch-all query with dynamic search conditions.

The basic non-optimized solution is to change the where clause for @city to be something like this (t being the alias for your table):

where (t.city = @city or @city is null)

Some also write this as 

where t.city = isnull(@city,t.city)

Both options will return the correct result set, but for a procedure this can lead to issues of a cached plan being sub-optimal for a given set of parameters. There are a few solutions to improve this situation, the simplest being to add option(recompile) to the end of the query. This will generate a new query plan for each call to the procedure, and thus incur some additional overhead with each execution.

A more complex solution would rewrite the query using dynamic sql, and for that I will direct you to the following resources:

catch-all query reference:

Upvotes: 1

Related Questions