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

User · Answer

In SQL Server 2008 you can use   http   www sommarskog se share data html tableparam  or else simple and same as common execution  CREATE PROCEDURE OrderSummary  MaxQuantity INT OUTPUT AS  SELECT Ord EmployeeID  SummSales   SUM OrDet UnitPrice   OrDet Quantity  FROM Orders AS Ord      JOIN  Order Details  AS OrDet ON  Ord OrderID   OrDet OrderID  GROUP BY Ord EmployeeID ORDER BY Ord EmployeeID  SELECT  MaxQuantity   MAX Quantity  FROM  Order Details   RETURN  SELECT SUM Quantity  FROM  Order Details   GO   I hopes its help to you

User · Answer

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

User · Answer

It s VERY important to include    SET NOCOUNT ON    into SP  In First line   if you do INSERT in SP  the END SELECT can t return values   THEN  in vb60 you can   SET RS   CN EXECUTE SQL    OR   RS OPEN CN  RS  SQL

[sql] How to return a table from a Stored Procedure?

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures