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

Question

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:

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

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

IDListing, IDCategory, Price, Seller, Image.

How could I achieve this?

Answer 1

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

Example:

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

Answer 2

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

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

Answer 3

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.

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%5Fprocedures%5Fbasics%5Fp1.aspx

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