Msg 137, Level 15, State 2, Line 29 Must declare the scalar variable "@ACTIVE_STATUS".

See more:

SQL-Server

ALTER PROCEDURE [dbo].[S_EDIT_USER]
( @DSA_CODE VARCHAR(10)
,@REQUESTOR_DEPT VARCHAR(40)
,@ACTIVE_STATUS INT
,@MAKER_ID VARCHAR(10)
,@MAKER_IP VARCHAR(20)
,@ERROR_CODE INT OUTPUT
)
AS
BEGIN

DECLARE @CNT INT;
DECLARE @SQL NVARCHAR(MAX);

--DECLARE @REQUESTOR_DEPT VARCHAR(40);
--print(@REQUESTOR_DEPT);
SELECT @CNT = COUNT(*) FROM TMAS_UAM_USER_TMP WHERE DSA_CODE = @DSA_CODE;

IF @CNT > 0
SET @ERROR_CODE = 1;
ELSE
SET @ERROR_CODE = 0;

if @REQUESTOR_DEPT = 'N'
SET @REQUESTOR_DEPT = '';
ELSE
SET @REQUESTOR_DEPT = @REQUESTOR_DEPT ;
print @REQUESTOR_DEPT;

IF @ERROR_CODE = 0

set @SQL = 'INSERT INTO TMAS_UAM_USER_TMP (
DSA_CODE
,DSA_NAME
,DSA_CITY
,DSA_PRODUCT
,DSA_PHNO
,DSA_MOBNO
,DSA_RQSTR
,DSA_RQSTR_DEPT
,GROUP_ID
,ACTIVE_STATUS
,REQ_TYPE
,LAST_LOGED_IN
,CREATED_ID
,CREATED_IP
,CREATED_DATE
,MAKER_ID
,MAKER_IP
,MAKER_DATE
) SELECT DSA_COD
,DSA_NAM
,DSA_CTY
,PRODUCT
,DSA_PHO
,DSA_MOB
,REQUESTOR
,'+@REQUESTOR_DEPT+'
,GROUP_ID
,@ACTIVE_STATUS
,1
,LAST_LOG_DAT
,CREATED_ID
,CREATED_IP
,CREATED_DATE
,'+@MAKER_ID+'
,'+@MAKER_IP+'
,GETDATE()
FROM DSA_MST WHERE DSA_COD = '+@DSA_CODE+' and ';

if @REQUESTOR_DEPT = 'N'
begin
set @SQL = @SQL + 'REQUESTOR_DEPT is null';
print('If Query'+@SQL);
end
else
begin
set @SQL = @SQL + 'REQUESTOR_DEPT = ''' + @REQUESTOR_DEPT + '''';
print('Else Query'+@SQL);
end

--EXEC sp_executesql @SQL, N'@REQUESTOR_DEPT varchar(100) output', @REQUESTOR_DEPT output
--EXECUTE sp_executesql @SQL;
execute (@SQL);
RETURN @ERROR_CODE;
END