Parameterized Views

for RuBoard

On SQL Server, an inline table-valued function is as close as you can get to a parameterized view. Like a view, an inline function doesn't have a body. It consists of a SELECT statement only. Of course, the SELECT may include unions and complex joins, but regardless it's still just a single SELECT. What an inline table gives you is a way to pass parameters into the SELECT. That is, instead of having to construct a WHERE clause every time you want to filter the view, with an inline function, you just pass in parameters. This is syntactically more concise and less error prone, and it gives you greater control over the query plan that will be produced. Oftentimes, when applying complex filter criteria in a SELECT that accesses a view that has its own complex filter criteria, you simply have to cross your fingers and pray that you'll get a good query planyou're at the mercy of the optimizer. With an inline function, the filter conditions are applied in exactly one place. You can control how and where this happens. Listing 9-13 demonstrates an inline UDF playing the role of a parameterized view:

Listing 9-13 An inline function can play the role of a parameterized function.

 CREATE FUNCTION dbo.ContactCustomersv (@CompanyName nvarchar(80), @ContactName nvarchar(60)) RETURNS TABLE AS RETURN(SELECT * FROM dbo.Customers        WHERE         CASE        WHEN @CompanyName IS NULL AND @ContactName IS NOT NULL THEN ContactName        WHEN @ContactName IS NULL AND @CompanyName IS NOT NULL THEN CompanyName        ELSE '%'        END LIKE COALESCE(@CompanyName, @ContactName, '%') ) GO SELECT * FROM dbo.ContactCustomersv(DEFAULT,'Ale%') 

(Results abridged)

 CustomerID CompanyName            ContactName      ContactTitle ---------- ---------------------- ---------------- ------------------- MORGK      Morgenstern Gesundkost Alexander Feuer  Marketing Assistant ROMEY      Romero y tomillo       Alejandra Camino Accounting Manager 

Because we can pass parameters into the function, we can essentially combine two queries into one. The function filters on different columns based on what parameters are passed in to it. Although this sort of " hoop-jumping " isn't necessarily conducive to an efficient query plan being generated, it does demonstrate the notion that an inline function playing the role of a parameterized view can do things a regular view could never do.

for RuBoard