Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can we pass parameters to a view in SQL?

People also ask

Can we pass parameter to view in mysql?

Better yet, you can pass parameters to your views in a simple manner by creating a Function to GET your values from Session Variables.

Can we pass parameter to view in Oracle?

You can't pass a parameter to a view.

Can I declare variable in view?

You can't declare variables in a view.


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)

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
)

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).


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").


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!