我们可以在 SQL 中将参数传递给视图吗？

我们可以在 Microsoft SQL Server 中传递一个参数给视图吗？

我试着用下面的方法做 create view，但是没有用:

create or replace view v_emp(eno number) as select * from emp where emp_id=&eno;
428497 次浏览
小开

如果必须使用用户定义的函数，则可以将参数传递给该函数。

小开

不，你不能，正如姆拉登 · 普拉季奇所说。可以将视图看作是表或表组合上的“静态过滤器”。例如: 一个视图可以合并表 Order和 Customer，这样您就可以从 Order中获得一个新的“表”，其中包含新的列，其中包含客户的名称和客户编号(表的组合)。或者，您可以创建一个仅从 Order表(静态筛选器)中选择未处理订单的视图。

然后从视图中选择，就像从其他“正常”表中选择一样——所有“非静态”过滤都必须在视图之外完成(比如“为名为 Miller 的客户获取所有订单”或“获取12月24日进来的未处理订单”)。

小开

不，查询视图与从表中进行 SELECTing 没有什么不同。

若要执行所需操作，请使用带有一个或多个参数的 表值用户定义函数

小开

有两种方法可以实现您想要的效果，但是不幸的是，这两种方法都不能通过视图来实现。

您可以创建一个表值用户定义函数，该函数接受所需的参数并返回查询结果

或者您也可以做几乎相同的事情，但是创建一个存储过程而不是用户定义的函数。

例如:

存储过程看起来像

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

或者用户定义的函数看起来像

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

没有 您可以将参数传递给视图中的过程

小开

我已经说过了，你不能。

一个可能的解决方案是实现一个存储函数，比如:

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

这允许您使用它作为一个正常的视图，具有:

SELECT * FROM v_emp(10)
小开

视图不过是一个预定义的“ SELECT”语句，所以唯一真正的答案是: 不，你不能。

我认为您真正想要做的是创建一个存储过程，在这个过程中，原则上您可以使用任何有效的 SQL 来执行任何您想要的操作，包括接受参数和选择数据。

当您从视图中进行选择时，您似乎只需要添加一个 where 子句，但是您并没有提供足够的细节来确定。

小开

不，视图是静态的。您可以做的一件事(取决于 SQL 服务器的版本)是索引一个视图。

在您的示例中(只查询一个表) ，索引视图对于简单地查询带有索引的表没有任何好处，但是如果您使用连接条件对表进行大量连接，那么索引视图可以极大地提高性能。

小开

我们可以使用输入参数编写一个存储过程，然后使用该存储过程从视图中获取结果集。 看下面的例子。

存储过程是

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

我们可以从中得到结果集的视图是

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%')
小开

在没有存储过程或函数的情况下，一种简单的方法是在数据库中创建一个设置表，其中包含 ID、 Param1、 Param2等列。向表中插入一行，其中包含 Id = 1、 Param1 = 0、 Param2 = 0等值。然后，可以在视图中向该表添加联接以创建所需的效果，并在运行视图之前更新设置表。如果有多个用户同时更新设置表和运行视图，那么可能会出错，但是否则应该可以正常工作。比如:

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

正如我所知道的，视图可以像 select 命令一样。 您还可以将参数添加到这个 select 中，例如，在 where 中使用下面的语句:

 WHERE  (exam_id = @var)
小开

为什么需要视图中的参数? 可以使用 WHERE子句。

create view v_emp as select * from emp ;

您的查询应该可以完成这项工作:

select * from v_emp where emp_id=&eno;
小开

如果你不想使用一个函数，你可以使用这样的东西

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

希望能有所帮助

小开

你可以绕过只是运行视图，SQL 会葡萄酒和哭泣，但只是这样做，并运行它！你救不了我。

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

这里有一个我迄今为止还没有见过的选择:

只需在视图中添加要限制的列:

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)
小开

视图可以引用一些包含参数的外部表。

正如其他人提到的，SQLServer 中的视图不能有外部输入参数。但是，您可以使用 CTE 轻松地在视图中伪造一个变量。可以在 SQLServer 版本中测试运行它。

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

产出:

status  name
12      dbo
0       db_accessadmin
0       db_securityadmin
0       db_ddladmin

也通过 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

也通过 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
小开

通常视图是不参数化的。但是你可以注入一些参数。例如使用 会议上下文:

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

祈求:

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

还有一个:

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

DBFiddle 演示

这同样适用于 Oracle (当然上下文函数的语法是不同的)。

小开

我有个想法，我还没试过，你可以这么做:

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)

您的参数将在 Config 表中保存和更改。

小开

我意识到这项任务是为了满足我的如下需要

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
小开

只需在查询视图中将此视图用于具有所需参数/s (例如，在 SQLServer 中)和参数值的存储过程。

使用 View/table: _ spCallViewWith 参数创建存储过程

enter image description here

执行程序:

enter image description here

小开

尽管这个问题已经得到了很好的回答，我还是想补充一点。大多数时候，我们认为视图是一个发送数据的查询，但是视图不仅仅是这样... ... 视图也可以用来更新底层表中的数据。您可以在 SSMS 中右键单击一个视图，您会发现选项“编辑前200行”。

我相信要实现这种编辑数据的能力，对于如何为 View 编写查询有一定的限制，它需要是一个静态查询。

因此，与用户定义函数或存储过程不同的是，视图可以维护实时连接(例如在 Microsoft Access 链接表/视图中) ，并将更新写回数据库。

因此，如果您只想获得一组具有某些动态条件的数据，那么应该使用带有所需参数的 UDF/SP。

小开

是的，可以使用 SESSION _ CONTEXT ()函数，甚至可以使用临时表。下面是使用同义词的临时表方法的完整指南:

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

提交答案