0

That is my first post here, need helps: in a stored procedure I had some following code

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

......

I got this error message:

Must declare the scalar variable "@recAvg".

where I did wrong?

3

Mostly to eliminate all the SQL injection and string handling issues, and remove things unused:

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
1

The issue is you are declaring the DECLARE @recAvg FLOAT * * outside the dynamic SQL.

you should do

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

Also when in doubt, always use print before running the EXEC or sp_executesql.

1

I would recommend using sp_executesql and then declaring the parameter and outputting the desired variable. Please see below.

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)
DECLARE @sqlparam 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 @sqlparam = '@recAvg FLOAT OUTPUT'

SET @sql1 = 'SET @recAvg = (SELECT 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 sp_executesql @sql1, @sqlparam, @recAVG OUT

PRINT @recAvg

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.