SQL Server Stored Procedure Return a Table

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 return a table from a Stored Procedure?

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.

SQL Server : have stored procedure return dataset and carry out comparison

One approach is to call the existing stored procedure for each product line using INSERT...EXEC to capture the results of each into a temp table/variable. The table/variable schema must match that of the QueryProductLines result set, although you can ignore unneeded columns in the final query.

Below is an example of this technique.

CREATE PROCEDURE dbo.FindCommonProductLines
      @ParamProductLineIdOne int
    , @ParamProductLineIdTwo int
AS
SET NOCOUNT ON;

DECLARE @ProductLineIdOne TABLE(
    ProductName varchar(100) PRIMARY KEY
);
DECLARE @ProductLineIdTwo TABLE(
    ProductName varchar(100) PRIMARY KEY
);

INSERT INTO @ProductLineIdOne(ProductName)
    EXEC dbo.QueryProductLines @ParamProductLineId = @ParamProductLineIdOne;
INSERT INTO @ProductLineIdTwo(ProductName)
    EXEC dbo.QueryProductLines @ParamProductLineId = @ParamProductLineIdTwo;

SELECT p1.ProductName
FROM @ProductLineIdOne AS p1
JOIN @ProductLineIdTwo AS p2 ON p2.ProductName = p1.ProductName;
GO

From a performance perspective, but at the expense of code reuse, it would be more efficient to develop a specialized query to return the common products.

How to return a user defined table type from a stored procedure

You just CANNOT return table-valued variable from stored procedure.

As you said, you cannot use table-valued parameter if it's not declared as readonly.

In your simple case you can use an inline table-valued function instead:

create function  getCarDetails( @carNumber varchar(20))
returns table
as
  return 
       select CarNumber, Model
       from dbo.cars
       where CarNumber = @carNumber;

How to return an id and use it directly in another stored procedure?

If you want to return something from stored procedure to the context of SQL query execution you may use a return statement or an output parameter. I would suggest you to use the second option. The first one is generally intended to return status of procedure execution.

ALTER PROCEDURE [dbo].[InsertAddress_DBO]
    @Name VARCHAR(50),
    @Address_ID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Address]([Address_Name])
    VALUES (@Name)
    SET @Address_ID = SCOPE_IDENTITY()
END

Than you can use returned value in your outer procedure

ALTER PROCEDURE [dbo].[InsertEstablishmentByStrings_DBO]
    @Establishment_Name VARCHAR(50),
    @Address_Name VARCHAR(50),
    @Documentation_Text VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Address_ID INT ,
        @Documentation_ID INT 

    EXEC [dbo].[InsertAddress_DBO] 
        @Address_ID = @Address_ID OUTPUT,
        @Name = "rue de la banchiesserie 85 Golback"

     ...
END

An OUTPUT INSERTED clause you used doesn't returns data to the query execution context but send them to the output stream.

---

Related Topics