Sql Function 返回表

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接: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)--




展开阅读全文

SQL Function 返回一个字符串的问题

05-19

写一个SQL的 Function 想在Store Procedures返回一个特地的字符串 大家帮我看看哪里有问题 谢谢 rnrnrnaDeliverySchedule 是一个表rn 对于每输入的一个 @StatusType 和iOrderPlanID 会有多条记录 想根据表中的iTotalCnt 和 iDeliveredCnt的关系 返回一个特地的字符串 rnrn只要返回的所有记录中有一个的 @vchStatus是 In Processing 就 返回 'In Processing' rn如果所有的纪录都是Delivered 那么就返回'Delivered'rn如果所有的纪录都是Scheduled 那么就返回'Scheduled'rnrn这些状态都定义在code里面 小弟总是返回0 请大家帮我看一下 谢谢rnrnrnset ANSI_NULLS ONrnset QUOTED_IDENTIFIER ONrngornrnrn-- =============================================rn-- Description: get Status for Order Planrn-- =============================================rnALTER FUNCTION [dbo].[FN_OrderPlanStatus]rn(rn @StatusType varchar(100),rn @iOrderPlanID intrn)rnRETURNS varchar(200)rnASrnBEGINrnrndeclare @rst varchar(200),@iTotalCnt int,@iDeliveredCnt int,int,@iDeliveryScheduleID int,@vchStatus varchar(50)rn rn if @StatusType='Delivery'rn beginrnDeclare aPlanStatusCursor CURSOR FORrn Select a.iScheduleID,a.iTotalCnt,a.iDeliveredCntrn from aDeliverySchedule a rn where a.iOrderPlanID = @iOrderPlanIDrn rnrnrnDECLARE @aPlanStatusTable TABLErn(rnrn iDeliveryScheduleID int,rn iTotalCnt int,rn iDeliveredCnt int,rn iOrderPlanID int,rn vchStatus varchar(50)rn)rnrnOPEN aPlanStatusCursorrnFETCH NEXT FROM aPlanStatusCursor INTO @iDeliveryScheduleID,@iTotalCnt,@iDeliveredCntrn WHILE (@@FETCH_STATUS=0)rnBEGINrn if(@iTotalCnt-@iDeliveredCnt=0)rn set @rst='Delivered'rn else if(@iDeliveredCnt-@iTotalCnt<0 and @iDeliveredCnt<>0)rn set @rst='In Processing'rn else if(@iTotalCnt*@iDeliveredCnt=0)rn set @rst='Scheduled'rn else rn set @rst='Unknown'rnrn if @rst='In Processing'rn return @rst rnrnset @vchStatus=@rstrnINSERT INTO @aPlanStatusTable VALUES(@iDeliveryScheduleID,@iTotalCnt ,@iDeliveredCnt,@iOrderPlanID,@vchStatus)rn rnFETCH NEXT FROM aPlanStatusCursor INTO @iDeliveryScheduleID,@iTotalCnt ,@iDeliveredCntrnENDrnCLOSE aPlanStatusCursorrnDEALLOCATE aPlanStatusCursorrnrnSelect Distinct @rst=vchStatus from @aPlanStatusTablernrnreturn @rstrnendrn return @rstrnrnrnENDrn 论坛

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