Tip #219: You want the top one

One of the challenges of creating reports is the lack of real data in development environment. There is not enough Nancies Davolios to simulate real volumes. For that reason alone production organization is often replicated “back” to test and development environments. Reports suddenly come to life and designers can see 7-figures opportunities and otherwise empty categories.
There is a price to pay, however. If you ever designed report with explicit pre-filtering then at some point you have a data source with dynamic SQL that looks like this:

SET @SQL = 'SELECT Name, DueDate, TotalAmount
FROM ('+@CRM_FilteredInvoice+') AS FA 
where ShipTo_StateOrProvince = ''FL''

UNION

SELECT Name, DueDate, TotalAmount
FROM ('+@CRM_FilteredInvoice+') as CA 
where ShipTo_StateOrProvince  = ''CA'' '

EXEC (@SQL)

All is well until you press OK button at which point you are presented with this:
Parameter prompt

It’s understandable, report designer is very determined to parse the statement to figure out the columns. There is a temptation to type select * from filteredinvoice and be done. If you are developing in a replica of production environment, chances are that will be your opportunity to have a very long lunch. There is not much fun watching parser trying to digest 2 million records for the sake of figuring data type of TotalAmount.

The solution? Use top 1 clause to limit resultset to one record only as parser does not need any more than that:
One invoice only

Tweet about this on TwitterShare on Facebook0Share on Google+0

Leave a Reply

Your email address will not be published. Required fields are marked *