SQL 如何使用我传递的参数使存储过程返回“数据集”?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1344697/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 03:22:27  来源:igfitidea点击:

How can I make a stored procedure return a "dataset" using a parameter I pass?

sqltsql

提问by Sergio Tapia

I'm very new to Stored Procedures.

我对存储过程很陌生。

Say I have a IDCategory (int) and I pass that to a stored procedure. In normal-talk, it'd go:

假设我有一个 IDcategory (int) 并且我将它传递给一个存储过程。在正常谈话中,它会去:

Find me all the listings with an IDCategory equal to the IDCategory I'm telling you to find.

为我查找 IDcategory 等于我要您查找的 IDcategory 的所有列表。

So it would find say 3 listing, and create a table with columns:

所以它会找到 say 3 列表,并创建一个包含列的表:

IDListing, IDCategory, Price, Seller, Image.

IDListing、IDcategory、价格、卖家、图片。

How could I achieve this?

我怎么能做到这一点?

采纳答案by Kevin LaBranche

To fill a dataset from a stored procedure you would have code like below:

要从存储过程填充数据集,您将使用如下代码:

SqlConnection mySqlConnection =new SqlConnection("server=(local);database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText = "IDCategory";
    mySqlCommand.CommandType = CommandType.StoredProcedure;
    mySqlCommand.Parameters.Add("@IDCategory", SqlDbType.Int).Value = 5;

    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    mySqlDataAdapter.Fill(myDataSet);

Your connection string will be different and there are a few different ways to do this but this should get you going.... Once you get a few of these under your belt take a look at the Using Statement. It helps clean up the resources and requires a few less lines of code. This assumes a Stored Procedure name IDCategory with one Parameter called the same. It may be a little different in your setup.

您的连接字符串会有所不同,并且有几种不同的方法可以做到这一点,但这应该能让您继续前进……一旦您掌握了其中的一些,请查看 Using 语句。它有助于清理资源并需要更少的代码行。这假定存储过程名称 IDcategory 具有一个相同的参数。您的设置可能略有不同。

Your stored procedure in this case will look something like:

在这种情况下,您的存储过程将类似于:

CREATE PROC [dbo].[IDCategory] 
    @IDCategory int
AS 
    SELECT IDListing, IDCategory, Price, Seller, Image
         FROM whateveryourtableisnamed
         WHERE IDCategory = @IDCategory

Here's a link on Stored Procedure basics: http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

这是存储过程基础知识的链接:http: //www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

Here's a link on DataSets and other items with ADO.Net: http://authors.aspalliance.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx

这是有关 ADO.Net 的数据集和其他项目的链接:http: //authors.aspalliance.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx

回答by Dan Appleyard

Have a table in your database that contains those 5 fields you wish and query it.

在您的数据库中有一个包含您希望的 5 个字段的表并对其进行查询。

Example:

例子:

Select IDListing, IDCategory, Price, Seller, Image
From [listingtable] --whatever your table is called
where IDCategoryID = @IDCategoryID

回答by OMG Ponies

Entire stored procedure:

整个存储过程:

CREATE PROCEDURE sp_Listing_Get
  @IDCategory int
AS

  DECLARE @categoryid
      SET @categoryid = @IDCategory

BEGIN

   SELECT t.idlisting,
          t.idcategory,
          t.price,
          t.seller,
          t.image
     FROM [databaseName].dbo.LISTING t
    WHERE t.idcategoryid = @categoryid

END

Replace [databaseName] with the name of your database. The benefit to using the two period format is that the sproc will return results as long as the user who is executing the sproc has access to the table (and database).

将 [databaseName] 替换为您的数据库名称。使用两个句点格式的好处是只要执行 sproc 的用户有权访问表(和数据库),sproc 就会返回结果。

The @categoryid is used to deal with SQL Servers parameter sniffing issue.

@categoryid 用于处理 SQL Servers 参数嗅探问题