Can we pass parameters to a view in SQL?

Question

Can we pass a parameter to a view in Microsoft SQL Server?

I tried to create view in the following way, but it doesn't work:

create or replace view v_emp(eno number) as select * from emp where emp_id=&eno;

Answer 1

If you don't want to use a function, you can use something like this

-- VIEW
CREATE VIEW [dbo].[vwPharmacyProducts]
AS
SELECT     PharmacyId, ProductId
FROM         dbo.Stock
WHERE     (TotalQty > 0)

-- Use of view inside a stored procedure
CREATE PROCEDURE [dbo].[usp_GetProductByFilter]
(   @pPharmacyId int ) AS

IF @pPharmacyId = 0 BEGIN SET @pPharmacyId = NULL END

SELECT  P.[ProductId], P.[strDisplayAs] FROM [Product] P
WHERE (P.[bDeleted] = 0)
    AND (P.[ProductId] IN (Select vPP.ProductId From vwPharmacyProducts vPP
                           Where vPP.PharmacyId = @pPharmacyId)
                       OR @pPharmacyId IS NULL
        )

Hope it will help

Answer 2

no. if you must then use a user defined function to which you can pass parameters into.

Answer 3

No, a view is queried no differently to SELECTing from a table.

To do what you want, use a table-valued user-defined function with one or more parameters

Answer 4

You can bypass just to run the view, SQL will wine and cry but just do this and run it! You can't save.

create or replace view v_emp(eno number) as select * from emp where (emp_id = @Parameter1);

Answer 5

Your view can reference some external table containing your parameters.

As others mentioned, the view in SQL Server cannot have external input parameters. However, you can easily fake a variable in your view using CTE. You can test-run it in your version of SQL Server.

CREATE VIEW vwImportant_Users AS
WITH params AS (
    SELECT 
    varType='%Admin%', 
    varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers, params
    WHERE status > varMinStatus OR name LIKE varType

SELECT * FROM vwImportant_Users

yielding output:

status  name
12      dbo
0       db_accessadmin
0       db_securityadmin
0       db_ddladmin

also via JOIN

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers INNER JOIN params ON 1=1
    WHERE status > varMinStatus OR name LIKE varType

also via CROSS APPLY

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers CROSS APPLY params
    WHERE status > varMinStatus OR name LIKE varType

Answer 6

As I know view can be something just like select command. You also can add parameters to this select for example in where statements like this:

 WHERE  (exam_id = @var)