将c＃DataTable作为参数传递到MS SQL Server 2008中的存储过程 [英] Passing c# DataTable as a parameter to stored procedure in MS SQL Server 2008

问题描述

我要将 DataDable 传递到存储过程作为参数，表示以下列：

I want to pass a DataDable to a stored procedure as parameter cointaning the columns below:

Supp_Id          int
Del_Methode_Id   int
Ord_Ammount      int
Promo_Id         int
Discount_Ammount Money

现在我想在存储过程中使用这个datatable，并且想在其上声明一个游标。

Now I want to use this datatable in stored procedure and want to declare a cursor on it. And use that cursor to insert values into the database table sequentially.

请告诉我如何在存储过程中声明datatable参数，然后使用<

Please tell me how to declare datatable parameter in stored procedure and then using cursor on that parameter ?

推荐答案

首先，您需要创建一个类型：

First you need to create a type:

CREATE TYPE dbo.whatever AS TABLE
(
  Supp_Id          int, 
  Del_Methode_Id   int,
  Ord_Amount       int,
  Promo_Id         int,
  Discount_Amount  Money
);

现在您的存储过程可以声明为只读输入参数：

Now your stored procedure can declare this as a read only input parameter:

CREATE PROCEDURE dbo.do_whatever
  @datatable dbo.whatever READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.destination_table(column_list)
    SELECT column_list FROM @datatable;
END
GO

为什么要在这里使用游标，你需要一个，我不知道。如果你认为这将是有用的，你可以在 INSERT ... SELECT 中添加一个 ORDER BY 是有意义的顺序），否则如果你真的想要一个游标在这里，你应该能够声明一个 @datatable ，就像对任何其他表一样。

Why you want to use a cursor here, or think you need one, I'm not sure. You can add an ORDER BY clause to the INSERT...SELECT if you think that will be useful (and there is something meaningful to order by), but otherwise if you really really want a cursor here you should be able to declare one against @datatable just as you would for any other table.

这篇关于将c＃DataTable作为参数传递到MS SQL Server 2008中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！