Can SQL Server Views Accept Parameters?

Parameterized Views in SQL Server

Question:

Can a view in Microsoft SQL Server accept parameters?

Answer:

No, it is not possible to pass parameters to a view in SQL Server.

Reason:

Views are derived tables that are created using a static query. Parameters are only allowed in queries and stored procedures that dynamically execute code.

Solution:

As an alternative, you can create a stored function that takes a parameter and returns a table. This function can then be used in place of a view.

Example:

CREATE FUNCTION v_emp(@pintEno INT)
RETURNS TABLE
AS
RETURN
   SELECT * FROM emp WHERE emp_id=@pintEno;

This function can be used as a view with syntax like the following:

SELECT * FROM v_emp(10)

This will return all the records from the emp table where the emp_id column is equal to 10.

The above is the detailed content of Can SQL Server Views Accept Parameters?. For more information, please follow other related articles on the PHP Chinese website!