How to return a table from a Stored Procedure?

Question

It is very simple question.

I am trying to return a table from a stored procedure, like

select * from emp where id=@id 

I want to return this query result as a table. I have to do this through a stored procedure.

Answer 1

Where is your problem??

For the stored procedure, just create:

CREATE PROCEDURE dbo.ReadEmployees @EmpID INT AS    SELECT *  -- I would *strongly* recommend specifying the columns EXPLICITLY    FROM dbo.Emp    WHERE ID = @EmpID 

That's all there is.

From your ASP.NET application, just create a SqlConnection and a SqlCommand (don't forget to set the CommandType = CommandType.StoredProcedure)

DataTable tblEmployees = new DataTable();  using(SqlConnection _con = new SqlConnection("your-connection-string-here")) using(SqlCommand _cmd = new SqlCommand("ReadEmployees", _con)) {     _cmd.CommandType = CommandType.StoredProcedure;      _cmd.Parameters.Add(new SqlParameter("@EmpID", SqlDbType.Int));     _cmd.Parameters["@EmpID"].Value = 42;      SqlDataAdapter _dap = new SqlDataAdapter(_cmd);      _dap.Fill(tblEmployees); }  YourGridView.DataSource = tblEmployees; YourGridView.DataBind(); 

and then fill e.g. a DataTable with that data and bind it to e.g. a GridView.