How to Pass Parameters to a View in SQL

Passing parameters to a SQL view

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

Passing Parameters to Create a View in SQL Server using stored procedure - dynamic SQL

This code should work:

ALTER PROCEDURE [dbo].[sp_businessUnit_totalRequests] 
                (
                @ViewName AS VARCHAR(50),
                @RequiredBU AS VARCHAR(50)
                )
    AS
    BEGIN

        Declare @Req_View_Name AS SYSNAME;
        DECLARE @sql NVARCHAR(MAX);

        Set @Req_View_Name = @ViewName

        set @sql = '
        CREATE VIEW [Req_View_Name]
        As 
        Select [Reviewer], Count([Reviewer]) as Total_Requests From [dbo].[reviews_not_sent] where [BU] = ''' + @RequiredBU + '''  Group By [Reviewer];
        '
        SET @sql = REPLACE(@sql, '[Req_View_Name]', QUOTENAME(@Req_View_Name));

           EXEC sp_executesql @sql

    END;

This Code works although it suffers from SQL Injection. Parametrized SQL View creation is not possible.

How to use parameters in a view

You don't want a view. You want a table-valued function:

create function spider3.udf_spider (
    @CA_Removal varchar(max)
) returns table
as return(select asa.id, ase.common
           from [spider].Activity asa join
                [spider].External ase
                on asa.primaryKey = ase.owner
           where asa.type = TRY_CONVERT(int, @CA_Removal)
          );

It seems really awkward to pass in a string that you just convert to an integer. I would strongly recommend that you pass in the value as an integer:

create function spider3.udf_spider (
    @CA_Removal int
) returns table
as return(select asa.id, ase.common
           from [spider].Activity asa join
                [spider].External ase
                on asa.primaryKey = ase.owner
           where asa.type = @CA_Removal
          );

sql server: passing the parameter to the view

you can use a table function such as:

CREATE FUNCTION [dbo].[TblFunc_AAA]
(
@ID BIGINT
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM MyTable 
where aaa = @ID
)

Pass parameter to a View

Views can't have parameters, but you can wrap it into a function:

create function comedies(p_kind text)
  returns setof films
as
$$
 select *
  from films
  where kind = p_kind;
$$
language sql
stable;

Then use it like a table/view:

select *
from comedies('bla');

create SQL-view with parameter

paramerterised views are allowed in MS Access but no serious db server will allow you to construct a view that requires a mandatory parameter.

your example describes a stored procedure which should be the implementation of choice for your requirement.

another way of meeting your requirement would be to keep your @Date parameter in another table, provided it's there the view can refer to that and will return a row if the criteria is met

Passing parameter to a Stored Procedure with VIEWS?

You cannot pass parameter to a view, you have to go with table valued UDF instead if you want to pass parameters. Otherwise you need to have all column available to a view on which you want to apply filters.

e.g.

INSERT INTO tblTempTable (companyID,companyName,TotalBegInv)
    SELECT CompanyID,
           CompanyName,
           TotalBegInv
    FROM   vCompanyInfo
    WHERE  SomeColumn BETWEEN @FromDate AND @ToDate

If somecolumn is not available in vCompanyInfo definition, then you have to use underline query and can either create a new view with this column or create a UDF or stored proc to pass parameters

Passing arguments to views in SQL Server

It is possible to call User Defined Functions within a view, yes, but I would rather use a User Defined Table Function, where you pass in the parameters you require, and this in turn returns the data you wish for.

User Defined Functions

---

Related Topics