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

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

sql optional parameter

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

sql optional parameter

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us