SQL Server：可自定义参数的父子关系查询

在SQL Server里边执行父子关系查询，第一时间想到的应该是CTE（要是对CTE不太了解，可以参考这篇，讲的超详细），它兼顾了union all和递归的属性，用起来简单效率也不错。好啦，言归正传，先说说测试的基本信息：

数据库版本：SQL Server2005（CTE从2005开始支持的，2005以下的都不支持这个）

所用表：s_user

字段对应关系：S_USER_ID是其他记录的S_USER_SJ_ID。

SQL如下：

WITH result     
 AS (   
 SELECT * FROM S_USER  su WHERE su.S_DEPARTMENT_ID in (5) and (su.s_user_id=84) 
 UNION ALL     
 SELECT child.* FROM S_USER AS child INNER JOIN result  AS result 
ON child.S_USER_SJ_ID = result.s_user_id
) 
SELECT S_USER_ID,S_DEPARTMENT_ID,S_Department_Name,S_USERNAME,S_USER_SJ_NAME,S_USER_SJ_ID  FROM result  order by S_USER_SJ_ID ;

以上的SQL中有个问题，就是user的S_DEPARTMENT_ID和s_user_id是写死的，不能根据实际情况直接进行查询，如此就需要引入参数的问题了。实现思路如下：

将以上的查询信息定义到一个自定义的方法中，该方法带两个参数，然后将查询结果以table的形式返回，最后直接调用该方法。

具体实现SQL如下：

CREATE FUNCTION fu_userInfo
( @departmentid nvarchar(30),@userid nvarchar(30) )
RETURNS table
AS
RETURN (
WITH result     
 AS (   
 SELECT * FROM S_USER  su WHERE su.S_DEPARTMENT_ID in (@departmentid) and (su.s_user_id=@userid) 
 UNION ALL     
 SELECT child.* FROM S_USER AS child INNER JOIN result  AS result 
ON child.S_USER_SJ_ID = result.s_user_id
) 
SELECT S_USER_ID,S_DEPARTMENT_ID,S_Department_Name,S_USERNAME,S_USER_SJ_NAME,S_USER_SJ_ID  FROM result
)

--执行查询
SELECT * FROM fu_userInfo(5,84) order by S_USER_SJ_ID

参考链接：

1、Sql Server父子关系迭代查询SQL：http://terry0501.iteye.com/blog/1896929
2、在SQL Server中，关于with as使用介绍：http://jc-dreaming.iteye.com/blog/772030
3、sql server 视图中定义参数：http://www.cnblogs.com/qanholas/archive/2012/07/19/2599613.html