Solved

stored procedure with optional parameters

Posted on 1998-09-20
1
213 Views
Last Modified: 2010-03-19
i want to build a stored procedure with some parameters, if one ore more of the parameters isn't given then it should not be included in the procedure ( 'and <column name> = 'value' should be ignored ), I know it is possible but i don't know how.
0
Comment
Question by:skysoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 7

Accepted Solution

by:
spiridonov earned 200 total points
ID: 1090204
You do not need to specify parameter if you specify default when you define your stored procedure:
Simple example:
CREATE PROCEDURE test @optional_param varchar(10)="none"
as
IF @optional_param="none"
 SELECT * from t1
ELSE
 SELECT @ from t1
  WHERE col1=@optional_param
IF you have multiple optional parameters you will have to first build your SQL string and then use EXEC to execucte it.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question