問題描述

我有一個表 Cars 如下:

此表將在 C# 應用程序的網格中讀取.應用程序可以編輯或刪除或添加新行.更新后的網格將被作為參數傳遞給存儲過程.

This table will be read in grid of a C# application. The application can either edit OR delete OR add a new row. The updated grid will be take in a datatable as be passed as a parameter to the Stored Procedure.

我想開發一個以 @typeCars 作為輸入的存儲過程.

I want to develop a stored procedure which takes @typeCars as input.

現在應該更新 Cars 表中已編輯的列，應該刪除已刪除的列并添加新列.

Now the edited columns should be updated in the Cars table, deleted columns should be deleted and new columns should be added.

請告訴我如何在存儲過程中實現這一點.

Please let me know how to achieve this in a stored procedure.

我的嘗試:

CREATE PROC sp_updateDataTable (@dtTypeCars dtTypeCars READONLY)
AS 
BEGIN
   BEGIN TRY
      DECLARE @dbMaxCarIdval INT;
      DECLARE @typeCurrentCarIdVal INT;

      SELECT TOP 1 @dbMaxCarIdval = id FROM Cars ORDER BY id DESC

      IF (@dbMaxCarIdval >= (SELECT id FROM @dtTypeCars))
      BEGIN
          UPDATE Cars 
          SET Model = (SELECT Model FROM @dtTypeCars), 
              Company = (SELECT Company FROM @dtTypeCars)
          WHERE id = (SELECT id FROM @dtTypeCars);
      END
 END TRY
 BEGIN CATCH
     PRINT 'I am in Catch';
 END CATCH
END

請注意 dtTypeCars 是一個 TYPE 如下

Please note dtTypeCars is a TYPE as follows

CREATE TYPE dtTypeCars AS TABLE
(
 id INT NOT NULL,
 Model varchar(50),
 Company varchar(50)
)

推薦答案

使用Merge語句更新、插入和刪除數據http://technet.microsoft.com/en-在/library/bb522522(v=sql.105).aspx

Use Merge statement to update,insert and delete the data http://technet.microsoft.com/en-in/library/bb522522(v=sql.105).aspx

這篇關于使用存儲過程將 DataTable 值插入或更新到數據庫表中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！