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
– SS_DBA, Commented Aug 1, 2017 at 12:34
@WEI_DBA so you sort of execute the view from the stored procedure? — David
– David, Commented Aug 1, 2017 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
– SS_DBA, Commented Aug 1, 2017 at 12:53

huMpty duMpty · Accepted Answer · 2017-08-01 12:36:04Z

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 1, 2017 at 12:36

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

David Over a year ago

So you create the view independently and then use the stored procedure to further filter the view and return the result?

GuidoG Over a year ago

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 Over a year ago

Also always begin every stored procedure with "set nocount on"

observer Over a year ago

"@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?

huMpty duMpty Over a year ago

@observer: I would suggest you read Is a view faster than a simple query?

|

Jason A. Long · Accepted Answer · 2017-08-01 12:50:41Z

3

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;

answered Aug 1, 2017 at 12:50

Jason A. Long

4,4421 gold badge14 silver badges18 bronze badges

Collectives™ on Stack Overflow

Passing parameters to a SQL view

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related