函数代码
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[f_DBFileForRole]
(
@name VARCHAR(50)
)
RETURNS TABLE
RETURN
(
DECLARE ReadCursor CURSOR FOR
SELECT r.[Name] + ';' FROM OA..PersonRole AS pr, OA..[Role] AS r,OA..Person as p
where pr.RoleId = r.PKID AND p.PKID = pr.AccountID AND P.TrueName = @name
OPEN ReadCursor
DECLARE @RoleName sysname
DECLARE @indextable TABLE(id INT IDENTITY(1,1),nid INT) FETCH NEXT FROM ReadCursor
INTO @RoleName WHILE @@FETCH_STATUS = 0
BEGIN
IF((SELECT COUNT(*) FROM DBFile WHERE Re13 LIKE '%'+@RoleName+'%') > 0)
BEGIN
insert into @indextable(nid) SELECT Id FROM DBFile WHERE Re13 LIKE '%'+@RoleName+'%' ORDER BY DBSaveDate ASC
END
FETCH NEXT FROM ReadCursor
INTO @RoleName
END
CLOSE ReadCursor
DEALLOCATE ReadCursor
SELECT * FROM @indextable
(出错信息:
消息 156,级别 15,状态 1,过程 f_DBFileForRole,第 8 行
关键字 'DECLARE' 附近有语法错误。
消息 102,级别 15,状态 1,过程 f_DBFileForRole,第 31 行
'(' 附近有语法错误。在线求救!!!!
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[f_DBFileForRole]
(
@name VARCHAR(50)
)
RETURNS TABLE
RETURN
(
DECLARE ReadCursor CURSOR FOR
SELECT r.[Name] + ';' FROM OA..PersonRole AS pr, OA..[Role] AS r,OA..Person as p
where pr.RoleId = r.PKID AND p.PKID = pr.AccountID AND P.TrueName = @name
OPEN ReadCursor
DECLARE @RoleName sysname
DECLARE @indextable TABLE(id INT IDENTITY(1,1),nid INT) FETCH NEXT FROM ReadCursor
INTO @RoleName WHILE @@FETCH_STATUS = 0
BEGIN
IF((SELECT COUNT(*) FROM DBFile WHERE Re13 LIKE '%'+@RoleName+'%') > 0)
BEGIN
insert into @indextable(nid) SELECT Id FROM DBFile WHERE Re13 LIKE '%'+@RoleName+'%' ORDER BY DBSaveDate ASC
END
FETCH NEXT FROM ReadCursor
INTO @RoleName
END
CLOSE ReadCursor
DEALLOCATE ReadCursor
SELECT * FROM @indextable
(出错信息:
消息 156,级别 15,状态 1,过程 f_DBFileForRole,第 8 行
关键字 'DECLARE' 附近有语法错误。
消息 102,级别 15,状态 1,过程 f_DBFileForRole,第 31 行
'(' 附近有语法错误。在线求救!!!!
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[f_DBFileForRole]
(
@name VARCHAR(50)
)
RETURNS @indextable TABLE(id INT IDENTITY(1,1),nid INT)
asbegin
DECLARE ReadCursor CURSOR FOR
SELECT r.[Name] + ';' FROM OA..PersonRole AS pr, OA..[Role] AS r,OA..Person as p
where pr.RoleId = r.PKID AND p.PKID = pr.AccountID AND P.TrueName = @nameOPEN ReadCursor
DECLARE @RoleName sysname
FETCH NEXT FROM ReadCursor
INTO @RoleNameWHILE @@FETCH_STATUS = 0
BEGIN
IF((SELECT COUNT(*) FROM DBFile WHERE Re13 LIKE '%'+@RoleName+'%') > 0)
BEGIN
insert into @indextable(nid) SELECT Id FROM DBFile WHERE Re13 LIKE '%'+@RoleName+'%' ORDER BY DBSaveDate ASC
END
FETCH NEXT FROM ReadCursor
INTO @RoleName
END
CLOSE ReadCursor
DEALLOCATE ReadCursor
SELECT * FROM @indextable
end
SET QUOTED_IDENTIFIER ON
GOCREATE FUNCTION [dbo].[f_DBFileForRole](@name VARCHAR(50))
RETURNS @indextable TABLE(id INT IDENTITY(1,1),nid INT)
AS
BEGIN
DECLARE @RoleName sysname DECLARE ReadCursor CURSOR FOR
SELECT r.[Name] + ';' FROM OA..PersonRole AS pr, OA..[Role] AS r,OA..Person as p
where pr.RoleId = r.PKID AND p.PKID = pr.AccountID AND P.TrueName = @name OPEN ReadCursor
FETCH NEXT FROM ReadCursor INTO @RoleName WHILE @@FETCH_STATUS = 0
BEGIN
IF((SELECT COUNT(*) FROM DBFile WHERE Re13 LIKE '%'+@RoleName+'%') > 0)
BEGIN
insert into @indextable(nid) SELECT Id FROM DBFile WHERE Re13 LIKE '%'+@RoleName+'%' ORDER BY DBSaveDate ASC
END
FETCH NEXT FROM ReadCursor INTO @RoleName
END
CLOSE ReadCursor
DEALLOCATE ReadCursor
RETURN
END
GOSELECT *
FROM [dbo].[f_DBFileForRole]('TEST')
GO
消息 444,级别 16,状态 2,过程 f_DBFileForRole,第 31 行
函数中含有的 SELECT 语句无法向客户端返回数据。
消息 455,级别 16,状态 2,过程 f_DBFileForRole,第 31 行
函数中最后一条语句必须是返回语句。