Differential querying based upon a parameter value

Question

Differential querying based upon a parameter value

nadarajan_v

SSC-Addicted

Points: 406
More actions
April 11, 2011 at 5:23 pm

#249279

Hi,
I have a complex stored proc, but only the necessary code is shown below. Depending upon whether a parameter receives a value of 'All' or a specific one, it should pull different set of values. Is there a diiferent way of writing this proc without the big if/else statement? The possible values of the parameter ar 'All', 'StepByStep' and 'Regular'
Create Procedure Get_Published_Articles
{
@ArticleType nvarchar(50)
}
if @ArticleType = 'All'
Begin
select * from Articles
end
else
Begin
select * from Articles where ArticleType = @ArticleType
end
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Without knowing the complete statement the only answer that I can give you is "It depends", not much assistance I know.

I would suggest that you post the table definition(s), some sample data (NOT real data that your company would not like to have exposed), but representative. And what results you need from that sample data.

All told to help those who want to help you, please click on the first link in my signature block which will show you how to post the table definition, provide sample data in an easily consumed format.

Further without knowing how your SP is accessed, from a user GUI with a limited drop down list of permitted values, or having the parameter passed from a web based application. I would suggest including a TRY CATCH block of code to handle possible "incorrect" values.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

You should review the following article by Erland: http://www.sommarskog.se/dyn-search-2005.html

It goes through the various different options of writing dynamic queries for searching. Most likely, the best option for this would be dynamic SQL - which should get you an optimal plan for the various possible parameters.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

nadarajan_v SSC-Addicted Points: 406 More actions · Answer 3

Hi bitbucket,

I get your point. But my question is very simple - just how to rewrite the query. While table defenition may help a little bit, in my humble opinion, it is not needed. The parameter @ArticleType can only have three possible values: All, StepByStep or Regular. When "All" was the parameter value, then all the values from the Articles table should be returned. If the value is either "StepByStep" or "Regular", then only those rows where the AtrticleType column data correspond to either one of those values should be returned.

Thanks

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 4

Try this.

SELECT *

FROM Articles

WHERE (( ArticleType = @ArticleType)

OR (@ArticleType = 'All'))

_____________________________________________
One ounce of practice is more important than tonnes of dreams

nadarajan_v SSC-Addicted Points: 406 More actions · Answer 5

The ArticleType column will contain either "StepByStep" or "Regular" values only. It will not have the value "All"

Thanks

srikant maurya Hall of Fame Points: 3293 More actions · Answer 6

Try this may help you

SELECT *

FROM Articles

WHERE ArticleType=case when @ArticleType ='All THEN ArticleType ELSE @ArticleType END

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 7

The ArticleType column will contain either "StepByStep" or "Regular" values only. It will not have the value "All"
Thanks

Ok. But you are passing 'All' as parameter.. right? Actually the below part of the code is not comparing the Field value in the table. It is comparing the param.

OR (@ArticleType = 'All'))

So, it should work. Just paste the code in the sp and try all the following.

EXECUTE Get_Published_Articles 'StepByStep'

EXECUTE Get_Published_Articles 'Regular'

EXECUTE Get_Published_Articles 'All'

_____________________________________________
One ounce of practice is more important than tonnes of dreams

nadarajan_v SSC-Addicted Points: 406 More actions · Answer 8

nadarajan_v

SSC-Addicted

Points: 406

April 12, 2011 at 5:39 am

#1310295

Yes, It is working. Thank you so much!!

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 9

Srikant's solution also will work for this scenario. He is using "CASE" and in my code it is "OR". You need to check performance of the both solution and have to use the best one.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 10

Please see this link

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Clear Sky SQL
My Blog[/url]

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 11

Hi Dave, The link is exactly what we were looking for. Thanks.

So, Our solutions are TRICKS 😀 your link is REALITY :cool:.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 12

C.K.Shaiju (4/12/2011)
Hi Dave, The link is exactly what we were looking for. Thanks.
So, Our solutions are TRICKS 😀 your link is REALITY :cool:.

Good stuff , but i cant take any credit for the content

Clear Sky SQL
My Blog[/url]

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 13

After reading Gail's article on catch all queries - you should also review Erland's article on dynamic SQL.

Whether or not you decide to go with dynamic SQL, a catch all query or using IF branches is really going to be determined by what is easiest for you to maintain and performs well enough in your environment.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs