• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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