How to get all records with a parameter value of zero
-
Is it possible to get all records from a table with
a (not in the table existing) value of zero in the parameter.
E.G.
USE Northwind
SELECT * FROM Catagories WHERE CatagoryID = ?
CatagoryID can be any value from 1 till 8.
When the parameter value filled in for CatagoryID = 0,
I want to see all the Catagories (1..8).
How can I modify the SELECT statement, that it gives all the
records when the user application supplies the value zero?
-
If you are doing it within a stored procedure then it is easy, however if you want to do it as a simple query then try
SELECT *
FROM dbo.Categories
WHERE CategoryId = ISNULL(NULLIF(@parameter,0),CategoryId)
If it was a stored procedure I would do it slightly differently.
CREATE PROC dbo.usp_GetCategory @lCategory Int
AS
/* Header comments to describe purpose of proc */
SET NOCOUNT ON -- Suppress the rows affected message and therefore get performance boost.
IF @lCategory=0
SELECT *
FROM dbo.Categories
ELSE
SELECT *
FROM dbo.Categories
WHERE CategoryId = @lCategory
GO
Always qualify the object names with the owner, usually dbo.
Try to be explicit about the columns you want in your SELECT list. Avoid SELECT * where you can.
-
I ran into a similar problem with a rather lengthy query and it became a real pain. I originally used IF logic but it required that the same lengthy query be repeated twice. Worse than that, it required that I maintain and synchronize changes between two virtually identical pieces of lengthy code.
Like I said, a real pain.The only thing that needs to be different is the WHERE clause and CASE works just fine in the WHERE clause (if you hold your mouth just right)....
- CREATE PROCEDURE MyTest
- @Category INT = 0 --Defaults to 0 to show all if no parameter supplied
- AS
- SELECT *
FROM Categories
WHERE 1 = CASE
--These are always executed in order shown. - --Case ends when first true condition is met.
WHEN @Category = 0 THEN 1
WHEN @Category = CategoryID THEN 1
ELSE 0
END
- GO
For testing, you can use the following...
- --===== Force which database to test with
- USE Northwind
- --===== Create and populate the test variable
- DECLARE @Category INT
SET @Category = 2
--<-- Change this number to test - --===== Do the test
- SELECT *
FROM Categories
WHERE 1 = CASE
WHEN @Category = 0 THEN 1
WHEN @Category = CategoryID THEN 1
ELSE 0
END
--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) -
David, thank you for your solution.
I tried in the Enterprise Manager:
WHERE CategoryId = ISNULL(NULLIF(@parameter,0),CategoryId)
(it is a nice idee to do it this way)
But it gives an error, saying something like:
(translated from a Duth message)
Not possible to get a parameter when the parameter is a function argument.
The SQL is not valid.
Using a stored procedure is not possible in the used database. 🙁
Btw, the SELECT * is only for testing, I never use it in the real world
-
It won't work in Enterprise Manager but it will in SQL Query Analyser.
-
But will it work in the ASP.NET application, written in Delphi?
I'll try 😉
As, eating is the proof of the pudding
-
If it is your application that is submitting the SQL then why not have a simple If statement in your code so that when category is not zero then the WHERE clause is appended to the SQL you are going to submit?
-
That is a good idee.
But ...
I did show you the essentials of my problem in a simple test-program.
The real program uses a rather huge amount of sql, with lots of parameters. And just one of them will have the value zero, to show all the items of that field. Partial using parameters and partial using Delphi code seems not such a good idea.
And using parameters means less checks for hacking (remember it is a internet program).
But, I think your solution will work. I tried it in QA and that went OK.
Thank you.
-
Jeff,
Thank you for your answer.
You described exactly my pain. My SQL is one page long, and my first solution was exactly the same, so I got two pages of SQL. Every modification had to be done twice, etc.
You and David gave me the real solution:
Make the condition true when the value is zero!
I'll also try your solution, next to Davids.
Thanks.
-
You're welcome and thanks for the feed back.
--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)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply