我收到一条错误消息，指出消息 137，级别 15，状态 2，第 8 行 必须声明标量变量"@UtilityID"

Question

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   

Answer 1

'

当我使用时： `

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

`