Must declare the scalar variable question

1 vote

3 answers

3348 views

                          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?

                        

Asked by Fred (21 rep)

Jul 17, 2019, 03:31 PM
Last activity: Jul 17, 2019, 06:03 PM

Must declare the scalar variable question

Related Questions