SQL in查询报告类型转换失败的3种解决办法

       
     -- in查询 nvarchar转int 错误 (NodeId 为 int 类型)
     DECLARE @NodeNum NVARCHAR(200) = '3,5,6,' 
     DECLARE @FlowNumber NVARCHAR(50)= '201309240001'
        
     SELECT ID ,
            NodeName
     FROM   WF_WorkFlowNode
     WHERE  NodeId IN ( @NodeNum + '0' )
            AND FlowNumber = ' + @FlowNumber + '
     ORDER BY id DESC



     --解决方法
     --1、执行T-SQL语句 
     DECLARE @sql NVARCHAR(300)= ' SELECT  ID ,
                NodeName
        FROM    WF_WorkFlowNode
        WHERE   NodeId IN (' + @NodeNum + '0)
                AND FlowNumber =''' + @FlowNumber + '''
        ORDER BY id DESC'  
        --PRINT @sql  
     EXEC( @sql)
       
       --2、charindex函数 
     SELECT ID ,
            NodeName
     FROM   WF_WorkFlowNode
     WHERE  CHARINDEX(',' + CAST(NodeId AS NVARCHAR(10)) + ',',
                      ',' + @NodeNum + '0,') > 0
            AND FlowNumber = '' + @FlowNumber + ''
     ORDER BY id DESC
        
       -- 3、while循环取出单个值(update可以这么干,但是select则会出现多个表) 
        --SELECT SUBSTRING('123',1,2)--下标从1计算
        --SELECT CHARINDEX('2','123123123',4)
        
     DECLARE @ids NVARCHAR(200)= @NodeNum ,
        @iditem NVARCHAR(10)
     WHILE ( CHARINDEX(',', @ids) > 0 ) 
        BEGIN
            SET @iditem = SUBSTRING(@ids, 1, CHARINDEX(',', @ids) - 1)
            SET @ids = SUBSTRING(@ids, CHARINDEX(',', @ids) + 1, LEN(@ids))
            PRINT @iditem + '>' + @ids
            SELECT  ID ,
                    NodeName
            FROM    WF_WorkFlowNode
            WHERE   NodeId = @iditem
                    AND FlowNumber = '' + @FlowNumber + ''
        END
      
    

 

转载于:https://www.cnblogs.com/gagarinwjj/p/3336993.html