Create parameterized VIEW in SQL Server 2008

Question

Can we create parameterized VIEW in SQL Server 2008   Or Any other alternative for this

User · Answer

in fact there exists 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

User · Answer

no  You can use UDF in which you can pass parameters

User · Answer

As astander has mentioned  you can do that with a UDF  However  for large sets using a scalar function  as oppoosed to a inline-table function  the performance will stink as the function is evaluated row-by-row  As an alternative  you could expose the same results via a stored procedure executing a fixed query with placeholders which substitutes in your parameter values    Here s a somewhat dated but still relevant article on row-by-row processing for scalar UDFs    Edit  comments re  degrading performance adjusted to make it clear this applies to scalar UDFs

User · Answer

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

User · Answer

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

[sql] Create parameterized VIEW in SQL Server 2008

Examples related to sql

Examples related to sql-server