如何在.sql文件中执行上一行时在sql中运行下一行 [英] How to run the next line in sql when the previous line is executed in a .sql file

问题描述

Hi


当在运行时使用C＃执行.sql文件时，如果在.sql文件中上一行失败时运行sql中的下一行



我写过这样的sql脚本

Hi
How to run the next line in sql when the previous line is in failure in a .sql file while Executing the .sql file in runtime using C#

I have written sql script like this

ALTER TABLE [dbo].[STOCKDETAILS] ADD  Constraint DF_STOCKDETAILS_QTY_IN DEFAULT ((0)) FOR [QTY_IN]
GO

ALTER TABLE [dbo].[STOCKDETAILS] ADD Constraint DF_STOCKDETAILS_QTY_OUT DEFAULT ((0)) FOR [QTY_OUT]
GO

ALTER TABLE [dbo].[STOCKDETAILS] ADD Constraint DF_STOCKDETAILS_UNIT_COST DEFAULT ((0)) FOR [UNIT_COST]
GO

ALTER TABLE [dbo].[STOCKDETAILS] ADD Constraint DF_STOCKDETAILS_AMT_IN DEFAULT ((0)) FOR [AMT_IN]
GO

ALTER TABLE [dbo].[STOCKDETAILS] ADD Constraint DF_STOCKDETAILS_AMT_OUT DEFAULT ((0)) FOR [AMT_OUT]
GO

ALTER TABLE [dbo].[STOCKDETAILS] ADD Constraint DF_STOCKDETAILS_ID DEFAULT ((0)) FOR [ID]
GO

ALTER TABLE [dbo].[STOCKDETAILS] ADD Constraint DF_STOCKDETAILS_BATCH_NO DEFAULT ((0)) FOR [BATCH_NO]
GO

ALTER TABLE [dbo].[PRODUCT] ADD Constraint DF_PRODUCT_SERIALIZED DEFAULT ((0)) FOR [SERIALIZED]
GO

--------------------------如果等级表包含数据/如果在转换时发生错误，则运行此脚本ABOUVE SRIPT ---------

ALTER TABLE GradedDETAILS ADD STOCK_DATE1 DATETIME NULL ---- STEP 1--



ALTER TABLE GradedDETAILS ALTER COLUMN [STOCK_DATE] NVARCHAR（100）----第2步 -



UPDATE GradedDETAILS SET [STOCK_DATE] = CONVERT（DATETIME，[STOCK_DATE] ]，104）----第3步 -



UPDATE GradedDETAILS SET [STOCK_DATE1] = [STOCK_DATE] ----第4步 -



ALTER TABLE GradedDETAILS DROP COLUMN [STOCK_DATE] ----第5步 -



SP_RENAME'GradededDETAILS.STOCK_DATE1'，'STOCK_DATE'，'COLUMN'----第6步 -



我想在sql中使用C＃逐步执行查询，只需单击



如果你执行一次然后我得到这样的错误



'SP_RENAME'附近的语法不正确。


当一行失败时
由于该错误导致总执行失败



我只需点击一下即可运行更多脚本。请你解决这个错误

--------------------------RUN THIS SCRIPT IF GRADEDDETAILS TABLE CONTAINS DATA / IF ERROR OCCURS WHILE CONVERTING THE ABOUVE SRIPT---------
ALTER TABLE GradedDETAILS ADD STOCK_DATE1 DATETIME NULL ----STEP 1--

ALTER TABLE GradedDETAILS ALTER COLUMN [STOCK_DATE] NVARCHAR(100) ----STEP 2--

UPDATE GradedDETAILS SET [STOCK_DATE]=CONVERT(DATETIME, [STOCK_DATE], 104) ----STEP 3--

UPDATE GradedDETAILS SET [STOCK_DATE1]=[STOCK_DATE] ----STEP 4--

ALTER TABLE GradedDETAILS DROP COLUMN [STOCK_DATE] ----STEP 5--

SP_RENAME 'GradedDETAILS.STOCK_DATE1','STOCK_DATE','COLUMN' ----STEP 6--

I want to execute query step by step in sql using C# with single click

if you execute it once then i got error like this

Incorrect syntax near 'SP_RENAME'.

when one line is failed total execution fails due to that error

I have more scripts to run in a single click. would u please resolve this error

推荐答案

你不能忽略错误并继续 - 但你可以使用 TRY ... CATCH 阻止每个ALTER语句。使用SELECT报告问题（这样你就可以在你的代码中处理它），然后转到下一个TRY ... ALTER ... CATCH块。

https://msdn.microsoft.com/en-GB/library/ms175976.aspx [ ^ ]

You can't ignore an error and continue - but you could use a TRY...CATCH block round each ALTER statement. Use a SELECT to report a problem (so you can deal with it in your code) and then move on to the next TRY...ALTER...CATCH block.
https://msdn.microsoft.com/en-GB/library/ms175976.aspx[^]

你可以直接在C＃中使用TRY ... CATCH，如果其中一个查询失败，则捕获catch中的异常，并且不要在catch中提及任何内容。

You can directly use TRY... CATCH in C# and if one of the query is failed then catch the exception in catch and don't mention anything in the catch.

这篇关于如何在.sql文件中执行上一行时在sql中运行下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！