September 23, 2021 at 6:17 am
Hi,
I have a where clause in my query that looks like this:
WHERE
items.UserNumber_02 IS NULL -- ALL ITEMS
OR items.UserNumber_02 IS NOT NULL -- Table is filtered
How can I switch between all items and the filtered part with a parameter?
September 23, 2021 at 9:32 am
Do not do this - it will mess up the query plan.
If the query is complicated then put it in a view and select from the view with different WHERE clauses.
September 23, 2021 at 11:57 am
You can't substitute "IS NOT NULL" for a parameter value. Now, if you mean that you want to toss, entirely, IS NOT NULL, then you simply would put:
... OR items.UserNumber_02 = @myusernumber ...
However, that changes the very nature of the query since it will return all the rows that are NULL, plus just the rows that match that value.
Maybe I'm confused as to what this is supposed to do. It's odd to want everything AND specificity at the same time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 23, 2021 at 12:16 pm
I see now I did not make my question as clear as I should.
In the WHERE clause, I want to be able to have the user toggle between
WHERE items.UserNumber_02 IS NULL -- ALL ITEMS
and
WHERE items.UserNumber_02 IS NOT NULL -- Table is filtered
so, I would like to use a parameter.
Perhaps it is actually really straightforward, but I am not so familiar with using parameters.
September 23, 2021 at 1:20 pm
AH!!!
Sorry. I really did misunderstand.
So, Ken is right. Don't do that. That is two, distinct queries. While yes, there are ways to force them into a single procedure or batch, these are poor coding practices. If you really want to do it, I'd suggest the following.
First, create a wrapper procedure. Something like this:
CREATE OR ALTER PROCEDURE dbo.ReturnItems
@ItemID int = NULL
AS
IF @ItemID IS NULL
EXEC dbo.ReturnAllItems;
ELSE
EXEC dbo.ReturnOneItem @ItemID = @ItemID;
Then, create your two distinct stored procedures like this:
CREATE OR ALTER PROCEDURE dbo.ReturnAllItems
AS
SELECT r.RadioName
FROM dbo.Radio AS r;
GO
CREATE OR ALTER PROCEDURE dbo.ReturnOneItem
@ItemID INT
AS
SELECT r.RadioName
FROM dbo.Radio AS r
WHERE r.RadioID = @ItemID;
GO
Then you can execute the wrapper procedure as follows:
exec dbo.ReturnItems;
exec dbo.ReturnItems @ItemID = 30;
That will keep the logic of two completely different queries apart, while allowing your users to pick what they want. You could also toss the default value of NULL and then require them to pass an @ItemID value as either a specific value, or as NULL.
This is the best way to do this kind of work. Each query can be tuned independently. Each will get an appropriate execution plan. Anything else involves dynamic queries, which are best left out of most data processing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 23, 2021 at 1:41 pm
Thanks again Grant,
I will see if it works for me...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply