Changing Where Clause Based on Parameter

[jackieg]

Hi Folks,

I have a parameter in a stored proc that can be null, a single value or a list of values delimited by commas, here's my problem

If the value is null I want the field to be any value but if it's not I want it to be in the list, how to I put that in the where clause?

The logic is something like

if @var is null then
EnterpriseId > 0
else EnterpriseId in (@var)

[deepuv04]

if @var is null
begin
select col1,col2... from table_name where EnterpriseId > 0
end
else
begin
select col1,col2... from table_name where EnterpriseId in @var
end

[qhjghz]

if @var is null
begin
select col1,col2... from table_name where EnterpriseId > 0
end
else
begin
select col1,col2... from table_name where EnterpriseId in @var
end

This is the best thing to do from performance point of view. But, however, if you can afford for a table scan then you can use this method

select * from
table_name a
where
CASE WHEN (@variable is null) THEN 1 ELSE
CASE WHEN a.column_name = @variable THEN 1 ELSE 0 END
END =1

[sayedul]

Hi Folks,

I have a parameter in a stored proc that can be null, a single value or a list of values delimited by commas, here's my problem

If the value is null I want the field to be any value but if it's not I want it to be in the list, how to I put that in the where clause?

The logic is something like

if @var is null then
EnterpriseId > 0
else EnterpriseId in (@var)

Hi,

If data type of EnterpriseId is varchar/char, try with where condition like below:

where (','+isnull(@var,EnterpriseId)+',') like ('%,'+EnterpriseId+ ',%')

If data type of EnterpriseId is any numeric, try with where condition like below:

where (','+isnull(@var,convert(varchar(20),EnterpriseId))+',') like ('%,'+convert(varchar(20),EnterpriseId)+ ',%')

The above should be a perfect and simple solution of your problem.

Regards,
Sayedul Haque

[qhjghz]

Hi,

If data type of EnterpriseId is varchar/char, try with where condition like below:

where (','+isnull(@var,EnterpriseId)+',') like ('%,'+EnterpriseId+ ',%')

If data type of EnterpriseId is any numeric, try with where condition like below:

where (','+isnull(@var,convert(varchar(20),EnterpriseId))+',') like ('%,'+convert(varchar(20),EnterpriseId)+ ',%')

The above should be a perfect and simple solution of your problem.

Regards,
Sayedul Haque

The performance will be very poor for this sql.

[sayedul]

The performance will be very poor for this sql.

Hi,

To fulfill all the requirements of the problem, I am sure that the other solutions except mine will not work. I think the other members who gave solution did not understand the problem completely or did not test their script to solve all the cases of the problem. The problem expresses that the parameter can be any of three types i.e. null or single value or multiple values separated by comma.

I shall be pleased if any of you can make another alternative perfect and simple solution of the problem and upload it. One thing you should remember that whenever you make a solution, the solution should be complete first then the performance issue will arise.

Thanks.

- Sayedul

[Sami Ward]

Hi,

I have had this same dilema and have used a variable to construct a statement and then execute it - here's an example that works for me. you will need to change the table name obviously. and ofcourse the @Pvar variable can be have multiple values, or a null.

Declare @PVar as Varchar(80)
declare @SqlStr as Varchar(8000)
Set @Pvar = '12141,12142,12 143'
set @SqlStr = 'Select * from tblInvoice '

IF @PVar is not null
Set @SqlStr = @SqlStr + ' Where InvoiceNo in (' + @PVar + ')'

Print @SqlStr

exec (@SqlStr)


I hope that answers the question.

:)

Hi,

To fulfill all the requirements of the problem, I am sure that the other solutions except mine will not work. I think the other members who gave solution did not understand the problem completely or did not test their script to solve all the cases of the problem. The problem expresses that the parameter can be any of three types i.e. null or single value or multiple values separated by comma.

I shall be pleased if any of you can make another alternative perfect and simple solution of the problem and upload it. One thing you should remember that whenever you make a solution, the solution should be complete first then the performance issue will arise.

Thanks.

- Sayedul