一直在项目中很少用到存储过程，虽说是能够高效执行sql，各种好处，但用得不熟练，总是写很长的sql也不会用存储过程替代，但这次不管sql写多长都不好实现，所以在同事的指导下还是用存储过程实现的，第一次体验存储过程的好处，所以记录下来，以便今后使用
使用存储过程原因：
实验管理员列表数据过多，需要支持分页，但数据源是多处拼凑出来的，不好直接实现分页，
所以使用存储过程，把查询出来的数据源新增到临时表，再进行分页就简便多了
原始sql:
1 DECLARE @ID NVARCHAR(100); 2 SET @ID ='00000000-0000-0000-0000-000000000001'; 3 WITH Test AS 4 ( 5 SELECT * FROM T_Sys_Domain where Enable=1 and DomainID='00000000-0000-0000-0000-000000000001' 6 UNION ALL 7 SELECT a.* FROM T_Sys_Domain a, Test b where a.Enable=1 and b.domainId = a.ParentID and a.InstructorShareToParent=1 8 ) 9 select * from 10 ( select d.DomainName Domian,11 c.UserID,c.LoginName,c.FirstName,c.LastName,c.Email,c.CompanyName,c.CreateTime 12 from Test as d,dbo.T_Sys_User as c13 where d.domainid=c.domainid and IsInstructor=1 and IsEnabled=1 and LoginName like '%%' ) t 14 union15 select (select DomainName from T_Sys_Domain where DomainID=u.DomainID) Domian,16 u.UserID,u.LoginName,u.FirstName,u.LastName,u.Email,u.CompanyName,u.CreateTime from dbo.T_Sys_User u where IsInstructor=1 and IsEnabled=1 17 and DomainID in(18 select ShareDomainID from dbo.T_Sys_Share s where DomainID='00000000-0000-0000-0000-000000000001' and ShareType=119 and s.ShareDomainID in (select DomainID from T_Sys_Domain where Enable=1)) 20 and LoginName like '%%'21 order by LoginName
用到的存储过程：
1 CREATE PROCEDURE [P_SelectInstructor] 2 @pageIndex INT, 3 @pageSize INT , 4 @SqlSelStr nvarchar(4000), 5 @counNum int out 6 AS 7 print @SqlSelStr; 8 begin 9 10 IF OBJECT_ID('tempdb..#tb') is not null11 DROP TABLE #tb12 13 /**/--创建临时表，字段跟显示的列表一样14 create table #tb( 15 DomainName nvarchar(100),16 UserID uniqueidentifier,17 LoginName nvarchar(80),18 FirstName nvarchar(80),19 LastName nvarchar(80),20 Email nvarchar(320),21 CompanyName nvarchar(80),22 CreateTime datetime 23 )24 25 /**/26 insert into #tb exec(@SqlSelStr) 27 begin28 select @counNum = COUNT('1') From #tb;29 with InstructorTable as30 ( 31 select row_number() over(order by CreateTime desc) as num2, * from #tb 32 ) 33 select DomainName,UserID,LoginName,FirstName,LastName,Email,CompanyName,CreateTime34 from InstructorTable35 where num2 between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize36 end37 end 1 public DataTable GetInstructorList(Guid domainId, string name) 2 { 3 if (!string.IsNullOrEmpty(name)) 4 { 5 name = "'%/" + name + "%' ESCAPE '/'"; 6 } 7 else 8 { 9 name = "'%" + name + "%'";10 }11 12 13 string sql = string.Format(14 @"DECLARE @ID NVARCHAR(100);15 SET @ID ='{0}';16 WITH Test AS17 ( 18 SELECT * FROM T_Sys_Domain where Enable=1 and DomainID='{0}'19 UNION ALL 20 SELECT a.* FROM T_Sys_Domain a, Test b where a.Enable=1 and b.domainId = a.ParentID and a.InstructorShareToParent=121 ) 22 select * from 23 ( select d.DomainName Domian,24 c.UserID,c.LoginName,c.FirstName,c.LastName,c.Email,c.CompanyName,c.CreateTime 25 from Test as d,dbo.T_Sys_User as c26 where d.domainid=c.domainid and IsInstructor=1 and IsEnabled=1 and LoginName like {1} ) t 27 union28 select (select DomainName from T_Sys_Domain where DomainID=u.DomainID) Domian,29 u.UserID,u.LoginName,u.FirstName,u.LastName,u.Email,u.CompanyName,u.CreateTime from dbo.T_Sys_User u where IsInstructor=1 and IsEnabled=1 30 and DomainID in(31 select ShareDomainID from dbo.T_Sys_Share s where DomainID='{0}' and ShareType=132 and s.ShareDomainID in (select DomainID from T_Sys_Domain where Enable=1)) 33 and LoginName like {1}34 order by LoginName ", domainId, name);35 36 DataSet ds = null;37 using (DBFactory dbfac = new DBFactory())38 {39 ds = dbfac.ExecuteSelectSql(sql);40 }41 42 if (ds != null && ds.Tables.Count > 0)43 {44 return ds.Tables[0];45 }46 else47 {48 return null;49 }50 } 1 public DataTable GetInstructorList(Guid domainId, string name, int pageSize,int pageIndex, out int count) 2 { 3 if (!string.IsNullOrEmpty(name)) 4 { 5 name = "'%/" + name + "%' ESCAPE '/'"; 6 } 7 else 8 { 9 name = "'%" + name + "%'";10 }11 12 13 string sql = string.Format(14 @"DECLARE @ID NVARCHAR(100);15 SET @ID ='{0}';16 WITH Test AS17 ( 18 SELECT * FROM T_Sys_Domain where Enable=1 and DomainID='{0}'19 UNION ALL 20 SELECT a.* FROM T_Sys_Domain a, Test b where a.Enable=1 and b.domainId = a.ParentID and a.InstructorShareToParent=121 ) 22 select * from 23 ( select d.DomainName Domian,24 c.UserID,c.LoginName,c.FirstName,c.LastName,c.Email,c.CompanyName,c.CreateTime 25 from Test as d,dbo.T_Sys_User as c26 where d.domainid=c.domainid and IsInstructor=1 and IsEnabled=1 and LoginName like {1} ) t 27 union28 select (select DomainName from T_Sys_Domain where DomainID=u.DomainID) Domian,29 u.UserID,u.LoginName,u.FirstName,u.LastName,u.Email,u.CompanyName,u.CreateTime from dbo.T_Sys_User u where IsInstructor=1 and IsEnabled=1 30 and DomainID in(31 select ShareDomainID from dbo.T_Sys_Share s where DomainID='{0}' and ShareType=132 and s.ShareDomainID in (select DomainID from T_Sys_Domain where Enable=1)) 33 and LoginName like {1}34 order by LoginName ", domainId, name);35 // ds = dbfac.ExecuteSelectSql(sql);36 37 List param = new List ();38 SqlParameter sqlstr = new SqlParameter();39 sqlstr.Direction = ParameterDirection.Input;40 sqlstr.SqlDbType = SqlDbType.NVarChar;41 sqlstr.Value = sql.ToString();42 sqlstr.ParameterName = "@SqlSelStr";43 44 param.Add(new SqlParameter("@pageIndex", pageIndex));45 param.Add(new SqlParameter("@pageSize", pageSize));46 47 SqlParameter output = new SqlParameter();48 output.ParameterName = "@counNum";49 output.SqlDbType = SqlDbType.Int;50 output.Direction = ParameterDirection.Output;51 param.Add(output);52 param.Add(sqlstr);53 DataSet ds = null;54 using (DBFactory dbfac = new DBFactory())55 {56 ds = dbfac.ExecuteSelectSql57 ("exec [P_SelectInstructor] @pageIndex,@pageSize,@SqlSelStr,@counNum output",58 param.ToArray());59 count = int.Parse(output.Value.ToString());60 }61 62 if (ds != null && ds.Tables.Count > 0)63 {64 return ds.Tables[0];65 }66 else67 {68 return null;69 }70 }