Stored Procedure Help and dealing with Where Clause

  • 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

  • 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

  • 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.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

  • 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.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil
    Already ahead of you on that one 🙂 I did create a permanent table because of the number of SPs that would be using this table.

  • Cheryl McLaughlin-385812 - Wednesday, February 7, 2018 1:02 PM

    Phil
    Already ahead of you on that one 🙂 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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply