stored procedure with optional parameters

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.
skysoftAsked:
Who is Participating?
 
Victor SpiridonovConnect With a Mentor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.