Using a parameter in a partition by clause

1

I have a stored procedure that is being used by a SQL Reporting Services report. I have a search parameter in the report and a filter in the procedure that limits the records based on the number of rows returned. Because there are several ways to search that number can change depending on which search parameter is chosen. So if I search by name or SS# I will get more results than if I search for the product sold. So I need my count to change with the change in search parameter. I tried using the variable in the partition by clause but that didn't work:

, CASE WHEN @SearchBy = 'Product Name' THEN count(ss_number) OVER (PARTITION BY ss_number, @SearchBy)

I can add a case statement and change the partition by for all 11 parameter values but I was hoping there was an easier, cleaner way.

2

Have you tried it?

3

Did you read my post?

4

You basically have two choices... Use a CASE expression or use dynamic sql.

5

I was asking how that can be done dynamically because the method I posted didn't work.