Can we pass parameters to a view in SQL?

Question

Can we pass a parameter to a view in Microsoft SQL Server?

I tried to create a view in the following way, but it doesn't work:

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

Answer 1

You can implement the stored function, like this:

CREATE FUNCTION v_emp (@pintEno INT)

RETURNS TABLE

AS

RETURN

SELECT * FROM emp WHERE emp_id=@pintEno;

This will allow you to use it as the normal view, by using the below code:

SELECT * FROM v_emp(10)

To master SQL statements, queries and become proficient in SQL queries, enroll in an industry-recognized SQL Online Course.

Answer 2

No, we cannot pass parameter to view in SQL.

But instead of passing parameters to view we can create a either user defined function or stored procedure.

For user defined function:

Create function( @parameter datatype) 

returns table 

as

return(select * from tablename where condition)