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

As already stated you can't.

A possible solution would be to implement a stored function, like:

CREATE FUNCTION v_emp (@pintEno INT)
RETURNS TABLE
AS
RETURN
   SELECT * FROM emp WHERE emp_id=@pintEno;

This allows you to use it as a normal view, with:

SELECT * FROM v_emp(10)

Answer 2

There are two ways to achieve what you want. Unfortunately, neither can be done using a view.

You can either create a table valued user defined function that takes the parameter you want and returns a query result

Or you can do pretty much the same thing but create a stored procedure instead of a user defined function.

For example:

the stored procedure would look like

CREATE PROCEDURE s_emp
(
    @enoNumber INT
) 
AS 
SELECT
    * 
FROM
    emp 
WHERE 
    emp_id=@enoNumber

Or the user defined function would look like

CREATE FUNCTION u_emp
(   
    @enoNumber INT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT    
        * 
    FROM    
        emp 
    WHERE     
        emp_id=@enoNumber
)

Answer 3

Normally views are not parameterized. But you could always inject some parameters. For example using session context:

CREATE VIEW my_view
AS
SELECT *
FROM tab
WHERE num = SESSION_CONTEXT(N'my_num');

Invocation:

EXEC sp_set_session_context 'my_num', 1; 
SELECT * FROM my_view;

And another:

EXEC sp_set_session_context 'my_num', 2; 
SELECT * FROM my_view;

DBFiddle Demo

The same is applicable for Oracle (of course syntax for context function is different).

Answer 4

No you can't, as Mladen Prajdic said. Think of a view as a "static filter" on a table or a combination of tables. For example: a view may combine tables Order and Customer so you get a new "table" of rows from Order along with new columns containing the customer's name and the customer number (combination of tables). Or you might create a view that selects only unprocessed orders from the Order table (static filter).

You'd then select from the view like you would select from any other "normal" table - all "non-static" filtering must be done outside the view (like "Get all the orders for customers called Miller" or "Get unprocessed orders that came in on Dec 24th").

Answer 5

Why do you need a parameter in view? You might just use WHERE clause.

create view v_emp as select * from emp ;

and your query should do the job:

select * from v_emp where emp_id=&eno;

Answer 6

A hacky way to do it without stored procedures or functions would be to create a settings table in your database, with columns Id, Param1, Param2, etc. Insert a row into that table containing the values Id=1,Param1=0,Param2=0, etc. Then you can add a join to that table in your view to create the desired effect, and update the settings table before running the view. If you have multiple users updating the settings table and running the view concurrently things could go wrong, but otherwise it should work OK. Something like:

CREATE VIEW v_emp 
AS 
SELECT      * 
FROM        emp E
INNER JOIN  settings S
ON          S.Id = 1 AND E.emp_id = S.Param1

Answer 7

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

Answer 8

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)

Answer 9

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 10

A view is nothing more than a predifined 'SELECT' statement. So the only real answer would be: No, you cannot.

I think what you really want to do is create a stored procedure, where in principle you can use any valid SQL to do whatever you want, including accept parameters and select data.

It seems likely that you really only need to add a where clause when you select from your view though, but you didn't really provide enough details to be sure.

Answer 11

we can write a stored procedure with input parameters and then use that stored procedure to get a result set from the view. see example below.

the stored procedure is

CREATE PROCEDURE [dbo].[sp_Report_LoginSuccess] -- [sp_Report_LoginSuccess] '01/01/2010','01/30/2010'
@fromDate datetime,
@toDate datetime,
@RoleName varchar(50),
@Success int
as
If @RoleName != 'All'
Begin
   If @Success!=2
   Begin
   --fetch based on true or false
  Select * from vw_Report_LoginSuccess
  where logindatetime between  dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
  And RTrim(Upper(RoleName)) = RTrim(Upper(@RoleName)) and Success=@Success
   End
   Else
   Begin
    -- fetch all
  Select * from vw_Report_LoginSuccess
  where logindatetime between  dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
  And RTrim(Upper(RoleName)) = RTrim(Upper(@RoleName))
   End

End
Else
Begin
   If @Success!=2
   Begin
  Select * from vw_Report_LoginSuccess
  where logindatetime between  dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
  and Success=@Success
 End
 Else
 Begin
  Select * from vw_Report_LoginSuccess
  where logindatetime between  dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
 End

End

and the view from which we can get the result set is

CREATE VIEW [dbo].[vw_Report_LoginSuccess]
AS
SELECT     '3' AS UserDetailID, dbo.tblLoginStatusDetail.Success, CONVERT(varchar, dbo.tblLoginStatusDetail.LoginDateTime, 101) AS LoginDateTime,
                      CONVERT(varchar, dbo.tblLoginStatusDetail.LogoutDateTime, 101) AS LogoutDateTime, dbo.tblLoginStatusDetail.TokenID,
                      dbo.tblUserDetail.SubscriberID, dbo.aspnet_Roles.RoleId, dbo.aspnet_Roles.RoleName
