how to write stored procedure for making table data in to dataset

I am new to SQL Server.

Can anyone help me in getting table data back as a dataset, using a Stored Procedure in SQL Server?开发者_JAVA技巧

Please can anyone help in explaining how to write a Stored Procedure?

---

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

To create a stored procedure in SQL, you have to write something like this.

CREATE PROCDEURE [PROCEDURE_NAME]  (
   // ... Add parameter list ...
   // ..........................
)
AS 
BEGIN  
     // ... SQL statements ....
     // .......................
     // .......................
END  

---

you need to look at:

CREATE PROCEDURE <name>
BEGIN
 ...some SQL statements
END

If you are using a dataset in Visual studio, you do not REQUIRE a stored procedure to populate the dataset. You can simply write SQL statements e.g.

SELECT * FROM table

---

Authoring a stored procedure that returns a dataset is the easy part:

create table mytable (col1 char(5), col2 char(5), theKey char(10))
insert into mytable values ('00001', '00001', 'some')
insert into mytable values ('00002', '00002', 'some')
insert into mytable values ('00003', '00003', 'some')
GO 

CREATE PROCEDURE sp_returning_dataset 
    @param1 char(10)
AS
BEGIN

    select col1, col2 from mytable where theKey=@param1

END
GO

--to test sp:
exec sp_returning_dataset 'some'

You just have to make sure that only one select 'goes out' from the stored procedure.

The tricky part is to execute the stored procedure from c#/VB. You have to declare every parameter for the stored procedure and add it to the call:

using System.Data;
using System.Data.SqlClient;
....


SqlConnection  cn = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.Connection = cn;
command.CommandTimeout = cn.ConnectionTimeout;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_returning_dataset";
SqlParameter param = new SqlParameter("@param1", SqlDbType.NText);
param.Value = myParam;
command.Parameters.Clear();
command.Parameters.Add(param);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataSet dataset = new DataSet();
adapter.Fill(dataset);
command.Connection.Close();
//do whatever with dataset:
...