How Can I Pass Parameters to Views in SQL Server?

Parameter Passing in Views in SQL

Unlike stored procedures, views in SQL Server do not allow for direct parameter passing. This can be a limitation when needing to filter data based on user-defined criteria.

One attempted solution to this issue is to create a view with a parameter, as seen below:

create or replace view v_emp(eno number) as select * from emp where emp_id=&eno;

However, this will not work in SQL Server.

An Alternative Solution: Stored Functions

An alternative approach to passing parameters to view-like functionality is to create a stored function. A stored function can be defined with input parameters and can return a table as its result set.

Here's an example of a stored function that can act as a parameterized view:

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

This stored function takes an @pintEno integer parameter and returns a table with employees matching that ID.

To use the stored function, you can call it like a view:

SELECT * FROM v_emp(10)

This will return all employees with an emp_id of 10.

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