消息137，级别15，状态2，行29必须声明标量变量“@ ACTIVE_STATUS”。 [英] Msg 137, Level 15, State 2, Line 29 Must declare the scalar variable "@ACTIVE_STATUS".
问题描述
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;
如果@REQUESTOR_DEPT ='N'
SET @REQUESTOR_DEPT ='';
ELSE
SET @REQUESTOR_DEPT = @REQUESTOR_DEPT;
打印@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（）
来自DSA_MST WHERE DSA_COD ='+ @ DSA_CODE +'和';
如果@REQUESTOR_DEPT ='N'
开始
设置@SQL = @SQL +'REQUESTOR_DEPT为空';
print（'如果查询'+ @ SQL）;
结束
其他
开始
设置@SQL = @SQL +'REQUESTOR_DEPT ='''+ @REQUESTOR_DEPT +'''';
print（'Else Query'+ @ SQL）;
end
--EXEC sp_executesql @ SQL，N'@REQUESTOR_DEPT varchar（100）output'，@ REQUESTOR_DEPT输出
--EXECUTE sp_executesql @SQL;
执行（@SQL）;
RETURN @ERROR_CODE;
END
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
推荐答案
我认为你因为下面给出的突出显示的代码而收到错误。将它附加到变量，就像你为@REQUESTOR_DEPT变量做的那样 -
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（）
来自DSA_MST WHERE DSA_COD ='+ @ DSA_CODE +'和';
i think you are getting error because of the highlighted code given below. Append it to the variable like you are doing for @REQUESTOR_DEPT variable -
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 ';
使用proc来串行mash SQL并运行它，会被延迟。这是浪费时间，你可以在代码中做（但是，不要。）
@ACTIVE_STATUS在字符串中。因此，你传递的是这个文本，而不是传递的值。你需要对其他变量做同样的事情，'+ @ACTIVE_STATUS +'....
话虽如此，你的代码却是愚蠢的。 proc是有效的，当我复制，粘贴和创建它时不会出错。
你需要告诉我们什么行有错误，甚至可能尝试将此简化为一个基本示例，其中包含产生错误的最小代码。
Using a proc to string mash SQL and run it, is retarded. It's a waste of time, you may as well do it in code ( but, don't. )
@ACTIVE_STATUS is inside a string. As such, you're passing this text, NOT the value that was passed in. You need to do the same thing you did for the other variables, ' + @ACTIVE_STATUS + '....
Having said that, while your code is idiotic. the proc is valid and does not give an error when I copy, paste, and create it.
You need to tell us what line has the error, and perhaps even try to simplify this down to a basic example with the minimal code that gives the error.
这篇关于消息137，级别15，状态2，行29必须声明标量变量“@ ACTIVE_STATUS”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
