Passing parameters to a SQL view

Question

I have a SQL view, and I want to filter it using parameters passed to it. I was thinking about creating a stored procedure, however, after some research, I came to the conclusion that this is not possible.

Other similar threads suggested either creating a stored function or a stored procedure with the View's code embedded into it. My question is, what is the most efficient way to perform such task. My view is made up of around 70 lines of code, just for the record. What do you think? Below are some snippets that are taken from another thread.

The stored procedure would look like

CREATE PROCEDURE s_emp
(
    @enoNumber INT
) 
AS 
SQL VIEW CODE + 
WHERE 
    parameter=@stored_parameter

Or the user defined function would look like

CREATE FUNCTION u_emp
(   
    @enoNumber INT
)
RETURNS TABLE 
AS
RETURN 
(
    SQL VIEW CODE + 
    WHERE     
        parameter=@stored_parameter
)

Source Thread

Treat the View like a table. Use a `stored procedure` to `Select` from the view with parameters in the `Where` clause. — SS_DBA, Aug 01 '17 at 12:34
@WEI_DBA so you sort of execute the `view` from the `stored procedure`? — David, Aug 01 '17 at 12:50
`Select * From View_Name` Treat your view as a table. That's it. Add a `Where` clause to narrow your selection down with parameters, etc. Use `Order by` to sort. Everything you do with a table, do with your view. — SS_DBA, Aug 01 '17 at 12:53

score 3 · Accepted Answer · answered Aug 01 '17 at 12:36

3

You can use the view inside stored procedure and filter based on parameter

CREATE PROCEDURE s_emp
(
    @enoNumber INT
) 
AS 
BEGIN
   SELECT *
   FROM VIEW_NAME
   WHERE COLUMN_NAME =  @enoNumber
END

answered Aug 01 '17 at 12:36

huMpty duMpty

14,346
14
60
99

So you create the view independently and then use the stored procedure to further filter the view and return the result? – David Aug 01 '17 at 12:52
1

if the view is only used in the stored procedure then why still create the view ? Just put the entire select into the stored procedure – GuidoG Aug 01 '17 at 13:41
Also always begin every stored procedure with "set nocount on" – GuidoG Aug 01 '17 at 13:42
"@huMpty duMpty" I would like to ask the same question as @GuidoG, why should we create the view in this scenario, if we can put code directly inside the stored procedure? Is there any advantage to this approach? – observer Apr 05 '19 at 04:07
@observer: I would suggest you read [Is a view faster than a simple query?](https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query) – huMpty duMpty Apr 05 '19 at 09:01
@huMptyduMpty I know Views are faster than a simple query, In this case, the SQL query can be placed inside the stored procedure. The stored procedure execution plan will be stored and reused, which I think will give a performance boost. – observer Apr 05 '19 at 11:45

score 3 · Answer 2 · answered Aug 01 '17 at 12:50

You can create a table valued function that behaves exactly like a parameterized view...

SELECT 
    mt.SomeColumn,
    mt.ComeOtherColumn,
    mtvf.FunctionColumn,
    mtvf.AnotherFunctionColumn
FROM 
    dbo.MyTable mt
    JOIN dbo.MyTableValuedFunction('2017-07-01', '2017-08-01') mtvf
        ON mt.SomeColumn = mtvf.SomeColumn;

Passing parameters to a SQL view

2 Answers2