Sql Function 返回表

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/czh4869623/article/details/50834815

DEMO1

USE [Test]
GO
/****** Object:  UserDefinedFunction [dbo].[fun_test1]    Script Date: 2016/5/5 10:23:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fun_test1](@Parameter NVARCHAR(MAX))
RETURNS @temp TABLE (Result varchar(200)) 
AS
BEGIN
	DECLARE @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)
	RETURN
END 

--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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fun_test2](@StudentId1 INT,@StudentId2 INT)
RETURNS @Result TABLE
(
	StudentId INT,
	StudentName NVARCHAR(50)
)
AS
BEGIN
	INSERT INTO @Result SELECT StudentId,StudentName FROM [dbo].[Student] WHERE StudentId=@StudentId1
	INSERT INTO @Result SELECT StudentId,StudentName FROM [dbo].[Student] WHERE StudentId=@StudentId2
	RETURN
END
--调用--
--SELECT * FROM [dbo].[fun_test2](1,3)--




展开阅读全文

没有更多推荐了,返回首页