July 10, 2013 at 6:05 am
I have the stored procedure with @StartDate and @EndDate parameters
my WHERE statement is using those parameters to filter out data
WHERE condition1 and SomeDate >= @StartDate and SomeDate <= @EndDate and condition2
However when @StartDate or @EndDate is null or empty string then WHERE statement should look like below
WHERE condition 1 and condition2
I was trying already to do it with CASE of IF statement but without success so far.
July 10, 2013 at 6:51 am
Have you tried dynamic SQL?
Something like this:
declare @sql nvarchar(max),
@Startdate nvarchar(10),
@Enddate nvarchar(10);
set @sql = 'select * from #table1';
set @Startdate = '01/01/1900';
set @Enddate = '01/01/1900';
if @Startdate is not null and @Startdate <> ''
and @Enddate is not null and @Enddate <> ''
set @sql = @sql + ' where Startdate >= ''' + @Startdate + ''' and Enddate <= ''' +@Enddate + ''''
print @sql
exec sp_executesql @sql
July 10, 2013 at 7:44 am
To be honest with you I don't like dynamic sql because of quotation marks '' which are making whole code harder to read 🙂
So Instead of this I developed something like this:
where condition1 and (SomeDate >= CASE WHEN @StartDate is null THEN SomeDate ELSE @StartDate END or SomeDate is null)
and (SomeDate <= CASE WHEN @EndDate is null THEN SomeDate ELSE @EndDate END or SomeDate is null) and condition2
July 10, 2013 at 7:49 am
Oh be careful here Laurie. You suggested using dynamic sql which is a good choice here. However you committed a cardinal sin. You allowed for the parameters to be executed. This is now a sql injection vulnerability. You should instead use parameters to your dynamic sql.
Here is a full working example.
create table #table1
(
StartDate datetime,
EndDate datetime
)
insert #table1
select '1/15/1900', '2/1/1900'
declare @sql nvarchar(max),
@Startdate nvarchar(10),
@Enddate nvarchar(10);
set @sql = 'select * from #table1';
set @Startdate = '01/01/1900';
set @Enddate = '01/01/1901';
if @Startdate > '' AND @Enddate > ''
set @sql = @sql + ' where Startdate >= @Startdate and Enddate <= @Enddate'
print @sql
exec sp_executesql @sql, N'@Startdate datetime, @EndDate datetime', @Startdate = @Startdate, @Enddate = @Enddate
drop table #table1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2013 at 7:50 am
phoenix_ (7/10/2013)
To be honest with you I don't like dynamic sql because of quotation marks '' which are making whole code harder to read 🙂So Instead of this I developed something like this:
where condition1 and (SomeDate >= CASE WHEN @StartDate is null THEN SomeDate ELSE @StartDate END or SomeDate is null)and (SomeDate <= CASE WHEN @EndDate is null THEN SomeDate ELSE @EndDate END or SomeDate is null) and condition2
This will work to a point. You will end up with some serious performance issues with this type of query though. Check out this article from Gail on the topic.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2013 at 3:38 pm
I use this syntax in my where clauses rather than case statements:
WHERE
((@StartDate IS NOT NULL AND StartDate >= @StartDate)
OR
(@StartDate IS NULL))
AND ((@EndDate IS NOT NULL AND EndDate <= @EndDate)
OR
(@EndDate IS NULL))
July 11, 2013 at 7:02 am
Steven Willis (7/10/2013)
I use this syntax in my where clauses rather than case statements:
WHERE
((@StartDate IS NOT NULL AND StartDate >= @StartDate)
OR
(@StartDate IS NULL))
AND ((@EndDate IS NOT NULL AND EndDate <= @EndDate)
OR
(@EndDate IS NULL))
You too might want to read that article from Gail. This type of approach can lead to really awful performance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2013 at 12:59 am
Sean Lange (7/10/2013)
phoenix_ (7/10/2013)
To be honest with you I don't like dynamic sql because of quotation marks '' which are making whole code harder to read 🙂So Instead of this I developed something like this:
where condition1 and (SomeDate >= CASE WHEN @StartDate is null THEN SomeDate ELSE @StartDate END or SomeDate is null)and (SomeDate <= CASE WHEN @EndDate is null THEN SomeDate ELSE @EndDate END or SomeDate is null) and condition2
This will work to a point. You will end up with some serious performance issues with this type of query though. Check out this article from Gail on the topic.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
+1 to you Sean for the link to the bible on this topic.
I saw the subject of this thread and visited to make sure someone had already provided it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 13, 2013 at 12:47 am
Thanks Sean for that link of Gail 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 14, 2021 at 12:14 am
If we have multiple parameters in one stored procedure and if any of those parameters has value empty, we should not us skip using a condition related to that parameter in where clause. How to achieve that? Below is the query:
If any parameter value is passed as empty, that parameter related condition should not be used in the below where clause.
CREATE PROCEDURE [dbo].[MC1_MSS_GET_FD_IQ_SEARCHORDERS]
@clientcode CHAR(3),
@ordernumber MSS_MC1_LookUpIds READONLY,
@fromdate DATETIME,
@todate DATETIME,
@orderstatus_code VARCHAR(256),
@permmasterlistid MSS_MC1_LookUpIds READONLY,
@batchnumber VARCHAR(128),
@externalordernumber MSS_MC1_LookUpIds READONLY,
@firstname VARCHAR(256),
@lastname VARCHAR(256),
@plannumber VARCHAR(128),
@halfilename MSS_MC1_LookUpIds READONLY,
@PageNum INT,
@PageSize INT,
@sortQuery VARCHAR(150),
@return_code CHAR(03) OUTPUT,
@return_msg VARCHAR(255) OUTPUT
AS
select POH.PermMasterListId,
BH.BatchNumber,
POH.OP_OrderNumber,
POH.C_FirstName,
POH.C_LastName,
POH.ExternalOrderNumber,
CAST(POH.CreateDate AS DATE) AS OrderDate,
OT.OrderPersonalizationCode,
POH.ShipMethod,
null as element_data,
PS.StatusName,
(select count(por.PermmasterListId) from tblIQPermorderRecipient por
where por.PermMasterListId=POH.PermmasterListId GROUP By por.PermmasterListId) AS RecipientCount
FROM tblIQPermOrderHeader POH LEFT JOIN tblIQBatchHeader BH WITH (NOLOCK) ON POH.BatchID = BH.BatchID
inner join tblIQPermOrderLine POL WITH (NOLOCK) ON POH.PermMasterListId = POL.PermMasterListId
LEFT JOIN tblIQProcessStatus PS WITH (NOLOCK) ON POH.statuscode= PS.statuscode
LEFT JOIN tblIqOrderType OT WITH (NOLOCK) ON POH.OrderTypeID= OT.OrderTypeID
LEFT JOIN tbliqclienttypes ict WITH (NOLOCK) ON POH.clientTypeid = ict.ClientTypeID
LEFT JOIN tbliqclient ic WITH (NOLOCK) ON ict.clientid = ic.clientid
LEFT JOIN tblIQItems it WITH (NOLOCK) ON it.ItemCode = pol.ItemCode and it.ClientId = ic.ClientID
LEFT JOIN tblIQItemAttributes iqitemattr WITH (NOLOCK) ON (iqitemattr.ItemId = it.ItemId and iqitemattr.AttributeId = @plannumberAttributeid)
WHERE
ic.ClientCode=@clientcode AND
((@orderCount = 0) OR POH.OP_OrderNumber in (SELECT lookUpIds FROM @ordernumber)) AND
((ISNULL(@fromdate, '') = '') OR CAST(POH.CreateDate AS DATE) >=@fromdate) AND
((ISNULL(@todate, '') = '') OR CAST(POH.CreateDate AS DATE) <=@todate) AND
((ISNULL(@plannumber, '') = '') OR (iqitemattr.AttributeValue = @plannumber)) AND
((@permmasterlistidCount = 0) OR POH.PermMasterListId in (SELECT lookUpIds FROM @permmasterlistid))AND
((ISNULL(@batchnumber, '') = '') OR BH.BatchNumber=@batchnumber) AND
((@extordernumCount = 0) OR POH.ExternalOrderNumber in (SELECT lookUpIds FROM @externalordernumber))AND
((ISNULL(@firstname, '') = '') OR POH.C_FirstName=@firstname) AND
((ISNULL(@lastname, '') = '') OR POH.C_LastName=@lastname) AND
((ISNULL(@orderstatus_code, '') = '') OR POL.statuscode=@orderstatus_code)
GROUP BY
POH.PermMasterListId,
BH.BatchNumber,
POH.OP_OrderNumber,
POH.C_FirstName,
POH.C_LastName,
POH.ExternalOrderNumber,
POH.CreateDate,
POH.ShipMethod,
OT.OrderPersonalizationCode,
PS.StatusName
October 14, 2021 at 1:19 pm
If we have multiple parameters in one stored procedure and if any of those parameters has value empty, we should not us skip using a condition related to that parameter in where clause. How to achieve that?
For starters you should start your own thread instead of hijacking one from 8 years ago. I would also suggest you not use the NOLOCK hint unless you are ok with sometimes returning missing and/or duplicate rows.
For the question at hand you need to read these two articles which explains how to tackle these kinds of queries.
https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2021 at 11:59 pm
Thank you very much for the response and the links. They really helped me but I have one more question now.
I am using multiple TVPs in my stored procedure and used those TVPs in dynamic sql. The execution is throwing an error: 'Procedure or function has too many arguments specified.'
I declared the TVPs as @ordernumber MSS_MC1_LookUpIds READONLY, @permmasterlistid MSS_MC1_LookUpIds READONLY, @externalordernumber MSS_MC1_LookUpIds READONLY
Used these TVPs in dynamic sql. And to execute, I am using
EXEC sp_executesql @sqlToExec, N'@ordernumber MSS_MC1_LookUpIds READONLY', @ordernumber,
N'@permmasterlistid MSS_MC1_LookUpIds READONLY', @permmasterlistid,
N'@externalordernumber MSS_MC1_LookUpIds READONLY', @externalordernumber
Which is throwing an error. When I give just one TVP to execute, it works fine - EXEC sp_executesql @sqlToExec, N'@ordernumber MSS_MC1_LookUpIds READONLY', @ordernumber
How to use these three TVPs in sp_executesql?
October 15, 2021 at 12:08 am
The answer for this is:
EXEC sp_executesql @sqlToExec, N'@ordernumber MSS_MC1_LookUpIds READONLY, @permmasterlistid MSS_MC1_LookUpIds READONLY, @externalordernumber MSS_MC1_LookUpIds READONLY',
@ordernumber, @permmasterlistid, @externalordernumber
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply