Sql Function 返回表

来源：互联网发布：sql select 结果拼接编辑：程序博客网时间：2024/06/06 00:42

DEMO1

USE [Test]GO/****** Object:  UserDefinedFunction [dbo].[fun_test1]    Script Date: 2016/5/5 10:23:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[fun_test1](@Parameter NVARCHAR(MAX))RETURNS @temp TABLE (Result varchar(200)) ASBEGINDECLARE @MyIndex INT,@MyChar NVARCHAR(100)SET @MyIndex=CHARINDEX(',',@Parameter)SET @MyChar=SUBSTRING(@Parameter,0,@MyIndex)INSERT INTO @temp VALUES(@MyChar)SET @MyChar=SUBSTRING(@Parameter,@MyIndex+1,LEN(@Parameter))INSERT INTO @temp VALUES(@MyChar)RETURNEND --SELECT * FROM [dbo].[fun_test1]('1,2')

DEMO2

USE [Test]GO/****** Object:  UserDefinedFunction [dbo].[fun_test2]    Script Date: 2016/3/9 13:59:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[fun_test2](@StudentId1 INT,@StudentId2 INT)RETURNS @Result TABLE(StudentId INT,StudentName NVARCHAR(50))ASBEGININSERT INTO @Result SELECT StudentId,StudentName FROM [dbo].[Student] WHERE StudentId=@StudentId1INSERT INTO @Result SELECT StudentId,StudentName FROM [dbo].[Student] WHERE StudentId=@StudentId2RETURNEND--调用----SELECT * FROM [dbo].[fun_test2](1,3)--

0 0