Dynamic parameter stored procedure
If we have more parameters in a stored procedure and every parameter is optional to send the value to the procedure then based on the parameter values sent, the result set of records should be retrieved from the table. This is something like dynamic/optional parameter stored procedure.
Example:
create procedure sp_procedurename
(
@parameter1 varchar(50)=null,
@parameter2 varchar(50)=null,
@parameter3 varchar(50)=null,
@parameter4 int=null
)
as
begin
select * from dbo.databasename.table1 where
(column1=@parameter1 or @parameter1=null),
(column2=@parameter2 or @parameter2=null),
(column3=@parameter3 or @parameter3=null),
(column4=@parameter4 or @parameter4=null)
end
Leave a Comment