sql optional parameter

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hello

Im trying to write a procedure.

Select *
from table1
inner join table2
on table1.col1= table.col1
where 1=1
and table1.col2= @col2value
and table2.col3 =@col3value

if @col2value is null from the user
i want to drop that table1.col2= @col2value from the where clause?.

how can i accomplish this?.

I tried ((@col2value IS NULL) OR (table1.col2 = @col2value))

this doesnt seem to work.
please help.

Posted 15-Aug-14 22:42pm

Member 10562713

Add a Solution

3 solutions

Solution 1

Try:

SQL

SELECT * FROM Table1 t1
INNER JOIN table2 t2 ON t1.col1= t2.col1
WHERE (@col2value IS NULL OR t1.Col2 = @col2Value)
  AND t2.Col3 = @col3Value

[edit]Removed spurious close bracket - OriginalGriff[/edit]

Posted 15-Aug-14 23:04pm

OriginalGriff

Updated 15-Aug-14 23:52pm

v2

Comments

Member 10562713 16-Aug-14 5:12am

I tried this but the query that gets built doesnt discard t1.col2 . if col2value is null I want the t1.col2 to be discarded from where clause.

OriginalGriff 16-Aug-14 5:19am

Ah! Do you mean you want to not return t1.Col2 (or even the whole of table1) is the parameter is null?

Member 10562713 16-Aug-14 5:25am

if col2value is not null..

where clause will be - where table1.col2 =@col2value and table2.col3=@col3value.
if col2value is null here is what i want-

where table2.col3=@col3value;

OriginalGriff 16-Aug-14 5:51am

And that is exactly what it does: if it's NULL, t1.Col2 isn't looked at.

Are you sure you are passing a NULL value?
How are you testing it?

Solution 2

Hi,

Try this...

SQL

Select * from table1 t1 inner join table2 t2 on t1.col1=t2.col1
where t1.col2 = CASE WHEN ISNULL(@col2Val,'') ='' THEN t1.col2 ELSE @col2Val END
AND t2.col3 = @col3Val

Hope this will help you.

Cheers

Posted 17-Aug-14 20:56pm

Magic Wonder

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)

Gupta Poonam · Accepted Answer · 2014-08-18T01:36:00

There is a work around if solution 1 is not working.. It will add few more lines of code but might solve you issue

SQL

IF(@col2value Is Null)
	BEGIN
		SELECT * FROM Table1 t1
		INNER JOIN table2 t2 ON t1.col1= t2.col1
		WHERE t2.Col3 = @col3Value
	END
	ELSE
	BEGIN
		SELECT * FROM Table1 t1
		INNER JOIN table2 t2 ON t1.col1= t2.col1
		WHERE t1.Col2 = @col2Value
		AND t2.Col3 = @col3Value
	
	END