locked
Conditional Stored Procedures? RRS feed

  • Question

  • User439975351 posted

    Hi all,

    I am having trouble working out the best way to aggregate a reasonably heavy query.

    In the first instance I'd welcome an opinion from any of you SQL gurus as to whether what I'm trying to achieve is possible with SQL Server alone?

    The requirement is to calculate a teams commission based on the rank (which is calculated on sales volume to determine the tier of commission payable). In addition if any "sub-team" (a team leader below the main team leader with his or her own team) accounts for 40% of the main teams total sales then that sub-team sales total must not be included in the commission calculation. The steps as I see it:

    1. Get list of team members and sub team members (recursive SQL query) 
    2. Get the rank for each member based on their min personal sales & team sales total
    3. Calculate their commission based on rank (I guess an if clause for each tier?)
    4. If any sub-member team sales are greater than or equal to 40% of the whole team sales then that  teams sales should be excluded from the commission calculation
    5. Return value of commission due to team leader

    If this is possible using just SQL, should I create 5 stored procedures to achieve this or is there a better approach?

    Thanks,

    J

    Tuesday, May 29, 2018 3:24 PM

Answers

  • User753101303 posted

    To reuse a result set coming from a stored procedure is less natural. IMO even if not planning to use a CTE in the final result, it can be still convenient during the query design phase. Note that it allows to define multiple expression, and they can depend or not from a previous expression :

    WITH a AS (
    	SELECT etc... WHERE etc...
    ),
    b AS 
    (
    	SELECT etc.. FROM a etc... -- reuse a
    ),
    c AS
    (
    	SELECT ... FROM ... -- from some other tables
    )
    -- Combine in your final query
    SELECT * FROM a
    JOIN b etc...
    JOIN c etc...
    WHERE etc...

    So when writing a complex query it can be imo a good tool (even if you'll finally reuse each part separately).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 30, 2018 11:45 AM

All replies

  • User753101303 posted

    Hi,

    It starts to be a bit complex but should be still possible. IMO it might be interesting to have a look at : https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

    It both help in doing recursive queries and so to speak allows to define queries you can later immediately reuse to tackle complexity (kind of temporary views). Wiht this you should be able to create 2/3 base  queries to get the basic information you need and then assemble those queries to produce the final result you want...

    Still unlike creating separate views you still have all under your eyes to first make this work (still if intermediate results are needed in some other context you could move some of that later to real separate views).

    Tuesday, May 29, 2018 3:48 PM
  • User439975351 posted

    Thanks for the reply PatriceSc, I have the recursion working which is a good step! For the remaining tasks I'm not sure if CTE would help? Also in your opinion do you think separate Stored Procs for each task?

    Wednesday, May 30, 2018 11:20 AM
  • User753101303 posted

    To reuse a result set coming from a stored procedure is less natural. IMO even if not planning to use a CTE in the final result, it can be still convenient during the query design phase. Note that it allows to define multiple expression, and they can depend or not from a previous expression :

    WITH a AS (
    	SELECT etc... WHERE etc...
    ),
    b AS 
    (
    	SELECT etc.. FROM a etc... -- reuse a
    ),
    c AS
    (
    	SELECT ... FROM ... -- from some other tables
    )
    -- Combine in your final query
    SELECT * FROM a
    JOIN b etc...
    JOIN c etc...
    WHERE etc...

    So when writing a complex query it can be imo a good tool (even if you'll finally reuse each part separately).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 30, 2018 11:45 AM
  • User439975351 posted

    Thanks PatriceSc

    Thursday, May 31, 2018 9:47 AM