use WLOAS
go
if exists(select * from sysobjects where name = 'Input_Top_N')
drop procedure Input_Top_N
go
if exists(select * from sysobjects where name = 'abc')
drop view abc
go
create procedure Input_Top_N
@topn int
as
begin
declare @sql varchar(4000);
create view abc as
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB1 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB2 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB3 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB4 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
set @sql = 'select top @topn * from abc order by CI'
exec(@sql)
end
消息 156,级别 15,状态 1,过程 Input_Top_N,第 6 行
关键字 'view' 附近有语法错误。请教这是怎么回事?
create view abc as
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB1 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB2 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB3 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB4 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
gocreate procedure Input_Top_N
@topn int
as
begin
declare @sql varchar(4000);
set @sql = 'select top @topn * from abc order by CI'
exec(@sql)
end
create view abc as
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB1 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB2 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB3 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB4 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
gocreate procedure Input_Top_N
@topn int
as
begin
declare @sql varchar(4000);
set @sql = 'select top '+ltrim(@topn)+' * from abc order by CI'
exec(@sql)
end
if exists(select * from sysobjects where name = 'Input_Top_N')
drop procedure Input_Top_N
go
if exists(select * from sysobjects where name = 'abc')
drop view abc
go
create procedure Input_Top_N
@topn int
as
begin
declare @sql varchar(max);
declare @sql2 varchar(max);
set @sql='create view abc
as
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB1 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB2 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB3 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI
union all
select n.BSC,n.BTSNO,n.CELL,b.CI,n.NCI,d.DISTANCE,(c.success+c.invalidPilotFail+c.blockFail+c.otherFail) as changeno,
n.PILOT_PN,n.NCELLSYSTEM,n.NCELL
from dbo.oNeihtborhoodInfo n,dbo.tBaseStation b,dbo.tDistanceB4 d,dbo.oChange c
where n.BSC = b.BSC and n.BTSNO = b.BTSNO and n.CELL = b.CELL and b.CI = d.CI and n.NCI = d.NCI
and b.CI = c.CI and n.NCI = c.gCI'
set @sql2 = 'select top @topn * from abc order by CI'
exec(@sql)
exec(@sql2)
end
若要存储过程中创建视图,要用动态SQL来执行
@sql='create view *** as ...'
exec(@sql)
看来这个procedure和view要分开
因为@topn是int型的,拼接字符串要转换成字符串形式。
ltrim()是把字符串的左边空白去掉的函数,有专门转换数据类型的函数么?