Assert parameters in a table-valued UDF

Is there a way to create "asserts" on the parameters of a table-valued UDF.

I'd like to use a table-valued UDF for performance reasons, however I know that certain parameter combinations (like start and end dates that are more than a month apart) will cause performance issues on the server for all users.

End users query the database via Excel using UDFs. UDFs (and table-valued UDFs in particular) are useful when the data is too large for Excel. Users write simple SQL queries that categorizes the data into groups to reduce the number of rows. For example, the user may be interested in weekly aggregates rather than hourly ones. Users write a group by SELECT statement to reduce the rows by 24x7=168 times. I know I can write RAISERROR statements in multistatement UDFs, but table-valued UDFs are integrated in the query optimizer so these queries are more efficient with ta开发者_如何转开发ble-valued UDFs.

So, can I define assertions on the parameters passed to a table-valued UDF?

The short answer is no - single statement TVFs can only contain a single statement.

There are a couple of alternatives you could try. One would be to carry out validation of the parameters within the SQL statement by extending the WHERE clause - like

...
WHERE ...
AND DATEDIFF(day, @startDate, @endDate) < 31

This may not be ideal for a couple of reasons - first, it may lead the users to think that no data exists meeting their criteria since there's no means to communicate why no results have been returned. Second, there's no guarantee that the DB engine won't run the data parts of the query anyway before evaluating the parameters. Thirdly, it may lead to a bad plan being cached.

If you're on SQL 2008, an alternative approach would be to look into the SQL server resource govenor which provides a means to limit users or groups of users to running queries for which the estimated execution time in seconds is less than a given threshold.

Another approach again would be to build some parameter validation into the Excel sheets your users use for their queries, but this may not be practical depending on the details of your setup.