June 25, 2010 at 3:32 pm
Hi all,
What is the best way to amend the WHERE clause based on a passed parameter in a stored procedure?
I've tried to show a simple example to explain what I'm after, and hopefully these three SQL statements might help make it clear. In the following example, I'd like a parameter to be passed to the stored procedure, where if its value is 1 it returns 'All', if 2 it returns those where the country is 'USA', and if 3 it returns where the country is 'UK'.
Parameter = 1
SELECTS.SALESPERSON,
S.COUNTRY,
S.SALES
FROMMONTHLY_SALES AS S
WHERES.SALES > 100
Parameter = 2
SELECTS.SALESPERSON,
S.COUNTRY,
S.SALES
FROMMONTHLY_SALES AS S
WHERES.SALES > 100
AND
S.COUNTRY = 'USA'
Parameter = 3
SELECTS.SALESPERSON,
S.COUNTRY,
S.SALES
FROMMONTHLY_SALES AS S
WHERES.SALES > 100
AND
S.COUNTRY = 'UK'
I've a serious personal 'thing' about maintainability (I come from a VB procedural background), so I can see that a change to main body of the SQL (that which is before the 'AND...' in the WHERE clause of the last two examples) would have to be applied in three places. That gets me a bit annoyed - and if I'm honest a bit embarrassed about the work I'd potentially produce - as because I'm only human I run the real risk of applying any changes in two places but not the third. Also, anyone supporting my work would have to deal with the same issues, and with my name in the procedure's comment you can understand why it feels important to me on a personal level for it to be 'correct'. 🙂
The SQL I'm dealing with is a lot more complex than the examples I've given (and isn't related to sales), but I thought they would help make my question more understandable. I considered 'Table-Valued' functions and applying the criteria after they were called, but I was concerned that that would be trading off a possible gain in maintainability against an increase in complexity and a loss of performance as the parameter would also have to be passed to the function.
So, what I ideally want is to be able to just maintain the bulk of the SQL in one place, and to amend the WHERE clause dynamically based on a passed parameter.
I'm sure it's a simple principle that I've not yet learnt (and I'm not ashamed to admit I'm still learning) but I think it's an important one I definitely need to know. Just think... one day, one of you could find yourself potentially having to maintain my stored procedures, and if that day comes, you're going to be seriously grateful for any advice you gave me... 😀
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
June 25, 2010 at 3:52 pm
Based on the information you've provided, I would suggest splitting the statement into two parts. The one part for cases when you want to return all countries and the other for limiting the countries you want to return. The reason I would do this is so that SQL Server can cache a separate execution plan for the two conditions. For instance:
declare @n_parameterIn int --this would be a parameter in your stored proc
declare @vc_country char(5) --this would be declared within the body of your proc
select @vc_country = case @n_parameterIn --this logic could be used to look up these values
when 2 then 'USA' --from a table rather than hard-coding in the proc,
when 3 then 'UK' end --which would make it easier to add countries later
--(that would be done better via a join than this)
if @n_parameterIn = 1--here we are looking for data regardless of country code
begin
SELECT S.SALESPERSON,
S.COUNTRY,
S.SALES
FROM MONTHLY_SALES AS S
WHERE S.SALES > 100
end
else--here we are looking for a subset of the data based on the country code
begin
SELECT S.SALESPERSON,
S.COUNTRY,
S.SALES
FROM MONTHLY_SALES AS S
WHERE S.SALES > 100
AND S.COUNTY = @vc_country
end
Now, you can get the same data returned in this fashion:
SELECT S.SALESPERSON,
S.COUNTRY,
S.SALES
FROM MONTHLY_SALES AS S
WHERE S.SALES > 100
AND ((@n_parameterIn <> 1 AND S.COUNTY = @vc_country)
OR @n_parameterIn = 1)
However, if you do this, SQL Server will cache one plan and it will use that same plan regardless of whether or not you're only looking for a particular country.
*edit formatting
June 25, 2010 at 5:34 pm
Hi bteraberry,
Firstly, thanks so much for the prompt reply. 🙂
Secondly, I love the fact that you expanded on the example I gave. with a lookup table to handle the possibilty of options being added in the future. Too many people (especially in the reality of the workplace) focus on the immediate problem, but it's rarer that someone looks at a problem and says "but what about next week/month/year?", so all kudos to you.
Unfortunately, in my real-world case there will only ever be three possible values for the parameter: (in the example 'All', 'US'or 'UK'), so the lookup table would be pretty much redundant (think 'Null', 'True', 'False'), but your argument for it is correct based on my example.
What you've posted helps me to rationalise my code in a big way and that's the kind of advice I wanted, so thanks! I can at least halve my code from your advice in less than an hour's work. In the long-term that time counts in a big way, especially if someone has to support it.
Oh, and I've learnt a thing or two, which I think is one of the main puposes of this site... 🙂
Cheers (and thanks for the advice!),
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
June 26, 2010 at 3:10 am
Please have a look this before you go ahead.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2010 at 9:14 am
Hope this helps...
declare @stmt nvarchar(max)
declare @whrcls nvarchar (max)
declare @param int
set @param=1
set @whrcls=''
set @stmt = 'SELECT SALESPERSON,
COUNTRY,
SALES
FROM MONTHLY_SALES WHERE SALES > 100'
if (@param=2)
set @whrcls=@whrcls + 'AND COUNTRY ='+ cast ('USA' as varchar) + ''
if (@param=3)
set @whrcls=@whrcls + 'AND COUNTRY ='+ cast ('UK' as varchar) + ''
set @stmt=@stmt+@whrcls
exec (@stmt)
Cheers
June 26, 2010 at 10:03 am
Might I suggest:
declare @Country varchar(50)
set @Country = CASE @Parameter WHEN 1 THEN '%'
WHEN 2 THEN 'USA'
WHEN 3 THEN 'UK'
END
SELECT S.SALESPERSON,
S.COUNTRY,
S.SALES
FROM MONTHLY_SALES AS S
WHERE S.SALES > 100
AND S.COUNTRY LIKE @Country
When you pass in 1, then @Country is set to '%'.
When you pass in 2, then it's set to USA.
When you pass in 3, then it's set to UK.
The query uses a LIKE operator on the country column, so:
if 1 was passed in, the country is compared to a %, and everything passes.
if 2 or 3 is passed in, the country is compared to either USA or UK. Without any wildcards in the search, it's performing an equals. Uses an index very nicely.
I'm surprised that Gail didn't refer you to this other blog as well: Catch-All-Queries[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 26, 2010 at 11:25 am
And a way that would get rid of parameter sniffing would be:
CREATE PROC MainAll
AS
SELECT S.SALESPERSON,
S.COUNTRY,
S.SALES
FROM MONTHLY_SALES AS S
WHERE S.SALES > 100
GO
CREATE PROC MainCountry (@Country varchar(3))
AS
SELECT S.SALESPERSON,
S.COUNTRY,
S.SALES
FROM MONTHLY_SALES AS S
WHERE S.SALES > 100
AND S.COUNTRY = @Country
GO
CREATE PROC Main (@Parameter1 tinyint)
AS
if @Parameter = 1 execute MainAll
else if @Parameter = 2 execute MainCountry 'USA'
else if @Parameter = 3 execute MainCounry 'UK'
GO
Edit: added missing quote
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 26, 2010 at 12:00 pm
ankur_dba (6/26/2010)
Hope this helps...declare @stmt nvarchar(max)
declare @whrcls nvarchar (max)
declare @param int
set @param=1
set @whrcls=''
set @stmt = 'SELECT SALESPERSON,
COUNTRY,
SALES
FROM MONTHLY_SALES WHERE SALES > 100'
if (@param=2)
set @whrcls=@whrcls + 'AND COUNTRY ='+ cast ('USA' as varchar) + ''
if (@param=3)
set @whrcls=@whrcls + 'AND COUNTRY ='+ cast ('UK' as varchar) + ''
set @stmt=@stmt+@whrcls
exec (@stmt)
I don't see dynamic SQL as being necessary for this, but if you do need to use dynamic SQL, NEVER use 'exec' ... use 'sp_executesql' instead.
June 28, 2010 at 4:34 am
Hi,
Thanks for all the replies. There's a lot to keep me entertained and I'm enjoying working my way through Gail's blog.
I really like Wayne's wildcard solution for it's absolute pure simplicity. I've honestly never considered intentionally using a single wildcard to return all records (I only tend to use wildcards when filtering data).
Anyway, thanks again all.
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply