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 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;

Solution 1

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)

Solution 2

There are two ways to achieve what you want. Unfortunately, neither can be done using a view.

You can either create a table valued user defined function that takes the parameter you want and returns a query result

Or you can do pretty much the same thing but create a stored procedure instead of a user defined function.

For example:

the stored procedure would look like

CREATE PROCEDURE s_emp
(
    @enoNumber INT
) 
AS 
SELECT
    * 
FROM
    emp 
WHERE 
    emp_id=@enoNumber

Or the user defined function would look like

CREATE FUNCTION u_emp
(   
    @enoNumber INT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT    
        * 
    FROM    
        emp 
    WHERE     
        emp_id=@enoNumber
)

Solution 3

Normally views are not parameterized. But you could always inject some parameters. For example using session context:

CREATE VIEW my_view
AS
SELECT *
FROM tab
WHERE num = SESSION_CONTEXT(N'my_num');

Invocation:

EXEC sp_set_session_context 'my_num', 1; 
SELECT * FROM my_view;

And another:

EXEC sp_set_session_context 'my_num', 2; 
SELECT * FROM my_view;

DBFiddle Demo

The same is applicable for Oracle (of course syntax for context function is different).

Solution 4

No you can't, as Mladen Prajdic said. Think of a view as a "static filter" on a table or a combination of tables. For example: a view may combine tables Order and Customer so you get a new "table" of rows from Order along with new columns containing the customer's name and the customer number (combination of tables). Or you might create a view that selects only unprocessed orders from the Order table (static filter).

You'd then select from the view like you would select from any other "normal" table - all "non-static" filtering must be done outside the view (like "Get all the orders for customers called Miller" or "Get unprocessed orders that came in on Dec 24th").

Solution 5

Why do you need a parameter in view? You might just use WHERE clause.

create view v_emp as select * from emp ;

and your query should do the job:

select * from v_emp where emp_id=&eno;

View more solutions

Comments

[Epicurist]

A view is a stored sql text of a select query. Parameters are out of the discussion. When your stored query returns the column where you want to filter with, you can do it in the calling query. E.g. "SELECT * FROM v_emp WHERE emp_id = ?"

[Lukasz Szozda]

@Epicurist Parameters are out of the discussion Too bold statement. Counterexample

[MikeMurko]

What are the practical differences between this and a view? Can you assign user permissions to only access this function?

[bobobobo]

In MySQL you write a stored procedure and have the last statement in the procedure be the resultset you want returned.

[mounaim]

can we use that request without any problem from JDBC code in java ?

[Doug_Ivison]

In some cases there will be a big performance improvement, when it's a WHERE for the table, instead of a WHERE for the view.

[TPAKTOPA]

it would terrible to use it for a request to view. But it's really usable, as a configuration/stage/environment, to use such hidden parameters. A Plus for me for that.

[nagu]

@MikeMurko one important difference is that the schema/metadata about the columns of a view can be queried if its a view. If its stored proc or a function, then I guess databases may not be able to give you that information.

[Jmoney38]

If you have a set of users who have access to your database, and you don't want them running "select * from [view]" and impacting performance, you could grant access to certain functions, which would FORCE them to provide filter parameters that, for example, leverage a certain set of index(es).

[Manngo]

@MikeMurko The main practical difference is that with a view you can’t do it, and with a function you can. Each DBMS has its own quirks and limitations, and with SQL Server the one that applies here is that views cannot be parameterised. Another practical difference applies when you access the database from an external application. Normally you will see the tables, as well as views, which present as tables. Functions don’t. In this case it doesn’t matter since the whole point is to accept a parameter, which you wouldn’t be able to send in the external application.

[Oleg Melnikov]

It should (PL/SQL and T-SQL are similar in many ways), but there is more than one way to find out :) Give it a try.

[8forty]

I think this is pretty handy. Similar to how parameters can be passed to web apps e.g. in Java.

[chb]

If you have doubts about the veracity of a response, don't post it before you verify that it's at least an adequate solution. As it stands, this is more of a question than an answer.

[Riccardo Bassilichi]

easy and functional! In other words... perfect! Thank You!

[saastn]

Just keep in mind that you can't use the SP option in a SELECT easily: read more.

[user123456]

I tired. Adding WHERE COUL = SESSION_CONTEXT(N'Ket'); in view result in Error 'SESSION_CONTEXT' is not a recognized built-in function name.

[Lukasz Szozda]

@user123456 You have to use SQL Server 2016 and above or Azure SQL Database

[aruno]

While what Doug says is somewhat true, modern databases can do a remarkable job of smartly 'expanding' a view and effectively ending up with the same result as if you were to just do the full query manually. So don't assume it will be inefficient because the database may surprise you - look at the generated query plan. A notable exception would be if the view has a GROUP BY clause that affects the output - in which case you couldn't do the WHERE from the 'outside'.

[Devin Burke]

These are not the same. Imagine a view and a function that both return select * from user where active = 1. If you query select * from fn where key=5: the view will render as select * from user where active=1 and key=5 whereas the function will render as select * from (select * from user where active=1) as fn where fn.key = 5. Point: functions are self contained subqueries.

[User1010]

One problem with this solution will be that if the query is being run in multiple sessions the wrong data in the config table might be used

[Zapnologica]

Would there be any performance issues or indexing issues by using this vs a view?