使用存储过程将 DataTable 值插入或更新到数据库表中

2021-09-10 00:00:00 tsql sql-server stored-procedures

我有一个表 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

相关文章