Write a conditional query

Question

Post reply

Write a conditional query

shohelr2003

Hall of Fame

Points: 3535
More actions
February 3, 2014 at 5:02 am

#396209

Dear,
I require to write a single query that if I pass a customer id, it will retrieve that customer's information. If I do not pass any customer id, it will retrieve all customers' information.
Please help me to provide any idea.
Regards,
Akbar

Viewing 6 posts - 1 through 5 (of 5 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Please post ddl and dml for the tables involved in your query.

This is quite a trivial request and there are several ways to do it - what have you tried?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lowell SSC Guru Points: 323518 More actions · Answer 2

read up at Gails blog on catch all queries: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

here's an example of a catch all query:

WHERE 1 = CASE

WHEN @CustomerID IS NULL

THEN 1

WHEN CustomerID = @CustomerID

THEN 1

ELSE 0

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That kind of query generally doesn't perform well. To give you an idea, I had a query the other week that was written to do just that and took 3 hours to run. When I removed the portion of the query that handled 'one customer or all customers', the query ran in under 2 seconds.

Much better to have the app decide which query to run or, if that's not possible, for the stored procedure to decide whether to run a procedure to return one customer or a procedure to return all customers.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

To add to that, unless you only have a 100 customers or so, I think it a bit insane to return all customers at any time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 5

Thanks all. Specially to GilaMonster and Lowell for your valuable suggestions.

It helped me.