问一个很烂的有关问题，方法必须有返回值吗？立即结贴
问一个很烂的问题，方法必须有返回值吗？--立即结贴
如果有的话
返回的内容是？？？？
例：
CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要??的字符串
@flag bit --???志,0??成半角,1??成全角
)
RETURNS nvarchar(4000)
AS
BEGIN
|
|
|
end
返回的是@str
如果再有一个nvarchar(4000)呢？？？
------解决方案--------------------
function 返回一个表
USE AdventureWorks;
GO
IF OBJECT_ID (N 'dbo.ufn_FindReports ', N 'TF ') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
Name nvarchar(255) NOT NULL,
Sort nvarchar (255) NOT NULL --两个返回值
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ( '| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT top 1 Name, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT *
FROM dbo.ufn_FindReports(109)
GO
------解决方案--------------------
函数必须，且只能有一个返回值。
这点和c有点类似。
想要返回多个值吗？可以，返回一张表吧，表里面想写什么都随便你。^_^
------解决方案--------------------
拿一个例子来说一下,该函数功能为根据分隔符来分隔字符串,把分隔内容填充到表,
Create Function Split(@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (a varchar(100))--跟存储过程有比较大的区别(函数一定要有返回值)
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i > = 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End
If @Sql <> ' '
Insert @temp Values (@Sql)
Return
End
/*
例：
Select * from dbo.Split( '0:418001:418002:418002 ', ': ')
*/
------解决方案--------------------
函数返回类型（各种类型的数据，表）
如果有的话
返回的内容是？？？？
例：
CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要??的字符串
@flag bit --???志,0??成半角,1??成全角
)
RETURNS nvarchar(4000)
AS
BEGIN
|
|
|
end
返回的是@str
如果再有一个nvarchar(4000)呢？？？
------解决方案--------------------
function 返回一个表
USE AdventureWorks;
GO
IF OBJECT_ID (N 'dbo.ufn_FindReports ', N 'TF ') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
Name nvarchar(255) NOT NULL,
Sort nvarchar (255) NOT NULL --两个返回值
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ( '| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT top 1 Name, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT *
FROM dbo.ufn_FindReports(109)
GO
------解决方案--------------------
函数必须，且只能有一个返回值。
这点和c有点类似。
想要返回多个值吗？可以，返回一张表吧，表里面想写什么都随便你。^_^
------解决方案--------------------
拿一个例子来说一下,该函数功能为根据分隔符来分隔字符串,把分隔内容填充到表,
Create Function Split(@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (a varchar(100))--跟存储过程有比较大的区别(函数一定要有返回值)
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i > = 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End
If @Sql <> ' '
Insert @temp Values (@Sql)
Return
End
/*
例：
Select * from dbo.Split( '0:418001:418002:418002 ', ': ')
*/
------解决方案--------------------
函数返回类型（各种类型的数据，表）