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 1
end

上面的存储过程有问题 

我害怕我意思表达不清楚 我把我写 的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