Returning Multiple Values from a Stored Procedure

Returning multiple values from a stored procedure

ALTER procedure ashwin @empid int,@empname varchar(20) output,@age int output
as
select @empname=ename,@age=age
from emp where empid=@empid;

declare @ename varchar(20),@age int
execute ashwin 101,@ename out,@age out
select @ename,@age;

//------------

namespace sqlserver
{
    class Program
    {
        static void Main(string[] args)
        {

            createconnection();
        }
        public static void createconnection()
        {
            SqlConnection con=new SqlConnection("Data Source=ASHWIN\\SQLEXPRESS;Initial Catalog=employee;Integrated Security=True;Pooling=False");
            con.Open();

            SqlCommand cmd=new SqlCommand("ashwin",con);
            cmd.CommandType=CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@empid",SqlDbType.Int,10,"empid"));
            cmd.Parameters.Add(new SqlParameter("@empname", SqlDbType.VarChar, 20,ParameterDirection.Output,false,0,20,"ename",DataRowVersion.Default,null));
            cmd.Parameters.Add(new SqlParameter("@age", SqlDbType.Int, 20, ParameterDirection.Output, false, 0, 10, "age", DataRowVersion.Default, null));
            cmd.Parameters[0].Value = 101;
            cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
            cmd.ExecuteNonQuery();
            string name = (string)cmd.Parameters["@empname"].Value;
            int age = Convert.ToInt32(cmd.Parameters["@age"].Value);
            Console.WriteLine("the name is {0}--and age is {1}", name,age);

            Console.ReadLine();
        }
    }

Return multiple output from a stored procedure

Yes. You can do that. You can get multiple output parameters from another procedure to parent procedure. Consider the following example.

Note : You should pass the parameters in the same order that is present in sp_SubSalaryCalcuation procedure.

1. Parent Procedure

CREATE PROCEDURE ParentProcedure

    @Param1 nvarchar(30)

AS
BEGIN

    DECLARE @out_timestatus nvarchar(100)
    DECLARE @out_status nvarchar(100)
    DECLARE @out_overtime nvarchar(100)

    SET NOCOUNT ON;

    -- Send parameters in the same order of sp_SubSalaryCalcuation
    exec sp_SubSalaryCalcuation 1,2,3,4,@out_timestatus OUTPUT,@out_status OUTPUT,@out_overtime OUTPUT

    PRINT @out_timestatus
    PRINT @out_status
    PRINT @out_overtime

END
GO

2. Child procedure

CREATE PROCEDURE sp_SubSalaryCalcuation 
@sec1_in nvarchar(200),
@sec1_out nvarchar(200),
@worktimefrm nvarchar(200),
@worktimeto nvarchar(200),
@out_overtime nvarchar(100) OUTPUT,
@out_status nvarchar(100) OUTPUT,
@out_overtimestatus nvarchar(100) OUTPUT

AS
BEGIN

    SET NOCOUNT ON;

    SET @out_overtime = 10
    SET @out_status = 20
    SET @out_overtimestatus = 30

END
GO

Result in you parameter of parent procedure will be as follows

x-----------------x----------x
|     Variable    |   Value  |
x-----------------x----------x
| @out_timestatus |   10     |
| @out_status     |   20     |
| @out_overtime   |   30     |
x-----------------x----------x

Returning multiple values from sql stored procedure

You have multiple selects which means multiple result-sets. So you have to move through them:

objRdr2.Read()
Session("RequestsSent") = objRdr2("requestsSent")

objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotal") = objRdr2("requestTotal")

objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotalPaid") = objRdr2("requestTotalPaid")

objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotalUnpaid") = objRdr2("requestTotalUnpaid")

Or, you can change the sproc to return one resultset with multiple columns:

Alter PROCEDURE  [dbo].[spDashboardPaymentRequests]
@id integer
AS
SELECT
    (SELECT COUNT(Receiptno) FROM [demofeepay3].[dbo].[vwallrequests] Where Orgid = @id) 
    as requestsSent,

    (SELECT Sum(totamount) FROM [demofee].[dbo].[vwallrequests] Where Orgid = @id) 
    as requestTotal,

    (SELECT Sum(totamount) FROM [demofee].[dbo].[vwallrequests] Where Orgid = @id AND status = 'paid')
    as requestTotalPaid,

    (SELECT Sum(totamount) FROM [demo].[dbo].[vwallrequests] Where Orgid = @id AND status = 'unpaid')
    as requestTotalUnpaid

A stored procedure returns multiple values but only need one

Considering there is a need for second column some where else.

Insert the result into temp table and take the float value from there

Insert into #temp(float_col,percent_col)
EXEC Interface.Proc_getpivalueaveragetime
  @result,
  @tag_in,
  @ProductionDate,
  @endDate,
  @pi_server,
  @Debug 

select @FCChemGALPERMIN= float_col
From #temp

If your second column is not useful at any case then alter your procedure from resulting percentage column

Return multiple values in stored procedure from function - SQL Server

Function

The function seems erroneous as the inner joins don't have a ON statement.

ALTER FUNCTION [dbo].[getFn]()
returns @table TABLE (value varchar(255))
as 
BEGIN 
    DECLARE @Desc1 varchar(200)
    DECLARE @Desc2 varchar(200)

    DECLARE c1  CURSOR FOR

    SELECT CMSC.C
    FROM   CMSC 
    INNER JOIN CMSB 
    INNER JOIN CMSC
    WHERE CMSC.C = "HELLO" OR CMSC.C = "GOODBYE"
    GROUP BY CMSC.C

    Open c1
    FETCH NEXT FROM c1 INTO @Desc1
    set @Desc2=@Desc1 
    INSERT INTO @table VALUES (@Desc2)
    CLOSE c1
    deallocate c1

    Return 
end

SP

Again the syntax of the SP is also wrong as the ON statement is missing in the INNER JOIN.

ALTER procedure [dbo].[sp1]            
as             
begin                        
DECLARE @od varchar(255)
EXEC @od =dbo.getFn

SELECT CMSA.A,CMSB.B, a.Value
FROM CMSA INNER JOIN CMSB
INNER JOIN (SELECT * FROM dbo.getFn()) a
GROUP BY CMSA.A, CMSB.B
end

It might be an INNER JOIN or a CROSS JOIN depending on whether you have a field to reference on

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);

---

Related Topics