Can we pass parameters to a view in SQL?

sql sql-server parameters parameter-passing views
arunachalam picture arunachalam · Nov 6, 2009 · Viewed 340.5k times · Source

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

I tried to create 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

Alex Bagnolini picture Alex Bagnolini · Nov 6, 2009

As already stated you can't.

A possible solution would be to implement a stored function, like: 

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

This allows you to use it as a normal view, with:

SELECT * FROM v_emp(10)

Related questions

Parameterize an SQL IN clause

How do I parameterize a query containing an IN clause with a variable number of arguments, like this one? SELECT * FROM Tags WHERE Name IN ('ruby','rails','scruffy','rubyonrails') ORDER BY Count DESC In this query, the number of arguments …

Read More
Pass table as parameter into sql server UDF

I'd like to pass a table as a parameter into a scaler UDF. I'd also prefer to restrict the parameter to tables with only one column. (optional) Is this possible? EDIT I don't want to pass a table name, I'd …

Read More
How to set table name in dynamic SQL query?

I want to set table name in a dynamic SQL query. I tried successfully for parameter as following: /* Using sp_executesql */ /* Build and Execute a Transact-SQL String with a single parameter value Using sp_executesql Command */ /* Variable Declaration */ DECLARE @EmpID …

Read More