Creating a Parameterized VIEW in SQL Server 2008

Question

Is it possible to create a parameterized VIEW in SQL Server 2008.

Or any other alternative for this?

Answer 1

Try creating an inline function oriented to the table. Example:

CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER) RETURNS TABLE AS RETURN ( SELECT Field1, Field2 FROM SomeTable WHERE Field3 = @Parameter1 ) -- Then call like this, just as if it a table/view just with a parameter SELECT * FROM dbo.fxnExample(1) 

If you look at the execution plan for SELECT, you won’t see a mention of the function at all, and you really just show you what base tables are being requested. This is good, as it means that statistics on the base tables will be used when creating the execution plan for the query.

The thing to avoid is a function with many statements, since basic table statistics will not be used and may lead to poor performance due to a poor execution plan.
An example of what to avoid :

 CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER) RETURNS @Results TABLE(Field1 VARCHAR(10), Field2 VARCHAR(10)) AS BEGIN INSERT @Results SELECT Field1, Field2 FROM SomeTable WHERE Field3 = @Parameter1 RETURN END 

A subtle difference, but with potentially large differences in performance when a function is used in a request.

Answer 2

No you can’t. But you can create a custom table function .

Answer 3

actually there is one trick:

 create view view_test as select * from table where id = (select convert(int, convert(binary(4), context_info)) from master.dbo.sysprocesses where spid = @@spid) 

... in sql query:

 set context_info 2 select * from view_test 

will be the same with

 select * from table where id = 2 

but using udf is more acceptable

Answer 4

As the specialist noted, you can do this using UDF. However, for large sets using a scalar function (as refuted by the built-in table function), performance will stink, since the function is evaluated line by line. Alternatively, you can publish the same results using a stored procedure that executes a fixed query with placeholders that replaces your parameter values.

( The following is a somewhat outdated, but still relevant article on line-by-line processing for scalar UDFs.)

Edit: re comments. reducing performance in order to make it clear that this applies to scalar UDFs.

Answer 5

no. You can use UDF in which you can pass parameters.