Create parameterized VIEW in SQL Server 2008

Question

Can we create parameterized VIEW in SQL Server 2008.

Or Any other alternative for this ?

Answer 1

Try creating an inline table-valued function. 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's a table/view just with a parameter SELECT * FROM dbo.fxnExample(1) 

If you view the execution plan for the SELECT you will not see a mention of the function at all and will actually just show you the underlying tables being queried. This is good as it means statistics on the underlying tables will be used when generating an execution plan for the query.

The thing to avoid would be a multi-statement table valued function as underlying table statistics will not be used and can result in poor performance due to a poor execution plan.
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 

Subtly different, but with potentially big differences in performance when the function is used in a query.

Answer 2

No, you cannot. But you can create a user defined table function.