/*这样还是抱declare错*/ use db_test go if exists(select * from sysobjects where name='T_date' and type='V') drop view T_date go create view T_date as select Tdate=getdate() go if exists(select * from sysobjects where name='ifee') drop function ifee go create function ifee() returns money as begin declare @inthour int , @ret money ,@fname varchar(100),@nowtime datetime select @nowtime=T_date from T_date select @inthour=datepart(hour,@nowtime) set @fname= case @inthour when 0 then 'f0' when 1 then 'f1' when 2 then 'f2' when 3 then 'f3' when 4 then 'f4' when 5 then 'f5' when 6 then 'f6' when 7 then 'f7' when 8 then 'f8' when 9 then 'f9' when 10 then 'f10' when 11 then 'f11' when 12 then 'f12' when 13 then 'f13' when 14 then 'f14' when 15 then 'f15' when 16 then 'f16' when 17 then 'f17' when 18 then 'f18' when 19 then 'f19' when 20 then 'f20' when 21 then 'f21' when 22 then 'f22' when 23 then 'f23' end select @ret=@fname from v_fee return @ret end
建议用存储过程select @ret=@fname from v_fee 这里 @fname是一个字符串,select @fname from v_fee 得到的值只会是'f1'之类的,如果你不用动态sql语句,好像是不能选出数据的所以你把'f1','f2'此类字符串赋给@ret这个money类型当然有错
看看这个的结果就知道了你需要用动态sql语句,但是函数中是不能用的if exists(select * from sysobjects where name='T_date' and type='V') drop view T_date go create view T_date as select T_date=getdate() go if exists(select * from sysobjects where name='ifee') drop function ifee go create function ifee() --returns money returns varchar(100) --此处改返回值 as begin declare @inthour int , @ret money ,@fname varchar(100),@nowtime datetime declare @test varchar(100)--用来测试 select @nowtime=T_date from T_date select @inthour=datepart(hour,@nowtime) set @fname= case @inthour when 0 then 'f0' when 1 then 'f1' when 2 then 'f2' when 3 then 'f3' when 4 then 'f4' when 5 then 'f5' when 6 then 'f6' when 7 then 'f7' when 8 then 'f8' when 9 then 'f9' when 10 then 'f10' when 11 then 'f11' when 12 then 'f12' when 13 then 'f13' when 14 then 'f14' when 15 then 'f15' when 16 then 'f16' when 17 then 'f17' when 18 then 'f18' when 19 then 'f19' when 20 then 'f20' when 21 then 'f21' when 22 then 'f22' when 23 then 'f23' end select @test=@fname from v_fee return @test end goprint dbo.ifee()
@@CPU_BUSY @@TOTAL_READ
@@IDLE @@TOTAL_WRITE
@@IO_BUSY GETDATE
@@MAX_CONNECTIONS GETUTCDATE
@@PACK_RECEIVED NEWID
@@PACK_SENT RAND
@@PACKET_ERRORS TEXTPTR
@@TIMETICKS
use db_test
go
if exists(select * from sysobjects where name='T_date' and type='V')
drop view T_date
go
create view T_date as
select Tdate=getdate()
go
if exists(select * from sysobjects where name='ifee')
drop function ifee
go
create function ifee()
returns money
as
begin
declare @inthour int , @ret money ,@fname varchar(100),@nowtime datetime
select @nowtime=T_date from T_date
select @inthour=datepart(hour,@nowtime)
set @fname=
case @inthour
when 0 then 'f0'
when 1 then 'f1'
when 2 then 'f2'
when 3 then 'f3'
when 4 then 'f4'
when 5 then 'f5'
when 6 then 'f6'
when 7 then 'f7'
when 8 then 'f8'
when 9 then 'f9'
when 10 then 'f10'
when 11 then 'f11'
when 12 then 'f12'
when 13 then 'f13'
when 14 then 'f14'
when 15 then 'f15'
when 16 then 'f16'
when 17 then 'f17'
when 18 then 'f18'
when 19 then 'f19'
when 20 then 'f20'
when 21 then 'f21'
when 22 then 'f22'
when 23 then 'f23'
end
select @ret=@fname from v_fee
return @ret
end
declare @ret money --@ret为函数返回值
declare @fname --v_fee中的字段名,字段类型全为money@ret 返回值具体由系统当前时的小时值确定,如果现在是9点返回值就是f9字段的值,v_fee表只有一行记录
这里 @fname是一个字符串,select @fname from v_fee
得到的值只会是'f1'之类的,如果你不用动态sql语句,好像是不能选出数据的所以你把'f1','f2'此类字符串赋给@ret这个money类型当然有错
drop view T_date
go
create view T_date as
select T_date=getdate()
go
if exists(select * from sysobjects where name='ifee')
drop function ifee
go
create function ifee()
--returns money
returns varchar(100) --此处改返回值
as
begin
declare @inthour int , @ret money ,@fname varchar(100),@nowtime datetime
declare @test varchar(100)--用来测试
select @nowtime=T_date from T_date
select @inthour=datepart(hour,@nowtime)
set @fname=
case @inthour
when 0 then 'f0'
when 1 then 'f1'
when 2 then 'f2'
when 3 then 'f3'
when 4 then 'f4'
when 5 then 'f5'
when 6 then 'f6'
when 7 then 'f7'
when 8 then 'f8'
when 9 then 'f9'
when 10 then 'f10'
when 11 then 'f11'
when 12 then 'f12'
when 13 then 'f13'
when 14 then 'f14'
when 15 then 'f15'
when 16 then 'f16'
when 17 then 'f17'
when 18 then 'f18'
when 19 then 'f19'
when 20 then 'f20'
when 21 then 'f21'
when 22 then 'f22'
when 23 then 'f23'
end
select @test=@fname from v_fee
return @test
end
goprint dbo.ifee()
自己改变了一下写法,使用了sp_executsql
没再使用函数