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

Question

I am very new to stored procedures.

Say I have an IDCategory (int) and I pass this to a stored procedure. In normal mode, the conversation:

Find all listings with IDCategory, equal to IDC category, I have what you need to find.

So, he will find 3 lists and create a table with columns:

IDListing, IDCategory, Price, Seller, Image.

How could I achieve this?

Answer 1

To populate the data set from the stored procedure, you will have the following code:

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);

, , ... , . . , . .

:

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

: http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

DataSets ADO.Net: http://authors.aspalliance.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx

Answer 2

, 5 .

:

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

Answer 3

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 [database_name] with the name of your database. The advantage of using the two-period format is that sproc will return results while the user running sproc has access to the table (and database).

@categoryid is used to work with the SQL Servers parameter sniff problem .