SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 User Input Parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/19/2012 :  08:05:52  Show Profile  Reply with Quote
I'm getting input from a user that drives this WHERE clause:
WHERE RELEASE_CYCLES.RCYC_NAME LIKE @Release_Cycle_Name@

This works fine in that if they enter a specific Release Cycle Name it's found (even if there are more than one) and the user can user the percent (%) as a wild card to get everything or some subset. All's well.

However, there are cases where there are rows that have an empty Release Cycle Name and there's the issue. Using LIKE and the % sign only gets rows where there's something in the Release Cycle Name.

This:
WHERE RELEASE_CYCLES.RCYC_NAME IS NULL

... Gets the rows where Release Cycle Name is blank.

Is there a WHERE clause I can build that will use LIKE when user has provided a value but use IS NULL when user has not provided a value?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  08:22:36  Show Profile  Reply with Quote
Use 1 below. If you want to return rows that have NULL RCYC_NAME with all queries, use 2 below

-- 1
...
WHERE
	RELEASE_CYCLES.RCYC_NAME LIKE @Release_Cycle_Name@
	OR
	(
		@Release_Cycle_Name@ IS NULL
		AND
		RELEASE_CYCLES.RCYC_NAME IS NULL
	);

-- 2
...	
WHERE
	RELEASE_CYCLES.RCYC_NAME LIKE @Release_Cycle_Name@
	OR RELEASE_CYCLES.RCYC_NAME IS NULL

Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/19/2012 :  09:32:01  Show Profile  Reply with Quote
Yes, I understand that. But what I need is to return using the WHERE clause with LIKE if the user provides a parameter value BUT I want to use the WHERE clause with IS NULL is the user does not provide a parameter.

Basically, I need a conditional WHERE clause ... I want to use your 1 when the user provides a non-null parameter and your 2 when the user provides no parameter value (a NULL).
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/19/2012 :  09:32:51  Show Profile  Reply with Quote
Yes, I understand that. But what I need is to return using the WHERE clause with LIKE if the user provides a parameter value BUT I want to use the WHERE clause with IS NULL is the user does not provide a parameter.

Basically, I need a conditional WHERE clause ... I want to use your 1 when the user provides a non-null parameter and your 2 when the user provides no parameter value (a NULL).
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/19/2012 :  09:33:16  Show Profile  Reply with Quote
Yes, I understand that. But what I need is to return using the WHERE clause with LIKE if the user provides a parameter value BUT I want to use the WHERE clause with IS NULL is the user does not provide a parameter.

Basically, I need a conditional WHERE clause ... I want to use your 1 when the user provides a non-null parameter and your 2 when the user provides no parameter value (a NULL).
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  09:41:41  Show Profile  Reply with Quote
I am afraid I have not understood your requirements.

#1 will give you the matches if the user provides a non-null value for the parameter. If the parameter is null, it returns all the rows where RCYC_NAME is null.

#2 will give you the matches AND all rows where RCYC_NAME is null.

Or perhaps you want #3 - see below. If the user provides a non-null value for the paramter, it returns all the matching rows. If the parameter is null, then it returns all the rows (without any filtering at all).
-- 3
...
WHERE
	RELEASE_CYCLES.RCYC_NAME LIKE @Release_Cycle_Name@
	OR
	@Release_Cycle_Name@ IS NULL
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/19/2012 :  09:57:15  Show Profile  Reply with Quote
OK. I'll try to explain again ...

If I use your #1, if the user supplies no parameter value, an error:

"Could not convert variant of type (Null) into type (String)

If I use your #2, if the user supplies no parameter value, only rows with no Release Cycle Name are returned.

I need to accommodate the scenario where the user wants all rows whether or not Release Cycle Name has a value or is NULL.

In other words, I need a conditional WHERE clause:

1) If the user supplies a parameter value, use your #1
2) If the user supplies no parameter value, I want essentially no WHERE clause at all (i.e. every row is returned whether or not Release Cycle has a value).


Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/19/2012 :  10:01:44  Show Profile  Reply with Quote
OK. I didn't see your #3 in your last post at first and yes, that looks like what I want.

Unfortunately, if the user provides no parameter value, I still get:

"Could not convert variant of type (Null) into type (String)"


.. it still complains about using the LIKE in the WHERE clause:

WHERE
	RELEASE_CYCLES.RCYC_NAME LIKE @Release_Cycle_Name@
	OR
	@Release_Cycle_Name@ IS NULL




Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  10:10:43  Show Profile  Reply with Quote
That error message does not sound familiar. Are you using Microsoft SQL Server? Also, what is the data type of the paramter is it SQL_VARIANT? Didn't think you could use SQL_VARIANT in a like clause.
Go to Top of Page

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/19/2012 :  10:36:54  Show Profile  Reply with Quote
Yes, SQL Server.

Found a solution:
WHERE ISNULL(CAST(RELEASE_CYCLES.RCYC_NAME as nvarchar(100)),'') LIKE '@Release_Cycle_Name@'

Procedurally, the user has to know that *some* value has to be provided. If the user wants a specific Release Cycle Name, he/she provides it. If the user wants any Release Cycle Name that contains, for example, the word "Integration", he/she would provide %Integration%. If the user wants all rows regardless of what the Release Cycle Name is, even if its empty, he/she provide % (the percent sign only).

This seems to work for me.

Thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000