• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

best way to write stored procedure with option parameter for where clause

What's the best way to create a stored procedure in SQL 2005 so it will run whether or not a parameter is passed in to it? See sample code.

Sometimes I want to run the select statement with no where clause. But if I pass in a parameter when I execute the stored procedure, then I want the where clause applied.

I thought of doing
"where col1 like %@param1%", is that a good approach? If I set the default value of the variable to be null or something like that?

Any suggestions?
create procedure MySP
as
(if I pass in a parameter)
select * from tab1 where col1 = @param1
(if I don't pass in a parameter)
select * from tab1

Open in new window

0
GordonPrince
Asked:
GordonPrince
1 Solution
 
SharathData EngineerCommented:
You can create the SP like this.
create procedure MySP @param1 int = null
as
select * from tab1 where col1 = @param1 or (col1 = col1 and @param1 is null)

Open in new window

0
 
sarabhaiCommented:
this is the simple ...

CREATE PROCEDURE  MySP
    @param1 varchar(40)  
AS
    select * from tab1 where col1 = coalesce (  @param1 , col1 )

0
 
GordonPrinceAuthor Commented:
Very elegant. Thanks.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now