无法在存储过程中将变量声明为 TVP 类型
我创建了一个 TVP，并尝试在存储过程中使用它来输入存储过程。 问题是我无法用它创建我的 SP。它说
Msg 137, Level 16, State 1, Procedure uspGetUsersPresentCount, Line 14
Must declare the scalar variable "@usersList".
我的 SP 是
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'uspGetUsersPresentCount')
BEGIN
DROP Procedure [AMProcedures].[uspGetUsersPresentCount]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [AMProcedures].[uspGetUsersPresentCount]
(
@usersList AS [AMProcedures].[udfUserListTVP] READONLY,
@startDate DATETIME,
@endDate DATETIME
)
AS
BEGIN
DECLARE @okStatus TINYINT = 4
SET NOCOUNT ON
SELECT MIMO.UserID, COUNT(MIMO.MoveInTime) FROM AMTables.tblUserMoveInMoveOutDetails MIMO
JOIN @usersList ON [email protected]
WHERE MIMO.Status=@okStatus AND
MIMO.MoveInTime BETWEEN @startDate AND @endDate
GROUP BY MIMO.UserID
SET NOCOUNT OFF
END
GO
任何人都可以告诉我我做错了什么......我已经尝试了我所知道的，但似乎没有任何效果。
提前致谢。
I have created a TVP and I am trying to use it in a stored proc for my input into stored proc.
the issue is I am not able to create my SP with it. Its says
Msg 137, Level 16, State 1, Procedure uspGetUsersPresentCount, Line 14
Must declare the scalar variable "@usersList".
My SP is
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'uspGetUsersPresentCount')
BEGIN
DROP Procedure [AMProcedures].[uspGetUsersPresentCount]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [AMProcedures].[uspGetUsersPresentCount]
(
@usersList AS [AMProcedures].[udfUserListTVP] READONLY,
@startDate DATETIME,
@endDate DATETIME
)
AS
BEGIN
DECLARE @okStatus TINYINT = 4
SET NOCOUNT ON
SELECT MIMO.UserID, COUNT(MIMO.MoveInTime) FROM AMTables.tblUserMoveInMoveOutDetails MIMO
JOIN @usersList ON [email protected]
WHERE MIMO.Status=@okStatus AND
MIMO.MoveInTime BETWEEN @startDate AND @endDate
GROUP BY MIMO.UserID
SET NOCOUNT OFF
END
GO
can anyone tell me what wrong am I doing ...I have tried what I knew, but nothing seems to work.
Thanks in advanced.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用用户列表的别名，或将其放在方括号中：
或
Either use an alias for the user list, or put it in square braces:
or