代码
/*
--整理者:永恒de影
--整理时间:2010/06/07
--内容:SQL函数的介绍:
*/
-- ★★★★★★★★★SQL函数书写的格式:★★★★★★★★★★★★★★★★★★★★★★★★★★★
create function fn_tonytest( @i int , @j int , @type int )
returns int
as
begin
declare @r int
if @type = 1 set @r = ( @i + @j )
if @type = 2 set @r = ( @i - @j )
if @type = 3 set @r = ( @i * @j )
if @type = 4 and @j <> 0 set @r = ( @i / @j )
return @r
end
go
select dbo.fn_tonytest( 6 , 3 , 1 ) -- 9
select dbo.fn_tonytest( 6 , 3 , 2 ) -- 3
select dbo.fn_tonytest( 6 , 3 , 3 ) -- 18
select dbo.fn_tonytest( 6 , 3 , 4 ) -- 2
-- ★★★★★★返回一个数据库表的SQL函数的格式:★★★★★★★★★★★★★★★★★★★★★★
IF OBJECT_ID ( ' FUN_MU ' ) IS NOT NULL DROP FUNCTION FUN_MU
GO
CREATE FUNCTION FUN_MU( @I INT )
RETURNS @T TABLE (NUM1 INT ,NUM2 INT ,NUM3 INT ,NUM4 INT )
AS
BEGIN
INSERT INTO @T
SELECT @I + 1 , @I + 2 , @I + 3 , @I + 4
RETURN
END
GO
DECLARE @NUM1 INT , @NUM2 INT , @NUM3 INT , @NUM4 INT
SELECT @NUM1 = NUM1, @NUM2 = NUM2, @NUM3 = NUM3, @NUM4 = NUM4 FROM DBO.FUN_MU( 4 )
SELECT @NUM1 , @NUM2 , @NUM3 , @NUM4
-- 5 6 7 8
--整理者:永恒de影
--整理时间:2010/06/07
--内容:SQL函数的介绍:
*/
-- ★★★★★★★★★SQL函数书写的格式:★★★★★★★★★★★★★★★★★★★★★★★★★★★
create function fn_tonytest( @i int , @j int , @type int )
returns int
as
begin
declare @r int
if @type = 1 set @r = ( @i + @j )
if @type = 2 set @r = ( @i - @j )
if @type = 3 set @r = ( @i * @j )
if @type = 4 and @j <> 0 set @r = ( @i / @j )
return @r
end
go
select dbo.fn_tonytest( 6 , 3 , 1 ) -- 9
select dbo.fn_tonytest( 6 , 3 , 2 ) -- 3
select dbo.fn_tonytest( 6 , 3 , 3 ) -- 18
select dbo.fn_tonytest( 6 , 3 , 4 ) -- 2
-- ★★★★★★返回一个数据库表的SQL函数的格式:★★★★★★★★★★★★★★★★★★★★★★
IF OBJECT_ID ( ' FUN_MU ' ) IS NOT NULL DROP FUNCTION FUN_MU
GO
CREATE FUNCTION FUN_MU( @I INT )
RETURNS @T TABLE (NUM1 INT ,NUM2 INT ,NUM3 INT ,NUM4 INT )
AS
BEGIN
INSERT INTO @T
SELECT @I + 1 , @I + 2 , @I + 3 , @I + 4
RETURN
END
GO
DECLARE @NUM1 INT , @NUM2 INT , @NUM3 INT , @NUM4 INT
SELECT @NUM1 = NUM1, @NUM2 = NUM2, @NUM3 = NUM3, @NUM4 = NUM4 FROM DBO.FUN_MU( 4 )
SELECT @NUM1 , @NUM2 , @NUM3 , @NUM4
-- 5 6 7 8