SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure Help and dealing with Where Clause


Stored Procedure Help and dealing with Where Clause

Author
Message
Cheryl McLaughlin-385812
Cheryl McLaughlin-385812
SSChasing Mays
SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)

Group: General Forum Members
Points: 646 Visits: 244
I can't see the forest through the trees. I am passing parameters from SSRS to a SP. One if the parameters is called @AssettSize, which means that the user can select if they want to see everything from Asset Size 0-1M, 1M-5M, or >5M. I have been struggling to figure out the best way to pass this to the stored procedure and return results based on those ranges. A condensed version of the code and the process I began, but it is not quite working right.

If there is a better and more efficient way, please let me know. I have worked this too long that I have tunnel vision and seem stuck on the path I have been pounding. Thanks in advance for any insight/hints.

--Testing
DECLARE @PracticeAreaArray varchar(2000)= '1,2,3,4,5,6,7,8,9,10,11'
,@MonthIDArray varchar(500) = '201801'
,@ClientType varchar(500) = '1,2,3,4,5,6,7,8,9,10,11,12'
,@AssetSize bigint = 5000000

,@MinAsset int = 0
,@MidAsset int = 1000001
,@MaxAsset bigint = 50000001

SELECT csl.SolutionGroupName
,csl.PracticeAreaName
,csl.SolutionGroupSortOrder
,csl.PracticeAreaSortOrder
,csl.SolutionGroupKey
,csl.PracticeAreaKey
,fr.CustomerTypeKey
,fr.AssetSize AS AssetSize
,d.MonthOfYearNum
,d.MonthNameLong
,SUM(fr.RevenueAmount) AS Revenue
,0 AS YTDRevenue
,0 AS YOYRevenue
,0 AS Receivables
,0 AS CYBookedBacklogAmount
,0 AS FYBookedBacklogAmount
,0 AS GoalAmount
,0 AS Pipeline
,0 AS Probability
,ISNULL(fr.TotalWorkOrders,0) AS TotalWorkOrders
FROM [reporting].[vFactRevenueLine] fr
LEFT OUTER JOIN reporting.vDimProject p
ON fr.ProjectKey = p.ProjectKey
LEFT OUTER JOIN [reporting].[vDimPracticeArea] csl
ON fr.PracticeAreaKey = csl.PracticeAreaKey
LEFT OUTER JOIN [reporting].[vDimDate] d
ON d.DateKey = fr.RevenueDateKey
OR d.DateKey = fr.RevenueDateKeyOverride --Future deposit to be revenue in an earlier month
WHERE d.MonthID IN (SELECT Item FROM [dbo].[DelimitedSplit8K](@MonthIDArray,','))
AND csl.PracticeAreaKey IN (SELECT Item FROM [dbo].[DelimitedSplit8K](@PracticeAreaArray,','))
AND fr.IsPullThrough=1
AND d.DateKey = isnull(fr.RevenueDateKeyOverride,fr.RevenueDateKey)-- to include any 012018 payments as 122017 revenue
AND fr.CustomerTypeKey IN (SELECT Item FROM [dbo].[DelimitedSplit8K](@ClientType,','))
AND ((fr.AssetSize >= @MinAsset and fr.AssetSize <= @AssetSize)-- 0 and 1000000
OR (fr.AssetSize BETWEEN @MidAsset AND @AssetSize) --1000001 and 5000000
OR (fr.AssetSize BETWEEN @MaxAsset and @AssetSize)) -- 5000001 and 9000000000
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121541 Visits: 8988
Dynamic SQL is how I would go (guarding against SQL Injection, obviously). That will allow you to remove the ORs completely because you will KNOW what was passed in because you interogate it as you build your SQL statement. This will get you the best plan for each execution too.

Whatever solution you pick I would include OPTION (RECOMPILE) at the end of it too. You are SOOO exposed to parameter sniffing/widely-varying-input-parameter issues here.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214092 Visits: 24694
You could try this sort of thing (untested). Instead of passing the asset sizes, pass in a code and use that:

