SQL中一个不明的错误
ERROR>>未能找到 ID 为 104 的数据库。可能该数据库尚未激活,也可能正在转换过程中。
这个错误我是认为比较诡异了。请高手解释一下
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn]
GO
CREATE FUNCTION fn
(
@type varchar(32)
)
RETURNS varchar(32)
AS
Begin
declare @Status_for varchar(32)
set @Status_for=case @type
when 'd' then 'd'
when 'c' then 'c'
else 'UnKnow'
end
return @Status_for
End
GO
create table #t
(
col1 varchar(32),
col2 varchar(32),
col3 int
)
insert into #t(col1, col2, col3) values('#t1', 'd', 1)
insert into #t(col1, col2, col3) values('#t2', 'd', 2)
insert into #t(col1, col2, col3) values('#t3', 'c', 1)
insert into #t(col1, col2, col3) values('#t4', 'c', 2)
create table #b
(
b1 int,
b2 varchar(32),
b3 varchar(32)
)
insert into #b(b1, b2, b3) values(1, 'd', 'd1')
insert into #b(b1, b2, b3) values(2, 'd', 'd2')
insert into #b(b1, b2, b3) values(1, 'c', 'c1')
insert into #b(b1, b2, b3) values(2, 'c', 'c2')
--下面的查询没有问题
select *
from #t a
left join #b b on b.b1 = a.col3 and b.b2 = dbo.fn(a.col2)
--这个子查询,错误就比较诡异了
select * from
(
select *
from #t a
left join #b b on b.b1 = a.col3 and b.b2 = dbo.fn(a.col2)
) a
--clear the garbage
drop table #t
drop table #b
drop function dbo.fn