首页 新闻 会员 周边 捐助

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

0
悬赏园豆：5 [已解决问题] 解决于 2012-08-31 09:46

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 明白意思了吗 大侠 

s_p的主页 s_p | 初学一级 | 园豆：140
提问于：2012-08-30 14:00
< >
分享
最佳答案
0

外部再包含一个统计并且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
收获园豆：5
acepro | 小虾三级 |园豆：1218 | 2012-08-30 19:41 
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      
set @DBConsoleID='SELECT  ( CASE WHEN SUM(B.usercont) = 2 THEN 1
               ELSE 0
          END ) result
FROM    ( SELECT    COUNT(1) usercont
          FROM      A_DBManageList a1
          WHERE     a1.UserName ='''+@UserName+'''
                    AND a1.PassWrod ='''+@PassWrod+'''
          UNION  ALL
          SELECT    COUNT(1) usercont
          FROM      '+@TableName+' a2
          WHERE     a2.UserName = '''+@UserName+'''
                    AND a2.Password = '''+@PassWrod+'''
        ) B'
        exec @DBConsoleID
        print @DBConsoleID
end

ok了按照你的写法 谢谢 朋友

s_p | 园豆：140 (初学一级) | 2012-08-31 09:46
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。