存储过程里动态sql语句update问题

freedomxa007 2016-01-12 04:55:41

create PROCEDURE cardtocard (@ccardno1 VARCHAR(20),@ccardno2 VARCHAR (20))
AS
BEGIN
DECLARE @card1yue INT
DECLARE @card2yue INT
DECLARE @hzname VARCHAR (40)
DECLARE @tbname VARCHAR (40)
DECLARE @sql1 nVARCHAR (500)
DECLARE @sql2 nVARCHAR (500)
DECLARE @sql3 nVARCHAR (500)
DECLARE @sql4 nVARCHAR (500)

IF ((SELECT luserl FROM S_UserParament WHERE citem='9-175')=1)
BEGIN

SELECT @hzname=cdbname FROM master..ciouser WHERE lsum=1

SET @tbname=@hzname+'..R_Remind'
SET @sql1='select @yue1 = nyczje from '+@tbname+' where ccardno='+@ccardno1
SET @sql2='select @yue2 = nyczje from '+@tbname+' where ccardno='+@ccardno2
SET @sql3='update '+@tbname+' set nyczje=0 where ccardno='+@ccardno1
SET @sql4='update' +@tbname+' set nyczje=nyczje'+ @card1yue +' where ccardno='+ @ccardno2

EXEC sp_executesql @sql1,N'@yue1 int output',@card1yue OUTPUT
SELECT @card1yue
EXEC sp_executesql @sql2,N'@yue2 int output',@card2yue OUTPUT
SELECT @card2yue
EXEC (@sql3)
EXEC (@sql4)

END
ELSE
BEGIN
SELECT @card1yue=nyczje FROM R_Remind WHERE cCardNo =@ccardno1
UPDATE R_Remind SET nYCZJE =0 WHERE cCardNo =@ccardno1
UPDATE R_Remind SET nYCZJE =nyczje +@card1yue WHERE cCardNo =@ccardno2
END
END
GO

exec cardtocard ‘888888’，‘999999’
到@sql4 提示转换成数据类型int时失败

...全文

310 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

freedomxa007 2016-01-13

打赏
举报

引用 2 楼 roy_88 的回复:

不少地方有错误用以下方式测测

CREATE  PROCEDURE cardtocard
       (
        @ccardno1 VARCHAR(20)
       ,@ccardno2 VARCHAR(20)
       )
AS
       BEGIN 
             DECLARE @card1yue INT
             DECLARE @card2yue INT
             DECLARE @hzname VARCHAR(40)
             DECLARE @tbname VARCHAR(40)
             DECLARE @sql1 NVARCHAR(4000)--sql2005以上版本建议用NVARCHAR(max)
 

             IF ( ( SELECT  luserl
                    FROM    S_UserParament
                    WHERE   citem = '9-175'
                  ) = 1 )
                BEGIN

                      SELECT    @hzname = cdbname
                      FROM      master..ciouser
                      WHERE     lsum = 1

                      SET @tbname = @hzname + '..R_Remind'
                      SET @sql1 = 'update ' + @tbname  + ' set @yue1 = nyczje,nyczje=0 where ccardno= @ccardno1;'
								+ 'update ' + @tbname + ' set @yue2 = nyczje,nyczje=nyczje + @yue1 where ccardno=  @ccardno2 ;' 

                      EXEC sp_executesql @sql1, N' @ccardno1 VARCHAR(20),@ccardno2 VARCHAR(20),@yue1 int output,@yue2 int output',
                        @ccardno1,@ccardno2,@card1yue OUTPUT ,@card2yue OUTPUT
                        
                      SELECT    @card1yue,@card2yue                

                END
             ELSE
                BEGIN
                           
                      UPDATE    R_Remind
                      SET       @card1yue = nyczje,nYCZJE = 0
                      WHERE     cCardNo = @ccardno1
                      
                      UPDATE    R_Remind
                      SET       nYCZJE = nyczje + @card1yue
                      WHERE     cCardNo = @ccardno2 
                END 
       END
GO

可以了，谢谢...

freedomxa007 2016-01-13

打赏
举报

引用 1 楼 KanzakiOrange 的回复:

@sql4 这里面有2个问题，一个是 update 的后面少了个空格。另外一个 @card1yue 是个整形，要做字符拼接要转成字符 rtrim(@card1yue) ,@ccardno2 同理，你测试一下

这样是不会报错，这样@card1yue不参与运算

中国风 2016-01-12

打赏
举报

不少地方有错误用以下方式测测

CREATE  PROCEDURE cardtocard
       (
        @ccardno1 VARCHAR(20)
       ,@ccardno2 VARCHAR(20)
       )
AS
       BEGIN 
             DECLARE @card1yue INT
             DECLARE @card2yue INT
             DECLARE @hzname VARCHAR(40)
             DECLARE @tbname VARCHAR(40)
             DECLARE @sql1 NVARCHAR(4000)--sql2005以上版本建议用NVARCHAR(max)
 

             IF ( ( SELECT  luserl
                    FROM    S_UserParament
                    WHERE   citem = '9-175'
                  ) = 1 )
                BEGIN

                      SELECT    @hzname = cdbname
                      FROM      master..ciouser
                      WHERE     lsum = 1

                      SET @tbname = @hzname + '..R_Remind'
                      SET @sql1 = 'update ' + @tbname  + ' set @yue1 = nyczje,nyczje=0 where ccardno= @ccardno1;'
								+ 'update ' + @tbname + ' set @yue2 = nyczje,nyczje=nyczje + @yue1 where ccardno=  @ccardno2 ;' 

                      EXEC sp_executesql @sql1, N' @ccardno1 VARCHAR(20),@ccardno2 VARCHAR(20),@yue1 int output,@yue2 int output',
                        @ccardno1,@ccardno2,@card1yue OUTPUT ,@card2yue OUTPUT
                        
                      SELECT    @card1yue,@card2yue                

                END
             ELSE
                BEGIN
                           
                      UPDATE    R_Remind
                      SET       @card1yue = nyczje,nYCZJE = 0
                      WHERE     cCardNo = @ccardno1
                      
                      UPDATE    R_Remind
                      SET       nYCZJE = nyczje + @card1yue
                      WHERE     cCardNo = @ccardno2 
                END 
       END
GO