http://www.sqlservercentral.com/blogs/pturley/2010/03/16/how-to-parameterize-a-top-values-query/

Printed 2014/10/21 04:11PM

How to parameterize a TOP values query

By pturley, 2010/03/16

There are certain key words in a T-SQL query than cannot be parameterized, including the TOP statement.  Consider the followong attempt to parameterize the number of rows returned from the query results:

Select Top @TopCustomerCount
      F.CustomerKey, F.OrderDateKey,
      Case When DC.MiddleName Is Null Then
FirstName + ' ' + LastName
      Else FirstName + ' ' + MiddleName + '.' + ' ' +
LastName End CustomerName,
      Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate,
      Sum(F.SalesAmount) SalesAmount
From
      FactInternetSales F, DimTime DT, DimCustomer DC
Where
      F.OrderDateKey = DT.TimeKey
      And F.CustomerKey = DC.CustomerKey
      And Convert(Int,DT.CalendarYear) = @Year
Group By
      F.CustomerKey, F.OrderDateKey, DC.MiddleName,
DC.FirstName, DC.LastName, DT.FullDateAlternateKey
Order By
      F.OrderDateKey Desc, CustomerName

This query will not work because the query designer can't resolve the TopCustomerCount parameter.  To solve this problem, build an expression to concatonate the entire query, with parameterized literal values like the following example:

>

="Select Top " & Parameters!TopCustomerCount.Value & " " _ 

& "      F.CustomerKey, F.OrderDateKey, " _ 

& "      Case When DC.MiddleName Is Null Then FirstName + ' ' + LastName " _ 

& "      Else FirstName + ' ' + MiddleName + '.' + ' ' + LastName End CustomerName, " _ 

& "      Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate, " _ 

& "      Sum(F.SalesAmount) SalesAmount " _ 

& "From " _ 

& "      FactInternetSales F, DimTime DT, DimCustomer DC " _ 

& "Where " _ 

& "      F.OrderDateKey = DT.TimeKey " _ 

& "      And F.CustomerKey = DC.CustomerKey " _ 

& "      And Convert(Int,DT.CalendarYear) = " & Parameters!Year.Value & " " _ 

& "Group By " _ 

& "      F.CustomerKey, F.OrderDateKey, DC.MiddleName, DC.FirstName, DC.LastName, " _ 

& "DT.FullDateAlternateKey " _ 

& "Order By " _ 

& "      F.OrderDateKey Desc, CustomerName"

This ame technique can be used to resolve complex decision structures in code before building the SQL Statement string.  For even more flexibility, create a custom function in the report properties Code window and use Visual Basic.NET code to build and return the entire query string.



Weblog by Paul Turley and SQL Server BI Blog.
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.