sql function

create table ClassDemo
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(50)
)
insert into ClassDemo(ClassName) values('一班');
insert into ClassDemo(ClassName) values('二班');
select * from ClassDemo;

create table StudentDemo
(
	StudentID int primary key identity(1,1),
	StudentName nvarchar(50),
	ClassID int
)
insert into StudentDemo(StudentName,ClassID)values('学生1',1);
insert into StudentDemo(StudentName,ClassID)values('学生2',1);
insert into StudentDemo(StudentName,ClassID)values('学生3',2);

select * from StudentDemo;

--表值函数
create function fun_student(@ClassID int)--参数
returns table--返回table
as	
	return(select * from StudentDemo where ClassID=@ClassID)
			
--测试
select * from fun_student(1);
select * from fun_student(1) where StudentID=1;


--标量函数
create function fun_name(@StudentID int)
returns nvarchar(50)
as 
begin
	declare @StudentName nvarchar(50)
	select @StudentName=StudentName from StudentDemo where StudentID=@StudentID
	return (@StudentName)
end

select dbo.fun_name(1);
select * from StudentDemo where StudentName= dbo.fun_name(1);
			

存储过程刷新表函数

EXEC [sys].[sp_refreshsqlmodule] Fun_student