DECLARE @PracticeAreaArray VARCHAR(2000) = '1,2,3,4,5,6,7,8,9,10,11'
,@MonthIDArray VARCHAR(500) = '201801'
,@ClientType VARCHAR(500) = '1,2,3,4,5,6,7,8,9,10,11,12'
,@AssetSizeCode TINYINT = 1;

--AssetSizeCode can be any of 1,2,3

DROP TABLE IF EXISTS #AssetSizes
CREATE TABLE #AssetSizes (
AssetSizeCode TINYINT PRIMARY KEY CLUSTERED
,MinAssetSize INT
,MaxAssetSize INT
);

INSERT #AssetSizes (
AssetSizeCode
,MinAssetSize
,MaxAssetSize
)
VALUES (
1
,0
,1000000
)
,(
2
,1000000
,5000000
)
,(
3
,5000000
,990000000
)

SELECT...
FROM reporting.vFactRevenueLine fr
LEFT OUTER JOIN reporting.vDimProject p ON fr.ProjectKey = p.ProjectKey
LEFT OUTER JOIN reporting.vDimPracticeArea csl ON fr.PracticeAreaKey = csl.PracticeAreaKey
LEFT OUTER JOIN reporting.vDimDate d ON d.DateKey = fr.RevenueDateKey
OR d.DateKey = fr.RevenueDateKeyOverride --Future deposit to be revenue in an earlier month
JOIN #AssetSizes asi ON asi.AssetSizeCode = @AssetSizeCode
WHERE d.MonthID IN (
SELECT Item
FROM dbo.DelimitedSplit8K(@MonthIDArray, ',')
)
AND csl.PracticeAreaKey IN (
SELECT Item
FROM dbo.DelimitedSplit8K(@PracticeAreaArray, ',')
)
AND fr.IsPullThrough = 1
AND d.DateKey = ISNULL(fr.RevenueDateKeyOverride, fr.RevenueDateKey) -- to include any 012018 payments as 122017 revenue
AND fr.CustomerTypeKey IN (
SELECT Item
FROM dbo.DelimitedSplit8K(@ClientType, ',')
)
AND fr.AssetSize >= asi.MinAssetSize
AND fr.AssetSize < asi.MaxAssetSize


If performance is an issue, I would also consider creating & populating temp tables for @MonthIDArray and @PracticeAreaArray and using these temp tables in your final SELECT.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Cheryl McLaughlin-385812
Cheryl McLaughlin-385812
SSChasing Mays
SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)

Group: General Forum Members
Points: 646 Visits: 244
Phil
Thanks, I think this one will work best due to what I am working with here. Performance is not an issue as these are fairly small databases and these reports are for internal use only. Again, thanks for the input. I am the only developer, so I don't have a go to person when I need a sounding board, etc lol.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214092 Visits: 24694
Cheryl McLaughlin-385812 - Wednesday, February 7, 2018 11:47 AM
Phil
Thanks, I think this one will work best due to what I am working with here. Performance is not an issue as these are fairly small databases and these reports are for internal use only. Again, thanks for the input. I am the only developer, so I don't have a go to person when I need a sounding board, etc lol.

Good stuff. It struck me afterwards that it might be better to create and populate the #AssetSizes table as a permanent table. Your code could reference it in the same way as shown above & you'd be able to change the boundary values without having to change code.
It's a lonely job being the sole developer, I don't envy that.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Cheryl McLaughlin-385812
Cheryl McLaughlin-385812
SSChasing Mays
SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)

Group: General Forum Members
Points: 646 Visits: 244
Phil
Already ahead of you on that one Smile I did create a permanent table because of the number of SPs that would be using this table.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77482 Visits: 11159
Cheryl McLaughlin-385812 - Wednesday, February 7, 2018 1:02 PM
Phil
Already ahead of you on that one Smile I did create a permanent table because of the number of SPs that would be using this table.

Don't forget that you can use the permanent table as a source for your drop-down in SSRS also...


Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search