can we pass parameter 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;

Answer 1

no. if you must then use a user defined function to which you can pass parameters into.

Answer 2

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

Answer 3

No, a view is queried no differently to SELECTing from a table.

To do what you want, use a table-valued user-defined function with one or more parameters

Answer 4

There are 2 ways to acheive what you want unfortunatly 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
)

Answer 5

no you can pass the parameter to the procedure in view

Answer 6

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)

Answer 7

A view is nothing more than a predifined 'SELECT' statement. So the only real answer would be: No, you cannot.

I think what you really want to do is create a stored procedure, where in principle you can use any valid SQL to do whatever you want, including accept parameters and select data.

It seems likely that you really only need to add a where clause when you select from your view though, but you didn't really provide enough details to be sure.

Answer 8

No, a view is static. One thing you can do (depending on the version of SQl server) is index a view.

In your example (querying only one table), an indexed view has no benefit to simply querying the table with an index on it, but if you are doing a lot of joins on tables with join conditions, an indexed view can greatly improve performance.