null value parameter in stored procedure

Question

0.00/5 (No votes)

See more:

Hello everyone,

I have a question about a query in a stored procedure.
When the used parmeter @parm is null in my where clause the syntax should be

SQL

and columnname in (select idfield from otherTable)

if the @parm is filled with a value the syntax should be:

SQL

and columnname = @parm

How to handle this problem in the where clause?

Posted 23-Oct-11 23:16pm

Herman<T>.Instance

Add a Solution

5 solutions

Solution 4

SQL

Declare @Query varchar(1000)

Set @Query = 'Select ColumnName1, columnName2, ..... From TableName1 Where ColumnName1 = ''Test'' and '

IF isnull(@parm, '') = ''
	Set @Query = @Query + 'CoulmnName2 in (Select CoulmnName2 from TableName2)'
ELSE
	Set @Query = @Query + 'CoulmnName2 = ' + @parm 

Execute @Query

Posted 24-Oct-11 1:02am

Muhammad Shahid Farooq

Updated 24-Oct-11 1:14am

v4

Comments

Herman<T>.Instance 24-Oct-11 7:06am

this is not solving in the where clause. is it possible to solve in the where clause?

Solution 1

Hi,

Try this if could help...

IF @parm  is NULL        
  BEGIN        
   and columnname in (select idfield from otherTable) 
   else
     and columnname = @parm
  END

Regards,
Al Moje

Posted 24-Oct-11 0:00am

Al Moje

Comments

Herman<T>.Instance 24-Oct-11 6:03am

thanks for thinking but this is NOT working. It fails on the IF, because in the where you start the lines with where, or, AND

Solution 2

You can do something like the following:

and (isnull(@parm,0) = 0 OR columnname in (select idfield from otherTable))

This will work for both the conditions.

Posted 24-Oct-11 0:04am

Om Prakash Pant

Comments

Herman<T>.Instance 24-Oct-11 6:08am

does not work either. because of the OR any value will return all possible values. In case a value is set, like 1 then only the values with 1 should return

Om Prakash Pant 24-Oct-11 6:50am

in that case you need to add this in where clause. something like:
and (isnull(@parm,0) = 0 OR columnname in (select idfield from otherTable where param_field = @parm))
Otherwise it will return all values.

Solution 3

SQL

IF isnull(@parm, '') = ''
	Select * from TableName Where CoulmnName in (Select CoulmnName from TableName)
ELSE
	Select * from TableName Where CoulmnName = @parm

Posted 24-Oct-11 0:52am

Muhammad Shahid Farooq

Updated 24-Oct-11 0:55am

v2

Comments

Herman<T>.Instance 24-Oct-11 7:05am

badly maintainable when using named columns

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)

Herman<T>.Instance · Accepted Answer · 2011-10-24T01:09:00

Solution 5

Admitting: this was a headache for 3 people but it works...

SQL

AND (@parm is null OR @parm = CASE
     WHEN Isnull(@parm,0) = 0 THEN
          CASE when columnName in (select id from table) then columnName
              ELSE @parm END
     ELSE
         ColumnName END)

Posted 24-Oct-11 1:09am

Herman<T>.Instance

Updated 24-Oct-11 1:12am

v2