sql-server - 我收到一条错误消息,提示消息 137、级别 15、状态 2、第 8 行必须声明标量变量“@UtilityID”
问题描述
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE [Type] = 'P' AND [Name] = 'GetSiteSetupPagedList')
BEGIN
DROP PROCEDURE [GetSiteSetupPagedList]
END
GO
CREATE PROCEDURE [GetSiteSetupPagedList]
(@UtilityID VARCHAR(6),
@ActiveOnly BIT = 1,
--@UtilityID VARCHAR(6),
-- Table Search Parameters
@PageSize INT = 10,
@RequestedPage INT = 0, -- 0-indexed
@SearchText VARCHAR(MAX) = '',
@UseSelectedIDs BIT = 0,
@SelectedIDs IntList READONLY ,
@OrderBy VARCHAR(MAX) = NULL,
@TotalPages INT OUT)
AS
BEGIN
SET @TotalPages = -1; -- -1 shall be interpreted as "use current value"
IF @UseSelectedIDs = 1
BEGIN
SELECT
[SiteID],
[Site].[Name],
[Site].[DefaultUtilityID],
[Utility].[Name] AS 'DefaultUtilityName'
FROM
@SelectedIDs
JOIN [Site] ON [Int] = [Site].[SiteID] AND [Site].AmendedTime IS NULL
JOIN [Utility] ON [Utility].[UtilityID] = [Site].[DefaultUtilityID] AND [Utility].AmendedTime IS NULL
RETURN
END
DECLARE @SQL NVARCHAR(MAX),
@From VARCHAR(MAX),
@Where VARCHAR(MAX),
@RowsToSkip INT,
@TotalRecords INT
-- Set the default order by
IF ISNULL(@OrderBy, '') = ''
SET @OrderBy = '[Site].[Name], [Site].[CreatedTime]'
-- Calculate Rows to Skip for paging
SET @RowsToSkip = @PageSize * @RequestedPage
-- Create the WHERE clause
SET @Where = ' WHERE [Site].AmendedTime IS NULL
AND [Utility].AmendedTime IS NULL
'
-- Active filter
IF @ActiveOnly = 1
BEGIN
SET @Where = @Where + ' AND [Site].[Active] = 1
AND [Utility].[UtilityID] = @UtilityID
'
END
-- Generic search filter
IF LEN(@SearchText) > 0
BEGIN
SET @Where = @Where + ' AND
convert(varchar(10), [SiteID]) +
[Site].[Name] +
ISNULL([Site].[DefaultUtilityID], '''') +
[Utility].[Name]
LIKE ''%'' + @SearchText + ''%''
'
END
-- Create combined FROM/WHERE clause
SET @From = ' FROM [Site]
JOIN [Utility] ON [Utility].[UtilityID] = [Site].[DefaultUtilityID]
'
+ @Where
-- Calculate total pages if we're loading the first page and not grabbing specific IDs
IF @RequestedPage = 0
BEGIN
SET @SQL = 'SELECT @TotalRecords = COUNT(*) ' + @From + ' '
--print @SQL
EXEC SP_EXECUTESQL @SQL,
N'@TotalRecords INT OUT, @SearchText VARCHAR(MAX)',
@TotalRecords = @TotalRecords OUT, @SearchText = @SearchText
SET @TotalPages = CEILING(CONVERT(DEC(10,2), @TotalRecords) / CONVERT(DEC(10,2), @PageSize))
END
-- Final select
SET @SQL = 'SELECT
[SiteID],
[Site].[Name],
[Site].[DefaultUtilityID],
[Utility].[Name] AS ''DefaultUtilityName''
' + @From + '
ORDER BY ' + @OrderBy + '
OFFSET ' + CONVERT(VARCHAR, @RowsToSkip) + ' ROWS
FETCH NEXT ' + CONVERT(VARCHAR, @PageSize) + ' ROWS ONLY
'
--print @SQL
EXEC SP_EXECUTESQL @SQL,
N'@SearchText VARCHAR(MAX)',
@SearchText = @SearchText
END
解决方案
`
当我使用时:`
EXECUTE SP_EXECUTESQL @SQL,
N'@UtilityID varchar(6)', -- this is an argument #1 with 1 parameter
N'@SearchText VARCHAR(MAX)', -- this is an argument #2 with 1 parameter
-- that's why we get an error saying too
-- many arguments
@UtilityID = @UtilityID,
@SearchText = @SearchText
`
`
I get the message saying:
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@UtilityID".
Msg 8144, Level 16, State 2, Line 2
Procedure or function has too many arguments specified.
It took me awhile to figure out but this is the correct syntax:
`
`
`
EXECUTE SP_EXECUTESQL @SQL,
N'@UtilityID varchar(6),
@SearchText VARCHAR(MAX)',
`
@UtilityID = @UtilityID,
@SearchText = @SearchText
`
推荐阅读
- amazon-web-services - AWS Control Tower 无法完全设置您的登录区:...因为日志组已存在
- c# - 为什么枚举不会触发默认选项?
- java - 自定义哈希表删除功能
- reactjs - 使用名称而不是 id 来响应路由
- javascript - Express.js 渲染未命名为 index 的 html 文件时出错
- javascript - 将类数据绑定到 XMLHttpRequest 类而不删除 xhr 数据
- r - 具有多个图层的形状单击事件
- vmware - 带有 vsphere 的 Packer 等待 IP 超时
- rust - 如何在 rust 的 GTK 事件中设置结构的值?
- python - Python -> 将数字从 0.0002321 转换为 2321