C#中执行存储过程并在SQL server中调试

1.编写存储过程

 1 if exists (select * from sysobjects where id = object_id(N'[sp_calcPci_of_baseRcd_GTmpTbl]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 2      drop PROCEDURE [dbo].[sp_calcPci_of_baseRcd_GTmpTbl]
 3 
 4 CREATE PROCEDURE [dbo].[sp_calcPci_of_baseRcd_GTmpTbl]
 5     --定义参数
 6     @bdindexId INT ,    
 7     @groupId INT,    
 8     @disVal INT ,    
 9     @retVal INT OUTPUT,  -- 返回值  
10     @routeType INT  
11                             
12 --WITH ENCRYPTION -- 加密
13 AS
14     BEGIN                
15         DECLARE @pciCalcRcdId0 INT
16         --执行另外一个存储过程,输出参数需带上关键字‘OUTPUT’
17         EXEC dbo.procGetCurNo 'PCA_T_D_PCICalcRcd', @pciCalcRcdId0 OUTPUT
18         IF( @pciCalcRcdId0 <= 0 )
19             BEGIN--sql中没有{}，以BEGIN、END代替
20                 SET @retVal = -5
21                 RETURN -5;
22             END
23 
24         SET @curSegIndex = 1;    --变量赋值     
25         --为查询结果定义游标     
26         DECLARE segListCursor CURSOR      
27         FOR
28             SELECT section_code ,pav_type ,segment_code ,begin_mile ,end_mile ,segment_length, lane_width
29             FROM    dbo.ZY_T_D_Segment
30             WHERE   route_code = @routeCode AND up_down = @direction AND lane = @lane
31                     AND begin_mile < @endMile AND end_mile > @beginMile AND valid_flag=1
32             ORDER BY begin_mile;
33         --打开游标
34         OPEN segListCursor;
35         --按行取值
36         FETCH NEXT FROM segListCursor INTO @sectionCode, @pavType,
37             @segmentCode, @segBeginMile, @segEndMile, @segLength, @laneWidth;
38         WHILE @@FETCH_STATUS = 0
39             BEGIN    -- 
40                 ---处理---              
41                 FETCH NEXT FROM segListCursor INTO @sectionCode,
42                     @pavType, @segmentCode, @segBeginMile, @segEndMile,@segLength, @laneWidth;
43             END;
44         CLOSE segListCursor;--关闭游标
45         DEALLOCATE segListCursor;--释放游标 deallocate
46 
47         -- 将临时表中的数据插入到实际表a_test中        
48         SET @insertSql = 'INSERT INTO ' + @pavDataLocTable + ' SELECT * FROM ' + @tempPavDataLocTable
49         EXEC sys.sp_executesql @insertSql
50         SET @insertSql = 'INSERT INTO ' + @pciCalcTable + ' SELECT * FROM ' + @tempPciCalcTable        
51         EXEC sys.sp_executesql @insertSql
52         
53         DROP table [dbo].[##pavDataLoc_temp_table]
54         DROP table [dbo].[##pciCalc_temp_table]        
55     END;
56 GO

 

2.C#中调用存储过程

 1 SqlParameter dmgPara1 = new SqlParameter("@bdindexId", SqlDbType.Int, 4);
 2             dmgPara1.Value = pciCalcRcd.BdindexId;
 3             SqlParameter dmgPara2 = new SqlParameter("@groupId", SqlDbType.Int, 4);
 4             dmgPara2.Value = pciCalcRcd.GroupId;
 5             SqlParameter dmgPara3 = new SqlParameter("@disVal", SqlDbType.Int, 4);
 6             dmgPara3.Value = pciCalcRcd.DisVal;
 7             SqlParameter dmgPara4 = new SqlParameter("@retVal", SqlDbType.Int, 4);
 8             dmgPara4.Value = 0;
 9             dmgPara4.Direction = ParameterDirection.Output;//输出参数
10             SqlParameter dmgPara5 = new SqlParameter("@routeType", SqlDbType.Int, 4);
11             dmgPara5.Value = routeTypeVal;//道路等级，高速与一级公路为0，二三四级为1
12             SqlParameter[] sqlDmgParam = new SqlParameter[5];
13             sqlDmgParam[0] = dmgPara1;
14             sqlDmgParam[1] = dmgPara2;
15             sqlDmgParam[2] = dmgPara3;
16             sqlDmgParam[3] = dmgPara4;
17             sqlDmgParam[4] = dmgPara5;
18             // 根据病害，入库相应的路面破损数据
19             try
20             {
21                 nRet = MsSqlTool.executeProcedureNoDataSet(conn, "sp_calcPci_of_baseRcd_GTmpTbl", sqlDmgParam);
22             }
23             catch (Exception ex)
24             {
25             }
26             if (int.Parse(sqlDmgParam[3].Value.ToString()) <= 0)
27             {
28                 return int.Parse(sqlDmgParam[3].Value.ToString());//获取输出参数返回值
29             }

 1 public static int executeProcedureNoDataSet(SqlConnection dbConn, string strProcName, SqlParameter[] procParams)
 2         {
 3             SqlConnection conn = dbConn;
 4             DataSet ds = new DataSet();
 5             try
 6             {
 7                 if (conn.State != ConnectionState.Open)
 8                     conn.Open();
 9 
10                 // 确认打开连接  Open();  
11                 SqlCommand cmd = new SqlCommand(strProcName, dbConn);  
12                 cmd.CommandType = CommandType.StoredProcedure;
13                 cmd.CommandTimeout = 3600;
14                 // 依次把参数传入存储过程 
15                 if (procParams != null)  
16                 {  
17                     foreach (SqlParameter parameter in procParams)  
18                         cmd.Parameters.Add(parameter);  
19                 }   
20                
21                 cmd.ExecuteNonQuery();
22 
23                 return 1;
24             }
25             catch (System.Exception ex)
26             {
27                 throw ex;
28             }
29             finally
30             {
31                 conn.Close();
32             }
33             return 1;
34         }

3.SQL 调试存储过程

 

转载于:https://www.cnblogs.com/lyd2016/p/6610260.html