0

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

1

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;
  • I don't believe this will work as the GROUPBY function will depend on the actual value that is used and that is not what I want. I have updated my post with a photo. Blanked out some info just in case. – Dylan Janszen Dec 13 '19 at 19:39
  • I just had a thought.... what if I update all of my records to equal '1' and then use your query? – Dylan Janszen Dec 13 '19 at 19:40
  • Hey, @DylanJanszen. Sorry I misunderstood what you were looking for (combinations of parameters vs combinations of parameter values). You're right that you could just "anonymize" the values. If you don't want to lose the data, you could do that in the query itself (answer updated). – Josh Darnell Dec 13 '19 at 19:50
  • 1
    That worked beautifully! The only thing I had to change is on the 3rd and 4th columns apparently those values are NULL in SQL Server even though it shows a default timestamp. Not really sure I have encountered that before, but I guess it is a thing. Maybe it is how the application is setting the default value and passing it through? Anyways, just changed those ones to IS NOT NULL instead of <> ''. Thanks for your help! – Dylan Janszen Dec 13 '19 at 20:16
  • You're welcome, I'm glad it worked out! Good catch with the null handling. – Josh Darnell Dec 13 '19 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.