存储过程的创建与使用

sqlserver上创建存储过程案例：

CREATE proc [dbo].[proc_GetAllStructureIdByStructureId] @Com_StructureId uniqueidentifier  -----@Com_StructureId传入参数
AS
BEGIN
select a.*,c.Type into #temp from Com_Structure a inner join Com_StructureType c on a.Com_SructureTypeID=c.Com_StructureTypeID where c.Type=1;
with cte as
(
select Com_StructureId from #temp where Com_StructureID=@Com_StructureId
union all
select a.Com_StructureId from #temp as a,cte as b where a.ParentStructureID=b.Com_StructureID
)
select * from cte
END

GO

 

exec proc_GetAllStructureIdByStructureId  '7E049D23-E4C4-40BD-9EE4-9222EB097A19'  -----执行存储过程

 

//在C#中使用存储过程

List<Guid> StructureIdList = new List<Guid>();
var dr = DataAccess.LepulsDB.DBContext.StoredProcedure("proc_GetAllStructureIdByStructureId").AddInputParameter("@Com_StructureId", System.Data.DbType.Guid, nodeId).ToDataReader();
while (dr.Read())
{
StructureIdList.Add(Guid.Parse(dr[0].ToString()));
}
dr.Close();