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  amp eno

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

no  you can pass the parameter to the procedure in view

User · Answer

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

User · Answer

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  gt  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  gt  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  gt  varMinStatus OR name LIKE varType

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp eno

User · Answer

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

[sql] Can we pass parameters to a view in SQL?

Examples related to sql

Examples related to sql-server

Examples related to parameters

Examples related to parameter-passing

Examples related to views