标量函数怎么个用法? -------------------------- Give out your sp first.If possible, you could rewrite it into a function
declare @Player varchar(40) declare GetPlayer cursor for select Player from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff group by Player order by len(Player) open GetPlayer fetch next from GetPlayer into @Player if exists(select * from sysobjects where xtype='u' and id=object_id('ttbb')) drop table ttbb create table ttbb(球员 varchar(50),场数 int,投篮 int,罚球 int,得分总数 int,平均得分 dec(4,2)) while @@fetch_status=0 begin insert ttbb select rtrim(Player)+'('+(select rtrim(SimpleCHNName) from TeamInfo where left(SimpleName,3)=Team)+')', (select count(*) from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff and Player=@Player), sum(Shot),sum(FreeShowShot),sum(PTS),avg(PTS*1.0) from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff and Player=@Player group by Player,Team fetch next from GetPlayer into @Player end select top 50 * from ttbb order by 平均得分 desc drop table ttbb close GetPlayer deallocate GetPlayer
CREATE procedure pArrayPTS @Season varchar(12), @IsPlayOff bit asset nocount off set @season= (select case when len(@Season)=2 then '200'+left(@Season,1)+'-200'+right(@Season,1) when len(@Season)=3 and charindex('-',@Season)>0 then '200'+left(@Season,1)+'-200'+right(@Season,1) when len(@Season)=3 and charindex('-',@Season)=0 then '200'+left(@Season,1)+'-20'+right(@Season,2) when len(@Season)=4 then '20'+left(@Season,2)+'-'+'20'+right(@Season,2) when len(@Season)=5 then '20'+left(@Season,2)+'-'+'20'+right(@Season,2) when len(@Season)=9 then @Season else @Season end) declare @Player varchar(40) declare GetPlayer cursor for select Player from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff group by Player order by len(Player) open GetPlayer fetch next from GetPlayer into @Player if exists(select * from sysobjects where xtype='u' and id=object_id('ttbb')) drop table ttbb create table ttbb(球员 varchar(50),场数 int,投篮 int,罚球 int,得分总数 int,平均得分 dec(4,2)) while @@fetch_status=0 begin insert ttbb select rtrim(Player)+'('+(select rtrim(SimpleCHNName) from TeamInfo where left(SimpleName,3)=Team)+')', (select count(*) from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff and Player=@Player), sum(Shot),sum(FreeShowShot),sum(PTS),avg(PTS*1.0) from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff and Player=@Player group by Player,Team fetch next from GetPlayer into @Player end select top 50 * from ttbb order by 平均得分 desc drop table ttbb close GetPlayer deallocate GetPlayer set nocount on GO
如两参数都是字符型declare @sql varchar(8000)
declare @p1 varchar(200)
set @p1='abcd'set @sql=''
select @sql=@sql+'exec spname '''+@p1+''','''+field1+''' '
exec(@sql)
Haiwer(海阔天空) 标量函数怎么个用法?
--------------------------
Give out your sp first.If possible, you could rewrite it into a function
declare GetPlayer cursor for
select Player from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff group by Player order by len(Player)
open GetPlayer
fetch next from GetPlayer into @Player
if exists(select * from sysobjects where xtype='u' and id=object_id('ttbb'))
drop table ttbb
create table ttbb(球员 varchar(50),场数 int,投篮 int,罚球 int,得分总数 int,平均得分 dec(4,2))
while @@fetch_status=0
begin
insert ttbb
select
rtrim(Player)+'('+(select rtrim(SimpleCHNName) from TeamInfo where left(SimpleName,3)=Team)+')',
(select count(*) from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff and Player=@Player),
sum(Shot),sum(FreeShowShot),sum(PTS),avg(PTS*1.0)
from vGetPlayOffSeason
where Season=@Season and IsPlayOff=@IsPlayOff and Player=@Player
group by Player,Team
fetch next from GetPlayer into @Player
end
select top 50 * from ttbb order by 平均得分 desc
drop table ttbb
close GetPlayer
deallocate GetPlayer
@Season varchar(12),
@IsPlayOff bit
asset nocount off
set @season= (select case
when len(@Season)=2 then '200'+left(@Season,1)+'-200'+right(@Season,1)
when len(@Season)=3 and charindex('-',@Season)>0 then '200'+left(@Season,1)+'-200'+right(@Season,1)
when len(@Season)=3 and charindex('-',@Season)=0 then '200'+left(@Season,1)+'-20'+right(@Season,2)
when len(@Season)=4 then '20'+left(@Season,2)+'-'+'20'+right(@Season,2)
when len(@Season)=5 then '20'+left(@Season,2)+'-'+'20'+right(@Season,2)
when len(@Season)=9 then @Season
else @Season end)
declare @Player varchar(40)
declare GetPlayer cursor for
select Player from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff group by Player order by len(Player)
open GetPlayer
fetch next from GetPlayer into @Player
if exists(select * from sysobjects where xtype='u' and id=object_id('ttbb'))
drop table ttbb
create table ttbb(球员 varchar(50),场数 int,投篮 int,罚球 int,得分总数 int,平均得分 dec(4,2))
while @@fetch_status=0
begin
insert ttbb
select
rtrim(Player)+'('+(select rtrim(SimpleCHNName) from TeamInfo where left(SimpleName,3)=Team)+')',
(select count(*) from vGetPlayOffSeason where Season=@Season and IsPlayOff=@IsPlayOff and Player=@Player),
sum(Shot),sum(FreeShowShot),sum(PTS),avg(PTS*1.0)
from vGetPlayOffSeason
where Season=@Season and IsPlayOff=@IsPlayOff and Player=@Player
group by Player,Team
fetch next from GetPlayer into @Player
end
select top 50 * from ttbb order by 平均得分 desc
drop table ttbb
close GetPlayer
deallocate GetPlayer
set nocount on
GO