FROM         dbo.tblLoginStatusDetail INNER JOIN
                      dbo.tblUserDetail ON dbo.tblLoginStatusDetail.UserDetailID = dbo.tblUserDetail.UserDetailID INNER JOIN
                      dbo.aspnet_UsersInRoles ON dbo.tblUserDetail.UserID = dbo.aspnet_UsersInRoles.UserId INNER JOIN
                      dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId
WHERE     (dbo.tblLoginStatusDetail.Success = 0)
UNION all
SELECT     dbo.tblLoginStatusDetail.UserDetailID, dbo.tblLoginStatusDetail.Success, CONVERT(varchar, dbo.tblLoginStatusDetail.LoginDateTime, 101)
                      AS LoginDateTime, CONVERT(varchar, dbo.tblLoginStatusDetail.LogoutDateTime, 101) AS LogoutDateTime, dbo.tblLoginStatusDetail.TokenID,
                      dbo.tblUserDetail.SubscriberID, dbo.aspnet_Roles.RoleId, dbo.aspnet_Roles.RoleName
FROM         dbo.tblLoginStatusDetail INNER JOIN
                      dbo.tblUserDetail ON dbo.tblLoginStatusDetail.UserDetailID = dbo.tblUserDetail.UserDetailID INNER JOIN
                      dbo.aspnet_UsersInRoles ON dbo.tblUserDetail.UserID = dbo.aspnet_UsersInRoles.UserId INNER JOIN
                      dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId
WHERE     (dbo.tblLoginStatusDetail.Success = 1) AND (dbo.tblUserDetail.SubscriberID LIKE N'P%')  

Answer 12

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 13

No, a view is static. One thing you can do (depending on the version of SQl server) is index a view.

In your example (querying only one table), an indexed view has no benefit to simply querying the table with an index on it, but if you are doing a lot of joins on tables with join conditions, an indexed view can greatly improve performance.

Answer 14

I have an idea that I haven't tried yet. You can do:

CREATE VIEW updated_customers AS
SELECT * FROM customer as aa
LEFT JOIN customer_rec as bb
ON aa.id = bb.customer_id
WHERE aa.updated_at between (SELECT start_date FROM config WHERE active = 1) 
and (SELECT end_date FROM config WHERE active = 1)

Your parameters will be saved and changed in the Config table.

Answer 15

no you can pass the parameter to the procedure in view

Answer 16

Here is an option I have not seen so far:

Just add the column you want to restrict on to the view:

create view emp_v as (
select emp_name, emp_id from emp;
)

select emp_v.emp_name from emp_v
where emp_v.emp_id = (id to restrict by)

Answer 17

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 18

Simply use this view into stored procedure with required parameter/s (eg. in SQL Server) and parameter values in querying view.

Create stored procedure with View/ table: _spCallViewWithParameters

Execute procedure:

Answer 19

While the question is well answered, I would like to just add a point. Most of the times, we think of Views as a query that sends out the data, but a View is more than that... Views can be used to update the data in underlying tables as well. You can right-click a View in SSMS and you will find the option "Edit Top 200 rows".

I believe to enable this ability of editing data as, there are certain limitations on how query is written for View, it needs to be a static query.

So unlike a User Defined Function or Stored Procedure, which sends out the queried data and closes, a View can maintain a live connection (e.g. in Microsoft Access linked tables/views) and write the updates back to the database.

Hence, in the cases where you just want to get a set a data with certain dynamic criteria, you should use UDF/SP with the required parameters.

Answer 20

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 21

I realized this task for my needs as follows

set nocount on;

  declare @ToDate date = dateadd(month,datediff(month,0,getdate())-1,0)

declare @year varchar(4)  = year(@ToDate)
declare @month varchar(2) = month(@ToDate)

declare @sql nvarchar(max)
set @sql = N'
    create or alter view dbo.wTempLogs
    as
    select * from dbo.y2019
    where
        year(LogDate) = ''_year_''
        and 
        month(LogDate) = ''_month_''    '

select @sql = replace(replace(@sql,'_year_',@year),'_month_',@month)

execute sp_executesql @sql

declare @errmsg nvarchar(max)
    set @errMsg = @sql
    raiserror (@errMsg, 0,1) with nowait

Answer 22

Yes, you can use SESSION_CONTEXT() function or even temp tables. Here is a complete guide for temp table approach using synonyms:

--1. prepare your data 
drop table if exists #temp_table;
select 1 as id, 'magic' as val into #temp_table; --if you change table definition (add/drop columns f.e.) you will need to recompile the view
drop synonym if exists table_synonym;
create synonym table_synonym for #temp_table;
go
--2. create a view
create or alter view some_view
as
select * from table_synonym --It is a hack and I'm not sure that it will work for your server. I test this on version 15.0.2000.5
go
--3. enjoy 
select * from some_view
go

--you can delete the synonym and the temp table without breaking some_view (but you would not be able to select the data)
drop table if exists #temp_table 
drop synonym if exists table_synonym;
go
select * from some_view 
go