Sql-server – How to write a query to identify common combinations of parameters used to call a stored proc

sql serversql-server-2017

recently I deployed a change into our production environment that ALTER'd an existing stored proc and essentially all the addition did was INSERT into a table I created the parameters passed in by the user calling that stored proc.

Example: User from finance passes in 15/20 possible parameters in his front-end application.. those exact parameters are first INSERTed into my table where each column is a parameter. If the user enters in nothing for a given parameter (because they are optional) the cell will be empty (not NULL).

So again, I need to aggregate and figure out what is the most common combination of parameters that are actually used (I do not care about the actual values, just that the parameter was used or not).

To clarify my table.. Each row is one stored proc call and each column is a parameter.

My table:
enter image description here

Best Answer

You need to find all of the distinct groups of parameter values for a given proc, and then sort by the count of those groups. Something like this:

SELECT 
    spc.Param1,
    spc.Param2,
    ...,
    spc.Param99,
    COUNT_BIG(*)
FROM dbo.StoredProcedureCall spc
WHERE
    spc.StoredProcedureName = 'sp_YourProc'
GROUP BY
    spc.Param1,
    spc.Param2,
    ...,
    spc.Param99
ORDER BY
    COUNT_BIG(*) DESC;

That would give the different parameters that sp_YourProc was called with, ordered from most common to least common.

If you don't care about the values, and you just want to know that certain combinations of parameters are being used, you could substitute static values like this:

SELECT 
    CASE WHEN spc.Param1 <> '' THEN 1 ELSE 0 END,
    CASE WHEN spc.Param2 <> '' THEN 1 ELSE 0 END,
    ...,
    CASE WHEN spc.Param99 <> '' THEN 1 ELSE 0 END,
    COUNT_BIG(*)
FROM dbo.StoredProcedureCall spc
WHERE
    spc.StoredProcedureName = 'sp_YourProc'
GROUP BY
    CASE WHEN spc.Param1 <> '' THEN 1 ELSE 0 END,
    CASE WHEN spc.Param2 <> '' THEN 1 ELSE 0 END,
    ...,
    CASE WHEN spc.Param99 <> '' THEN 1 ELSE 0 END,
ORDER BY
    COUNT_BIG(*) DESC;
Related Question