C＃中的Ms sqlserver存储过程问题

本文介绍了C＃中的Ms sqlserver存储过程问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我在Ms SQL server中编写了一个存储过程。在那个过程中，我使用了一些try catch块。如果我从SQL服务器管理工​​作室运行该存储过程，它运行正常。如果首先尝试阻止结果错误，它将转到其他块。但我从c＃调用的那个程序。在该存储过程结果错误的c＃第一个块中，它不会进入该存储过程的下一个块。简单就是显示错误。如何解决 我的尝试： 我用Google搜索解决方案但无法找到合适的解决方案 USE [inventoryDB] GO / * * ****对象：StoredProcedure [dbo]。[clearinv]脚本日期：05-10-2018 18:42:11 ****** / GO GO ALTER PROCEDURE [dbo]。[SPNULL] （ @ errorstat AS INT = 0 ） AS WHILE @ errorstat = 0 BEGIN BEGIN TRY UPDATE suppDB SET ldgrid = 0 WHERE ldgrid IS NULL ALTER TABLE suppDB ALTER COLUMN [ldgrid] INTEGER NOT NULL ALTER TABLE suppDB ADD CONSTRAINT sldgrid DEFAULT 0 FOR ldgridif @@ ERROR <> 0 或 @@ ERROR = 0 BEGIN PRINT ' 0' END END TRY BEGIN CATCH PRINT ' 1' END CATCH END GO DECLARE @ errorstat AS INT = 0 WHILE @ errorstat = 0 BEGIN BEGIN TRY 更新 suppDB1718 SET ldgrid = 0 WHERE ldgrid IS NULL ALTER 表 suppDB1718 ALTER COLUMN [ldgrid] INTEGER NOT NULL ALTER 表 suppDB1718 ADD CONSTRAINT sldgrid1718 DEFAULT 0 FOR ldgridif @@ ERROR <> 0 或 @@ ERROR = 0 BEGIN PRINT ' 0' END END TRY BEGIN CATCH PRINT ' 2' END CATCH END GO IF @@ ERROR <> 0 OR @@ ERROR = 0 DECLARE @ errorstat AS INT = 0 BEGIN 尝试 更新 suppDB1819 SET ldgrid = 0 WHERE ldgrid IS NULL ALTER TABLE suppDB1819 ALTER COLUMN [ldgrid] INTEGER NOT NULL ALTER 表 suppDB1819 ADD CONSTRAINT sldgrid1819 DEFAULT 0 FOR ldgrid END 尝试 BEGIN CATCH PRINT ' 3' END CATCH GO IF @@ ERROR <> 0 OR @@ ERROR = 0 DECLARE @ errorstat AS INT = 0 IF @ errorstat = 0 BEGIN TRY UPDATE suppDB SET jvno = 0 WHERE jvno IS NULL ALTER TABLE suppDB ALTER COLUMN [jvno] INTEGER NOT NULL ALTER TABLE suppDB ADD CONSTRAINT sjvno DEFAULT 0 FOR jvno END 尝试 BEGIN CATCH PRINT ' 4' END CATCH GO IF @@ ERROR <> 0 OR @@ ERROR = 0 DECLARE @ errorstat AS INT = 0 IF @ errorstat = 0 BEGIN TRY 更新 suppDB1718 SET jvno = 0 WHERE jvno IS NULL ALTER 表 suppDB1718 ALTER COLUMN [jvno] INTEGER NOT NULL ALTER 表 suppDB1718 ADD CONSTRAINT sjvno1718 DEFAULT 0 FOR jvno END TRY BEGIN CATCH PRINT ' 5' 结束 CATCH GO IF @@ ERROR <> 0 或 @@ ERROR = 0 DECLARE @ errorstat AS INT = 0 IF @ errorstat = 0 BEGIN TRY UPDATE suppDB1819 SET jvno = 0 WHERE jvno IS NULL ALTER 表 suppDB1819 ALTER COLUMN [jvno] INTEGER NOT NULL ALTER 表 suppDB1819 ADD CONSTRAINT sjvno1819 DEFAULT 0 FOR jvno END TRY BEGIN CATCH PRINT ' 6' 结束 CATCH GO 解决方案 0）允许异常发生以实现某种任意形式的错误处理只是一种不好的做法。例外通常应保留用于意外问题。 1）如果不需要，重复执行流程是没有意义的。 2）如果数据库中已存在数据，更改列的类型将导致数据被删除，因此我将该代码删除了。 试试这段代码： - - 如果尚未设置默认值 IF （ SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' suppDB' AND COLUMN_NAME = ldgrid'） IS NULL ALTER 表 suppDB ADD C. ONSTRAINT DF_suppDB_ldgrid DEFAULT 0 FOR ldgrid; IF （ SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' suppDB1718' AND COLUMN_NAME = ' ldgrid' ） IS NULL ALTER 表 suppDB1718 ADD CONSTRAINT DF_suppDB1718_ldgrid DEFAULT 0 FOR ldgrid; IF （ SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' suppDB1819' AND COLUMN_NAME = ' ldgrid' ） IS NULL ALTER 表 suppDB1819 ADD CONSTRAINT DF_suppDB1819_ldgrid DEFAULT 0 FOR ldgrid; IF （ SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' suppDB' AND COLUMN_NAME = ' jvno' ） IS NULL ALTER 表 suppDB ADD CONSTRAINT DF_suppDB_jvno DEFAULT 0 FOR jvno; IF （ SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' suppDB1718' AND COLUMN_NAME = ' jvno' ） IS NULL ALTER 表 suppDB1718 ADD CONSTRAINT DF_suppDB1718_jvno DEFAULT 0 FOR jvno; IF （ SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' suppDB1819' AND COLUMN_NAME = ' jvno' ） IS NULL ALTER 表 suppDB1819 ADD CONSTRAINT DF_suppDB1819_jvno DEFAULT 0 FOR jvno; 更新 suppDB SET ldgrid = 0 WHERE ldgrid IS NULL ; 更新 suppDB1718 SET ldgrid = 0 WHERE ldgrid IS NULL ; 更新 suppDB1819 SET ldgrid = 0 WHERE ldgrid IS NULL ; 更新 suppDB SET jvno = 0 WHERE jvno IS NULL ; 更新 suppDB1718 SET jvno = 0 WHERE jvno IS NULL ; 更新 suppDB1819 SET jvno = 0 WHERE jvno IS NULL ; I Wrote one store procedure in Ms SQL server. In that procedure I used some try catch blocks. If I run that store procedure from SQL server Management studio ,it is running fine.If first try block result error, it is going to other block. But That procedure I am calling from c#. In c# first block of that store procedure result error then it is not going next block of that store procedure. Simply It is showing error. How to solve thatWhat I have tried:I googled for solution but unable to find right oneUSE [inventoryDB]GO/****** Object: StoredProcedure [dbo].[clearinv] Script Date: 05-10-2018 18:42:11 ******/GOGOALTER PROCEDURE [dbo].[SPNULL]( @errorstat AS INT = 0)ASWHILE @errorstat = 0BEGINBEGIN TRYUPDATE suppDB SET ldgrid = 0 WHERE ldgrid IS NULLALTER TABLE suppDB ALTER COLUMN [ldgrid] INTEGER NOT NULLALTER TABLE suppDB ADD CONSTRAINT sldgrid DEFAULT 0 FOR ldgridif @@ERROR <> 0 OR @@ERROR = 0BEGIN PRINT '0'ENDEND TRYBEGIN CATCHPRINT '1'END CATCHENDGODECLARE @errorstat AS INT = 0WHILE @errorstat = 0BEGINBEGIN TRYUPDATE suppDB1718 SET ldgrid = 0 WHERE ldgrid IS NULLALTER TABLE suppDB1718 ALTER COLUMN [ldgrid] INTEGER NOT NULLALTER TABLE suppDB1718 ADD CONSTRAINT sldgrid1718 DEFAULT 0 FOR ldgridif @@ERROR <> 0 OR @@ERROR = 0BEGIN PRINT '0'ENDEND TRYBEGIN CATCHPRINT '2'END CATCHENDGOIF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0BEGIN tryUPDATE suppDB1819 SET ldgrid = 0 WHERE ldgrid IS NULLALTER TABLE suppDB1819 ALTER COLUMN [ldgrid] INTEGER NOT NULLALTER TABLE suppDB1819 ADD CONSTRAINT sldgrid1819 DEFAULT 0 FOR ldgridEND tryBEGIN CATCHPRINT '3'END CATCHGOIF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0IF @errorstat = 0BEGIN TRYUPDATE suppDB SET jvno = 0 WHERE jvno IS NULLALTER TABLE suppDB ALTER COLUMN [jvno] INTEGER NOT NULLALTER TABLE suppDB ADD CONSTRAINT sjvno DEFAULT 0 FOR jvnoEND tryBEGIN CATCHPRINT '4'END CATCHGOIF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0IF @errorstat = 0BEGIN TRYUPDATE suppDB1718 SET jvno = 0 WHERE jvno IS NULLALTER TABLE suppDB1718 ALTER COLUMN [jvno] INTEGER NOT NULLALTER TABLE suppDB1718 ADD CONSTRAINT sjvno1718 DEFAULT 0 FOR jvnoEND TRYBEGIN CATCHPRINT '5'END CATCHGOIF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0IF @errorstat = 0BEGIN TRYUPDATE suppDB1819 SET jvno = 0 WHERE jvno IS NULLALTER TABLE suppDB1819 ALTER COLUMN [jvno] INTEGER NOT NULLALTER TABLE suppDB1819 ADD CONSTRAINT sjvno1819 DEFAULT 0 FOR jvnoEND TRYBEGIN CATCHPRINT '6'END CATCHGO 解决方案 0) Allowing an exception to happen to implement some arbitrary form of error handling is just plain bad practice. Exceptions should generally be reserved for unexpected problems.1) There's no point in repeatedly performing a process if it doesn't need to be done. 2) If there's already data in the database, altering a column's type will cause the data to be removed, so I took that code out.Try this code:--if the default value has not been setIF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB' AND COLUMN_NAME = 'ldgrid') IS NULL ALTER TABLE suppDB ADD CONSTRAINT DF_suppDB_ldgrid DEFAULT 0 FOR ldgrid;IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1718' AND COLUMN_NAME = 'ldgrid') IS NULL ALTER TABLE suppDB1718 ADD CONSTRAINT DF_suppDB1718_ldgrid DEFAULT 0 FOR ldgrid;IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1819' AND COLUMN_NAME = 'ldgrid') IS NULL ALTER TABLE suppDB1819 ADD CONSTRAINT DF_suppDB1819_ldgrid DEFAULT 0 FOR ldgrid;IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB' AND COLUMN_NAME = 'jvno') IS NULL ALTER TABLE suppDB ADD CONSTRAINT DF_suppDB_jvno DEFAULT 0 FOR jvno;IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1718' AND COLUMN_NAME = 'jvno') IS NULL ALTER TABLE suppDB1718 ADD CONSTRAINT DF_suppDB1718_jvno DEFAULT 0 FOR jvno;IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1819' AND COLUMN_NAME = 'jvno') IS NULL ALTER TABLE suppDB1819 ADD CONSTRAINT DF_suppDB1819_jvno DEFAULT 0 FOR jvno;UPDATE suppDB SET ldgrid = 0 WHERE ldgrid IS NULL;UPDATE suppDB1718 SET ldgrid = 0 WHERE ldgrid IS NULL;UPDATE suppDB1819 SET ldgrid = 0 WHERE ldgrid IS NULL;UPDATE suppDB SET jvno = 0 WHERE jvno IS NULL;UPDATE suppDB1718 SET jvno = 0 WHERE jvno IS NULL;UPDATE suppDB1819 SET jvno = 0 WHERE jvno IS NULL; 这篇关于C＃中的Ms sqlserver存储过程问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！