--示例: create table table1( id int, name varchar(200) )insert table1 select 1,'aaa' union select 2,'bbbb' union select 3,'bbbb' union select 1,'fffff' go---函数 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_test]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_test] GOCREATE FUNCTION f_test( @id int )RETURNS @re TABLE(id int,name varchar(200)) AS BEGIN
INSERT @re select * from table1 where id=@id RETURN END GO --查询 select * from f_test(1)---结果: id name ----------- 1 aaa 1 fffff
create function T_fun(@ID int) returns @T table(ID int,name varchar(10)) as begin insert @T select * from 表 where ID=@ID return end
还可以写成:CREATE FUNCTION f_test( @id int )RETURNS TABLE AS RETURN (select * from table1 where id=@id) GO
调用方法为select * from dbo.f_test(2)
還可以寫成 create function a (@ID int) returns table as declare @a table(int c) return @a
寫反好,不好意思 declare @a table(int c)改為 declare @a table(c int )
create table table1(
id int,
name varchar(200)
)insert table1 select 1,'aaa'
union select 2,'bbbb'
union select 3,'bbbb'
union select 1,'fffff'
go---函数
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_test]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_test]
GOCREATE FUNCTION f_test(
@id int
)RETURNS @re TABLE(id int,name varchar(200))
AS
BEGIN
INSERT @re select * from table1 where id=@id
RETURN
END
GO
--查询
select * from f_test(1)---结果:
id name
-----------
1 aaa
1 fffff
returns @T table(ID int,name varchar(10))
as
begin
insert @T select * from 表 where ID=@ID
return
end
@id int
)RETURNS TABLE
AS
RETURN (select * from table1 where id=@id)
GO
create function a (@ID int)
returns table
as
declare @a table(int c)
return @a
declare @a table(c int )