Passing parameters to a SQL view

David picture David · Aug 1, 2017 · Viewed 9.6k times · Source

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

Answer

huMpty duMpty picture huMpty duMpty · Aug 1, 2017

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