How to handle One mandatory and One optional parameter in where condition SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,
I am having a problem in writing a select query .

I have written a stored procedure which takes two arguements input1 and input2,
where input2 can be null.In my stored procedure I am writing a select querry (which uses joins )and where condition which should ullfill below requirement.

1.when input1 and input2 are not null ,give the result (column1=input1 and column2=input2) (Result should have both vaues matched)

2.Input1 is matched and input2 is null-Return results for matched for input1

3.Input1 is not matched input2 is not null,Return matched for input2

Please help me.

Posted 12-Dec-12 17:58pm

Prashant Bangaluru

Add a Solution

3 solutions

Solution 4

SQL

declare @val1 varchar(10) ;
declare @val2 varchar(10) ;

set @val1='s';
set @val2='ur';


EASIEST WAY TO GET THE RESULT IS :-

select * from table_Name  where col1=isnull(@val1,col1) and col2=isnull(@val2,col2)

or
use the dynamic SQl code like

declare @val1 varchar(10) ;
declare @val2 varchar(10) ;
declare @dynaimcstring varchar(200);
declare @executestring nvarchar(max);
set @val1 ='s';
set @val2='';

if( ltrim(rtrim(@val1))='' and ltrim(rtrim(@val2))!='')
begin
set @dynaimcstring='where col2='''+@val2+'''';
end
if( ltrim(rtrim(@val1))!='' and ltrim(rtrim(@val2))='')
begin
set @dynaimcstring='where col1='''+@val1+'''';
end
if( ltrim(rtrim(@val1))!='' and ltrim(rtrim(@val2))!='')
begin
set @dynaimcstring=' where col1='''+@val1+''' and col2='''+@val2+'''';
end
if( ltrim(rtrim(@val1))='' and ltrim(rtrim(@val2))='')
begin
set @dynaimcstring='';
end
set @executestring='select * from allot_Det '+ @dynaimcstring;
print @str;
exec sp_executesql @str;

Posted 12-Dec-12 19:17pm

KapilMuni

Comments

Prashant Bangaluru 13-Dec-12 1:46am

Thanks for your solution..But I am using a stored procedure which is having 10 unions so I have to do everything in where condition only

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Krunal Berawala · Accepted Answer · 2012-12-12T18:05:00

Solution 1

try and use the where clause with

isnull(Column1,0) = 0

I have not tried on query, it may work.

Posted 12-Dec-12 18:05pm

Krunal Berawala

Comments

Prashant Bangaluru 13-Dec-12 1:18am

Thanks for your reply,,But its not working for me This is the code snippet I am using .. INNER JOIN @TEMPFLOORS j ON j.BuildingId = g.BuildingId WHERE @index='9' and ((g.propertynumber=@input1 and (j.AllFloors=isnull(@input2,j.AllFloors )))) and (f.scheduleddate>=@From and f.scheduleddate<=@To) Please help me where I am missing

__TR__ · Accepted Answer · 2012-12-12T18:27:00

Solution 2

Try

SQL

SELECT * FROM yourTable
WHERE Column1 = ISNULL(@input1, Column1) AND Column2 = ISNULL(@input2, Column2)

Posted 12-Dec-12 18:27pm

__TR__

Comments

Prashant Bangaluru 13-Dec-12 1:18am

Thanks for your reply,,But its not working for me
This is the code snippet I am using ..
INNER JOIN @TEMPFLOORS j ON j.BuildingId = g.BuildingId
WHERE
@index='9' and ((g.propertynumber=@input1 and (j.AllFloors=isnull(@input2,j.AllFloors ))))
and (f.scheduleddate>=@From and f.scheduleddate<=@To)

Please help me where I am missing

__TR__ 13-Dec-12 1:40am

Looks like you have missed to add ISNULL for @input1.
g.propertynumber=ISNULL(@input1, g.propertynumber)

Prashant Bangaluru 13-Dec-12 1:44am

Ya Thanks a lot..I am getting result but only missing 3rd condition,If the floor name is present but input1 is not matching it should give result for rows for matching floorname

__TR__ 13-Dec-12 2:34am

I don't see a floorname column in your where clause.

Prashant Bangaluru 13-Dec-12 3:46am

Oh I am very very sorry It is actually J.allFloors ..

__TR__ 13-Dec-12 5:29am

I did not understand what exactly is the problem here.

Prashant Bangaluru 13-Dec-12 6:42am

It is working for 2 conditions which I mentioned in my question .1.when input1 and input2 are not null ,give the result (column1=input1 and column2=input2) (Result should have both vaues matched)

2.Input1 is matched and input2 is null-Return results for matched for input1

But the 3rd Condition
3.Input1 is not matched to column1 and input2 is not null,Return matched for column2=input2