User Input Parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

User Input Parameters

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 08:05:52

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 08:22:36

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

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 09:32:01

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).

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 09:32:51

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 09:33:16

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 09:41:41

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

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 09:57:15

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).

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 10:01:44

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 10:10:43

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.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-19 : 10:36:54

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources