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

no you can pass the parameter to the procedure in view

Answer 2

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 3

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 4

There are 2 ways to acheive what you want unfortunatly 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 5

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 6

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.