How to Make a Stored Procedure Return a "Dataset" Using a Parameter I Pass

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

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_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

Stored procedure return into DataSet in C# .Net

Try this

    DataSet ds = new DataSet("TimeRanges");
    using(SqlConnection conn = new SqlConnection("ConnectionString"))
    {               
            SqlCommand sqlComm = new SqlCommand("Procedure1", conn);               
            sqlComm.Parameters.AddWithValue("@Start", StartTime);
            sqlComm.Parameters.AddWithValue("@Finish", FinishTime);
            sqlComm.Parameters.AddWithValue("@TimeRange", TimeRange);

            sqlComm.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = sqlComm;

            da.Fill(ds);
     }

How to return the output of stored procedure into a variable in sql server

That depends on the nature of the information you want to return.

If it is a single integer value, you can use the return statement

 create proc myproc
 as 
 begin
     return 1
 end
 go
 declare @i int
 exec @i = myproc

If you have a non integer value, or a number of scalar values, you can use output parameters

create proc myproc
  @a int output,
  @b varchar(50) output
as
begin
  select @a = 1, @b='hello'
end
go
declare @i int, @j varchar(50)
exec myproc @i output, @j output

If you want to return a dataset, you can use insert exec

create proc myproc
as 
begin
     select name from sysobjects
end
go

declare @t table (name varchar(100))
insert @t (name)
exec myproc

You can even return a cursor but that's just horrid so I shan't give an example :)

Return multiple values (OUTPUT) stored procedure to dataset or datatable?

You should retrieve output parameters AFTER finisted SQL DataReader stream. That means you couldn't get the output parameters until you read all the rows from the DataReader. So, if you want to set output values to all rows (I don't recommend it, It would causes redundant data), you can do it like this;

        foreach (DataTable datasetTable in dataset.Tables)
        {
            datasetTable.Columns.Add("Value1", typeof(string));
            datasetTable.Columns.Add("Value2", typeof(string));
            datasetTable.Columns.Add("Value3", typeof(string));
            datasetTable.Columns.Add("Value4", typeof(string));
            foreach (DataRow datasetTableRow in datasetTable.Rows)
            {
                datasetTableRow["Value1"] = command.Parameters["@Value1"].Value;
                datasetTableRow["Value2"] = command.Parameters["@Value2"].Value;
                datasetTableRow["Value3"] = command.Parameters["@Value3"].Value;
                datasetTableRow["Value4"] = command.Parameters["@Value4"].Value;
            }
        }

Or you can create another table for outputvalues like this;

    dataset.Tables.Add("OutputResults");
    dataset.Tables["OutputResults"].Columns.Add("Value1", typeof(string));
    dataset.Tables["OutputResults"].Columns.Add("Value2", typeof(string));
    dataset.Tables["OutputResults"].Columns.Add("Value3", typeof(string));
    dataset.Tables["OutputResults"].Columns.Add("Value4", typeof(string));
    var outputRow = dataset.Tables["OutputResults"].NewRow();
    outputRow["Value1"] = command.Parameters["@Value1"].Value;
    outputRow["Value2"] = command.Parameters["@Value2"].Value;
    outputRow["Value3"] = command.Parameters["@Value3"].Value;
    outputRow["Value4"] = command.Parameters["@Value4"].Value;
    dataset.Tables["OutputResults"].Rows.Add(outputRow);

Another option is you can add the rows as key-value pair for per output values in created table like this;

    dataset.Tables.Add("OutputResults");
    dataset.Tables["OutputResults"].Columns.Add("OutputName", typeof(string));
    dataset.Tables["OutputResults"].Columns.Add("OutputValue", typeof(string));

    var value1Row = dataset.Tables["OutputResults"].NewRow();
    value1Row["OutputName"] = "Value1";
    value1Row["OutputValue"] = command.Parameters["@Value1"].Value;

    var value2Row = dataset.Tables["OutputResults"].NewRow();
    value2Row["OutputName"] = "Value2";
    value2Row["OutputValue"] = command.Parameters["@Value2"].Value;

    var value3Row = dataset.Tables["OutputResults"].NewRow();
    value3Row["OutputName"] = "Value3";
    value3Row["OutputValue"] = command.Parameters["@Value3"].Value;

    var value4Row = dataset.Tables["OutputResults"].NewRow();
    value4Row["OutputName"] = "Value4";
    value4Row["OutputValue"] = command.Parameters["@Value4"].Value;

    dataset.Tables["OutputResults"].Rows.Add(value1Row);
    dataset.Tables["OutputResults"].Rows.Add(value2Row);
    dataset.Tables["OutputResults"].Rows.Add(value3Row);
    dataset.Tables["OutputResults"].Rows.Add(value4Row);

SQL server stored procedure return a table

A procedure can't return a table as such. However you can select from a table in a procedure and direct it into a table (or table variable) like this:

create procedure p_x
as
begin
declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t values('a', 1,1,1)
insert @t values('b', 2,2,2)

select * from @t
end
go

declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t
exec p_x

select * from @t

How to run a Stored Procedure which returns a dataset

Just change this line from

 Dim ds As DataSet = Nothing

to

 Dim ds = new DataSet()

You need to pass an initialized DataSet to the SqlDataAdapter.Fill method.

Actually your code is like

 sa.Fill(Nothing) 

and of course this is not appreciated by the Fill code.

---

Related Topics