Parameterised stored proc

I'm writing a parameterized stored proc. I know that you can se开发者_StackOverflow社区t the parameter value such that it displays all the results when the parameter is not mentioned in the execute command.. But i'm unable to recall how that is achieved. Any help is highly appreciated... Please..

---

I'd recommend parameterized dynamic sql (sp_executesql)

Going this route, you can discard any irrelevant parameter when building your where clause.

Example procedure:

create proc dbo.SearchForStuff
(
 @Id int = 0
,@Description varchar(100) = ''
)
as
begin
    set nocount on;

    declare @select nvarchar(max) = '
    select
    s.*
    from Stuff as s'

    declare @where varchar(max) = ''

    if isnull(@ID,0) != 0 begin                 
        set @where += case @where when '' then ' where ' else ' and ' end + 's.Id = @Id'        
    end

    if isnull(@Description,'') != '' begin      
        set @where +=  case @where when '' then ' where ' else ' and ' end + 's.[Description] = @Description'       
    end         

    set @select += @where

    exec sp_executesql
     @select
    ,N'
    ,@Id int = 0
    ,@Description varchar(100) = '''''
    ,@Id
    ,@Description      

end

Usage:

exec SearchForStuff @Id = 1, @Description = 'omg' -- Returns every item where Id is 1 and Description is 'omg'
exec SearchForStuff @Id = 1  -- Returns every item where Id is 1
exec SearchForStuff @Description = 'omg' -- Returns every item where Description is 'omg'
exec SearchForStuff --returns every item

In this fashion your final query is not littered with useless conditions. Further, you can get a bit more granular than I did here. Based upon which parameters were passed, you can tailor your where/join clauses to take advantage of your indexes such that you get the best possible performance. The only drawback is a slight loss of readability (imo).

---

You can make your WHERE conditions like this:

WHERE (@myParam IS NULL OR @myParam = someValue)

You may be able to use OPTION (RECOMPILE) is SQL2008SP1+ (or similar, don't know other options) in the sproc, depending on your RDBMS, to get this to be performant.

Method from Erland Sommarskog:
http://www.sommarskog.se/dyn-search-2008.html#static

From the link: "The effect of all the @x IS NULL clauses is that if that input parameter is NULL, then that AND-condition is always true. Thus, the only conditions that are in effect are those where the search parameter has a non-NULL value.

As far as maintainability goes, it's difficult to think of a better solution for the search conditions at hand. It's compact, easy to read and to extend. And performance? Very good as long as you include the query hint OPTION (RECOMPILE). This hint forces the query to be recompiled each time, in which case SQL Server will use the actual variable values as if they were constants."

---

If it is an int you can use

SELECT X,Y
FROM T
WHERE C BETWEEN COALESCE(@P, -2147483648) AND COALESCE(@P, 2147483647)

The definitive article on the subject