必須聲明標量變數問題

這是我在這裡的第一篇文章，需要幫助：在儲存過程中，我有一些以下程式碼

CREATE PROCEDURE [dbo].[SP_getAvg] (
   @projectId INT
   ,@carrierId INT
   ,@fetchType VARCHAR(20)
   )
AS
BEGIN TRANSACTION GetDataSet * *

DECLARE @recAvg FLOAT * *
DECLARE @FACCT VARCHAR(20)
DECLARE @counter INT
DECLARE @carrierAlias AS VARCHAR(20)
DECLARE @tmpDate VARCHAR(20)
DECLARE @sql1 VARCHAR(500)

SET @counter = 1

IF @projectId > 0
BEGIN
   SELECT @FACCT = FACCT
   FROM projects
   WHERE projectId = @projectId
END

SELECT @carrierAlias = carrierAlias
FROM carriers
WHERE carrierId = @carrierId

SET @sql1 = 'SELECT @recAvg = ISNULL(AVG(cast(FWEIGHT as float)/case when CAST(FPIECES as float) = 0 then 1 else cast(FPIECES as float) end),0) from psorderh where (DATEPART(m, manifestDate) =' + cast(@counter AS VARCHAR(2)) + ') AND (DATEPART(yyyy, manifestDate) = DATEPART(yyyy, GETDATE()))' + CASE 
       WHEN @projectId > 0
           THEN 'AND FACCT = ' + @FACCT
       ELSE ''
       END + CASE 
       WHEN @carrierId > 0
           THEN 'and FCARRIER=' + @carrierAlias
       ELSE ''
       END

EXEC (@sql1)

PRINT @recAvg

……

我收到此錯誤消息：

必須聲明標量變數“@recAvg”。

我哪裡做錯了？

主要是為了消除所有 SQL 注入和字元串處理問題，並刪除未使用的東西：

CREATE PROCEDURE [dbo].[SP_getAvg]
   @projectId INT
   ,@carrierId INT
   ,@fetchType VARCHAR(20)
AS
BEGIN    
 DECLARE @recAvg FLOAT,
         @FACCT VARCHAR(20),
         @counter INT = 1,
         @carrierAlias VARCHAR(20),
         @sql NVARCHAR(MAX);

 IF @projectId > 0
 BEGIN
   SELECT @FACCT = FACCT FROM projects WHERE projectId = @projectId;
 END

 SELECT @carrierAlias = carrierAlias FROM carriers WHERE carrierId = @carrierId;

 SET @sql = N'SELECT @recAvg = ISNULL(AVG(cast(FWEIGHT as float)/case 
     when CAST(FPIECES as float) = 0 then 1 else cast(FPIECES as float) end),0) 
     from psorderh where DATEPART(MONTH, manifestDate) = @counter 
       AND DATEPART(YEAR, manifestDate) = DATEPART(YEAR, GETDATE())' 
   + CASE WHEN @projectId > 0 THEN N' AND FACCT = @FACCT' ELSE N'' END 
   + CASE WHEN @carrierId > 0 THEN N' AND FCARRIER = @carrierAlias' ELSE N'' END;

   DECLARE @params nvarchar(max) 
     = N'@counter int,@FACCT varchar(20),@carrierAlias varchar(20),@recAvg float OUTPUT';

   EXEC sys.sp_executesql @sql, @params, @counter, @FACCT, @carrierAlias, @recAvg OUTPUT;

   PRINT @recAvg;
END
GO

問題是您要在DECLARE @recAvg FLOAT * *外部聲明動態 SQL。

你應該做

SET @sql1 = 'DECLARE @recAvg FLOAT ; SELECT @recAvg ....

此外，如有疑問，請始終在執行 EXEC 或 sp_executesql 之前使用 print。

引用自：https://dba.stackexchange.com/questions/243108