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 调试存储过程