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