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:

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.