Create parameterized VIEW in SQL Server 2008

by

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.

More Related Contents:

  1. select yesterday date in where condition
  2. SQL Server dynamic PIVOT query?
  3. SQL Server Process Queue Race Condition
  4. SQL Server 2005 Pivot on Unknown Number of Columns
  5. Is there a LastIndexOf in SQL Server?
  6. SQL – Subtracting a depleting value from rows
  7. Get all table names of a particular database by SQL query?
  8. Turn off constraints temporarily (MS SQL)
  9. Stored procedure or function expects parameter which is not supplied
  10. SQL Server Convert Varchar to Datetime
  11. OPTION (RECOMPILE) is Always Faster; Why?
  12. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  13. How to set variable from a SQL query?
  14. Case in Select Statement
  15. SQL dot notation
  16. Split function in SQL Server 2008
  17. Remove numbers from string sql server
  18. T-SQL How to create tables dynamically in stored procedures?
  19. What is the equivalent of ‘describe table’ in SQL Server?
  20. How to read the last row with SQL Server
  21. How do I edit a table in order to enable CASCADE DELETE?
  22. Does UNION ALL guarantee the order of the result set [duplicate]
  23. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  24. How to find sum of multiple columns in a table in SQL Server 2005?
  25. Query times out from web app but runs fine from management studio
  26. Backup a single table with its data from a database in sql server 2008
  27. What is the meaning of SELECT … FOR XML PATH(‘ ‘),1,1)?
  28. Try_Convert for SQL Server 2008 R2
  29. SQL Server variable scope in a stored procedure
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment