存储过程调用存储过程

2018/03/06 18:13
阅读数 23

from:https://www.cnblogs.com/zdkai/archive/2012/10/19/2730461.html

在存储过程中如何使用另一个存储过程返回的结果集

与这个问题具有相同性质的其他描述还包括:
如何在存储过程中检索动态SQL语句的执行结果?
如何实现类似SELECT * FROM (EXEC procedure_name @parameters_var) AS datasource WHERE ... 的功能?
procedure_name是一个存储过程的名字,@parameters_var是过程参数
如何将一个存储过程的执行结果记录集传递给另一个存储过程?
存储过程中如何根据另一个存储过程的执行结果选择执行流程?
存储过程中如何根据动态SQL语句的查询结果更改执行流程?
一个存储过程A使用另一个存储过程B的名字(或一段SQL语句或一个不确定的表名,字段名)作为参数,如何在不改动存储过程B的情况下,对存储过程B的执行结果记录集进行过滤/更改,再将过滤/更改后的结果集返回给存储过程A的调用者?


上面这些问题都有一个共同点,那就是都希望对存储过程(或动态SQL语句)的执行结果进行再处理,但是标准的SQL语句只能处理数据表,而一个存储过程(或动态SQL语句)的执行结果虽然是记录集,但它们本身不能当做数据表来处理。这样就大大限制了存储过程(或动态SQL语句)的应用范围,它们只能作为将记录集返回给应用程序前的最后一个处理层。如果我们可以像使用普通的数据表那样使用存储过程(或动态SQL语句)该有多好。
这个问题我以前的解决方法是使用OPENQUERY()或OPENDATASOURCE(),但OPENQUERY()不仅要求建立一个链接服务器,而且执行性能也让人无法满意。OPENDATASOURCE()则要求提供连接字符串,这对系统后期的维护也是一个很大的麻烦。
今天使用SQL Server联机丛书时无意中发现了一条SQL语句,竟然非常方便的解决了这个问题。这个语句就是INSERT语句。
INSERT语句在帮助中的定义是这样的:

INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}

{ [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES

其中execute_statement的解释是"任何有效的 EXECUTE 语句,它使用 SELECT 或 READTEXT 语句返回数据。"。通常我们放在这个位置的就是一段SELECT语句。但帮助既然说"任何有效的 EXECUTE 语句",那么"EXEC procedure_name"也应该可以罗?想到这一点,马上决定动手验证一下。验证结果证实没问题。即下面这样的语句

INSERT INTO table_name EXEC procedure_name @parameters_var

确实可以正常工作。有了这个基础,我们也就有了解决本文开头那些问题的方法。

基本思路是先创建一个临时表,通过INSERT ... EXEC ...语句将存储过程的返回结果保存到临时表中,接下来就可以像处理普通数据表那样对待这个临时表了。对于动态SQL语句,可以通过dbo.sp_executesql存储过程执行,或者直接作为EXEC的参数执行。具体的编写要求可以参考SQL Server联机丛书。这里只特别说明一下,临时表的表结构与存储过程(或动态SQL语句)返回的记录集的表结构兼容即可,不要求完全相同。如果直接通过EXEC执行动态SQL语句,SQL语句有4K的长度限制。


最后给出两种最常见的处理流程:
1。创建一个临时表#tmp,表结构与目标存储过程procedure_name的返回结果集兼容(兼容即可,不必相同)。
CREATE TABLE #tmp(
[columns_list]
)
2。执行存储过程并将存储过程的返回结果集插入临时表。
INSERT INTO #tmp EXEC procedure_name @parameters_var
3。现在可以使用(过滤,更改或检索)#tmp了。^_^
IF EXISTS(SELECT * FROM #tmp)
BEGIN
--执行分支1
END ELSE BEGIN
--执行分支2
END
4。别忘了最后清除临时表。
DROP TABLE #tmp

对于动态SQL语句,只要将第二步改为
INSERT INTO #tmp EXEC dbo.sp_executesql @querystring_var
即可。

 

 
 例子:
被调用的存储过程:

USE [NewITMS]
GO

/****** Object: StoredProcedure [dbo].[usp_Func_GetFuncByUserID] Script Date: 2018/3/6 18:10:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************
** Name: usp_Func_GetFuncByUserID
** Desc: 根据UserID获取用户权限
**
**
** Return Values:
**
** Parameters:
** Auth:
** Date:20018-03-06
*******************************************************************************/
CREATE PROC [dbo].[usp_Func_GetFuncByUserID]
(
@UserID UNIQUEIDENTIFIER = 'B64A4EA9-972E-4252-BB79-62BD35D62D57'
)
AS
BEGIN
WITH tree
AS ( SELECT DISTINCT
c.FuncID
FROM tbl_Base_UserRole AS b
INNER JOIN tbl_Base_RoleFunc AS c ON b.RoleID = c.RoleID
WHERE ( b.UserID = @UserID )
UNION ALL
--用户 组 权限
SELECT DISTINCT
ugfm.FuncID
FROM tbl_Base_UserGroupUserMapping ugm
INNER JOIN tbl_Base_UserGroupFuncMapping ugfm ON ugm.UserGroupID = ugfm.UserGroupID
WHERE ( ugm.UserID = @UserID )
UNION ALL
--用户 组 角色 权限
SELECT DISTINCT
ugfm.FuncID
FROM tbl_Base_UserGroupRoleMapping ugrm
INNER JOIN tbl_Base_UserGroupFuncMapping ugfm ON ugrm.UserGroupID = ugfm.UserGroupID
INNER JOIN tbl_Base_UserGroupUserMapping ugum ON ugum.UserGroupID = ugrm.UserGroupID
WHERE ugum.UserID = @UserID
-- 用户权限
UNION ALL
SELECT DISTINCT
ufm.FuncID
FROM tbl_Base_UserFuncMapping ufm
WHERE ufm.UserID = @UserID
)
SELECT DISTINCT
*
FROM tree
END

 


GO

 调用存储过程:

USE [NewITMS]
GO

/****** Object: StoredProcedure [dbo].[usp_Menu_GetMenuByUserID] Script Date: 2018/3/6 18:12:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 

/******************************************************************************
** Name: usp_Menu_GetMenuByUserID
** Desc: 根据UserID获取用户菜单
**
**
** Return Values:
**
** Parameters:
** Auth:
** Date:20018-03-06
*******************************************************************************/
CREATE proc [dbo].[usp_Menu_GetMenuByUserID]
@UserID UNIQUEIDENTIFIER ='B64A4EA9-972E-4252-BB79-62BD35D62D57'
as
begin

CREATE TABLE #tmp(
FuncID UNIQUEIDENTIFIER
)
INSERT INTO #tmp EXEC usp_Func_GetFuncByUserID @UserID

;--必要
WITH Tree --用户所有权限所对应的菜单(递归)(叶子节点+父亲节点)
AS ( SELECT a.MenuID ,
a.ParentMenuID ,
a.MenuName ,
a.FuncID ,
a.OrdinalNo
FROM tbl_Base_SysMenu AS a
INNER JOIN #tmp AS b_1 --用户所有权限
ON a.FuncID = b_1.FuncID
UNION ALL
SELECT e.MenuID ,
e.ParentMenuID ,
e.MenuName ,
e.FuncID ,
e.OrdinalNo
FROM tbl_Base_SysMenu AS e --用户所有权限所对应的菜单(递归)(叶子节点+父亲节点)
INNER JOIN Tree AS d --用户所有权限所对应的菜单(叶子节点)
ON e.MenuID = d.ParentMenuID
)
SELECT DISTINCT
CASE WHEN mt.menuID IS NULL THEN 0 --叶子节点
ELSE 1 --根节点
END MenuType ,
Tree_1.* ,
tbl_Base_Page.*
FROM ( SELECT FuncID ,
PageID ,
IsDefault
FROM tbl_Base_FuncPage
WHERE ( IsDefault = 1 )
) AS T --权限页面映射表
INNER JOIN tbl_Base_Page --页面表
ON T.PageID = tbl_Base_Page.PageID
RIGHT OUTER JOIN Tree AS Tree_1 --用户所有权限所对应的菜单(递归)(叶子节点+父亲节点)
ON T.FuncID = Tree_1.FuncID
LEFT JOIN tbl_Base_SysMenu mt --菜单表 用来确定菜单为根节点还是叶子节点
ON Tree_1.MenuID = mt.parentMenuID --菜单是否有子菜单


DROP TABLE #tmp
end


GO

 

展开阅读全文
go
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部