conditional where clause
-
I have this problem I cannot solve. In a stored procedure I need to make a select query. A parameter passed to the SP tells to select only the record with that ID. If the parameter is zero then all records are selected.
I tried several things with IF and CASE WHEN, but neither work. Can this be solved?
create procedure stp_test (@only_this_record int)
as
begin
select * from test where (field1=1) and ...etc...
if @only_this_record > 0
and (id=@only_this_record)
end
-
marc.corbeel (10/6/2016)
I have this problem I cannot solve. In a stored procedure I need to make a select query. A parameter passed to the SP tells to select only the record with that ID. If the parameter is zero then all records are selected.I tried several things with IF and CASE WHEN, but neither work. Can this be solved?
create procedure stp_test (@only_this_record int)
as
begin
select * from test where (field1=1) and ...etc...
if @only_this_record > 0
and (id=@only_this_record)
end
Using a conditional IF statement is by far the simplest way of doing this.
😎
USE TEEST;GO
SET NOCOUNT ON;
DECLARE @NUM INT = 0;
IF (@NUM > 0)
BEGIN
SELECT
NM.N
FROM dbo.TBL_NUMBERS NM
WHERE NM.N = @NUM;
END
ELSE
BEGIN
SELECT
NM.N
FROM dbo.TBL_NUMBERS NM;
END
-
Yes but here you include the complete select command in the condition.
Problem is here that my real query is very complex (about 40 lines), and putting it 2 times in the procedure makes maintenance much harder.
Isn't there a way to only put a part of the query in the condition?
-
marc.corbeel (10/6/2016)
Yes but here you include the complete select command in the condition.Problem is here that my real query is very complex (about 40 lines), and putting it 2 times in the procedure makes maintenance much harder.
Isn't there a way to only put a part of the query in the condition?
Yes, there is. But think about this for a moment. An execution plan set will be created and saved for the stored procedure. But which execution plan - the one for a single row, or the one for all of them? What do you think will happen if the saved plan favours a single row and you then execute the procedure with the 'all rows' option? There's another answer to this of course, but it comes at a cost...
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
marc.corbeel (10/6/2016)
Yes but here you include the complete select command in the condition.Problem is here that my real query is very complex (about 40 lines), and putting it 2 times in the procedure makes maintenance much harder.
Isn't there a way to only put a part of the query in the condition?
So what is wrong with creating a view for the main part of the query?
You can then:
SELECT * FROM YourView
SELECT * FROM YourView WHERE ...
etc
-
Eirikur Eiriksson (10/6/2016)
Using a conditional IF statement is by far the simplest way of doing this.And is prone to the performance-related problems that Chris alluded to.
https://www.simple-talk.com/content/article.aspx?article=2280
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The select query I talked about is actually part of an INSERT INTO.... SELECT query.
Does this make a difference?
-
Using a view is indeed my solution for now. Thanks to all repliers!
-
GilaMonster (10/6/2016)
Eirikur Eiriksson (10/6/2016)
Using a conditional IF statement is by far the simplest way of doing this.And is prone to the performance-related problems that Chris alluded to.
https://www.simple-talk.com/content/article.aspx?article=2280
We must be talking cross-purposes here Gail - I was referring to a one-size-fits-all plan, whereas Eirikur's suggestion would exhibit separate plans for the two queries. A little test harness bears this out:
CREATE procedure test01 (@NUM INT) AS
SET NOCOUNT ON;
IF (@NUM > 0) BEGIN
SELECT NM.Number
FROM master.dbo.spt_values NM
WHERE NM.Number = @NUM;
END
ELSE BEGIN
SELECT NM.Number
FROM master.dbo.spt_values NM
END
RETURN 0
GO
EXEC dbo.test01 0 -- Runs an index scan plan
EXEC dbo.test01 120 -- Runs an index seek plan retrieved from cache
EXEC dbo.test01 0 -- Runs an index scan plan retrieved from cache
Whereas this:
ALTER procedure test02 (@NUM INT)
as
SET NOCOUNT ON;
SELECT NM.Number
FROM master.dbo.spt_values NM
WHERE @NUM = 0 OR NM.Number = @NUM;
RETURN 0
EXEC dbo.test02 120
Performs an index scan for a single row, reading 2500 rows in the process.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
ChrisM@Work (10/6/2016)
GilaMonster (10/6/2016)
Eirikur Eiriksson (10/6/2016)
Using a conditional IF statement is by far the simplest way of doing this.And is prone to the performance-related problems that Chris alluded to.
https://www.simple-talk.com/content/article.aspx?article=2280
We must be talking cross-purposes here Gail - I was referring to a one-size-fits-all plan, whereas Eirikur's suggestion would exhibit separate plans for the two queries.
And they both have performance problems, as the article I linked shows. Different problems, but similar cause, that of compiling the query for a different row count that it runs with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (10/6/2016)
ChrisM@Work (10/6/2016)
GilaMonster (10/6/2016)
Eirikur Eiriksson (10/6/2016)
Using a conditional IF statement is by far the simplest way of doing this.And is prone to the performance-related problems that Chris alluded to.
https://www.simple-talk.com/content/article.aspx?article=2280
We must be talking cross-purposes here Gail - I was referring to a one-size-fits-all plan, whereas Eirikur's suggestion would exhibit separate plans for the two queries.
And they both have performance problems, as the article I linked shows. Different problems, but similar cause, that of compiling the query for a different row count that it runs with.
For this simple use case, the two different plans, for the two queries, work perfectly well. Pass 0 and you get a scan, pass > 0 and you get a seek. Sure you could mess it all up by adding something rowcount-sensitive to the query, but the simple SELECT works. That's my point.
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
ChrisM@home (10/6/2016)
Sure you could mess it all up by adding something rowcount-sensitive to the queryLike a key lookup due to a noncovering index on that single table. Yes, the trivial plan case works, but that query form is prone to erratic performance and the optional parameter, depending on the form, is prone to either consistently bad or erratic performance. Imho both should be avoided.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This becomes a little more interesting if you increase the complexity of the query:
CREATE PROCEDURE [dbo].[test04](@NUM INT)
AS
SET NOCOUNT ON;
IF (@NUM > 0)
BEGIN
SELECT p.n --
FROM dbo.Numbers NM
INNER JOIN dbo.Numbers p ON p.n = nm.n
WHERE NM.n = @NUM;
END
ELSE
BEGIN
SELECT p.n
FROM dbo.Numbers NM
INNER JOIN dbo.Numbers p ON p.n = nm.n
END
RETURN 0
The estimated plan looks like this, whatever value you use for the parameter:
And of course, it will work correctly every time, because the scan plan doesn't reference a parameter at all, and if the parameter is used, it always selects a single row using the seek plan.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
marc.corbeel (10/6/2016)
Yes but here you include the complete select command in the condition.Problem is here that my real query is very complex (about 40 lines), and putting it 2 times in the procedure makes maintenance much harder.
Isn't there a way to only put a part of the query in the condition?
If the query is complex and you dont want to maintain it in two code blocks, you could opt for a catch all query.
create procedure stp_test
(
@only_this_record int
)
as
set nocount on;
select@only_this_record=nullif(@only_this_record,0);
selectn
fromdbo.tbl_numbers
where(n = @only_this_record or @only_this_record IS NULL);
option(recompile)
;
----------------------------------------------------
-
All of the Basic terms wrong in your post. Rows are not records; columns are not fields. Instead of using a zero, which is an actual numeric value, you probably want to use a null. This is why we put them in SQL; they are placeholders for unknown values.
Here is a common idiom for using the null, as a generic value:
WHERE foobar_id = COALASE(@in_foobar_id, foobar_id)
You might run into some problems with performance, so check it out.
The reason I ask if you meant make or execute is that they are different. New SQL programmers like to use dynamic SQL. Experienced SQL programmers think that dynamic SQL is like fried babies at a barbecue.:w00t:
There is no way to optimize it, collect statistics on it. Over time, or anything else. But, boy howdy!, Dynamic SQL looks like how a lot of people grew up writing BASIC in the 1970 – 1980 timeframe.
In a well-designed SQL database, we have a little different model of the world. The schema represents a known environment, a system in which data flows in and out. We do not have a lot of surprises; we have a running enterprise, with well-defined rules and constraints. Many of these are actually governed by law!
But perhaps more than that you need to get a book on basic software engineering and look at the concepts of coupling and cohesion. When we write code, in any language, we want each module to do one and only one function with a known set of parameters (think mathematical functions); this is called high cohesion. We then want to be able to use these modules of code anywhere we want, like Lego blocks.
The worst sort of modules are those depend on their environment. This thing might be shoe sizes or gross domestic products of South American countries, depending on what flag parameter was passed to it. They are dependent on their environment. WOW!
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply