I have a stored procedure with a table created by several inners, and what I need is to pass between 1 and 4 parameters to form the where of the query. What I'm not realizing is if I want to pass 2 of the 4 alone, that the others do not ask for it within the procedure ...
Thank you!
What you need is to be able to generate a dynamic query . to your procedure you should always send all the parameters, but have an identifier with which you will validate if a parameter is added to the where:
In the following example, I use the "*" character as an identifier to identify when the where would not be applied:
create procedure SpConsultaDimanica
@parametro varchar(25)
as
begin
declare @sql nvarchar(max),@where nvarchar(max)
set @sql='
select campo1, campo2 from tabla
'
if @parametro<>'*'
begin
set @where='where campo1='+@parametro
end
set @sql=@sql+@where
exec sp_executesql @sql
end
I hope it helps.