sql存储过程的问题 路过的大侠看看吧 急 在线等

Question

ALTER PROCEDURE [dbo].[A_DBManageList_Exists]@UserName varchar(20),@PassWrod varchar(20),@TableName varchar(20)AS declare @DBManagID int,@DBConsoleID varchar(2000) ,@DB int begin select @DBManagID = count(1) FROM A_DBManageList WHERE UserName=@UserName and PassWrod=@PassWrod --print(@DBManagID) set @DBConsoleID='select count(1) FROM '+@TableName+'.dbo.S_SysUser a where a.UserName='''+@UserName+''' and a.Password='''+@PassWrod+''' ' exec(@DBConsoleID) set @DB=exec(@DBConsoleID) ???? print(@DB) if @DBManagID+@DB=2 return 0 else return 1end 上面的存储过程有问题  我害怕我意思表达不清楚 我把我写 的sql贴出来 (select count(1) from A_DBManageList a where a.UserName='tsp' and a.PassWrod='tsp') union all (select count(1) from TFDB_tsp.dbo.S_SysUser a where a.UserName='tsp' and a.Password='tsp') 其实那个存储过程就是对这个sql的封装 最后的结果是我希望这个2个的查询的结果=2 我就返回0 其他返回1 明白意思了吗 大侠 

Answer 1

外部再包含一个统计并且Case When 转换就可以了。

SELECT  ( CASE WHEN SUM(B.usercont) = 2 THEN 0
               ELSE 1
          END ) result
FROM    ( SELECT    COUNT(1) usercont
          FROM      A_DBManageList a1
          WHERE     a1.UserName = 'tsp'
                    AND a1.PassWrod = 'tsp'
          UNION  ALL
          SELECT    COUNT(1) usercont
          FROM      TFDB_tsp.dbo.S_SysUser a2
          WHERE     a2.UserName = 'tsp'
                    AND a2.Password = 'tsp'
        ) B