How to return a single integer from stored-procedure ?

Question

I have a sql-server 2011 stored procedure which iterates over id's received from a xml parameter and performs insert operations on two relational tables based on those Id's from xml param. The procedure definition is -

ALTER PROCEDURE [dbo].[RC_SP_ROUTE_REPOSITORY_DML]
    @RAWFILES_ID xml

AS
BEGIN

    DECLARE @TEMP_TABLE TABLE(ID BIGINT);


    INSERT INTO @TEMP_TABLE(ID) SELECT node.value('(.)[1]','BIGINT') FROM @RAWFILES_ID.nodes('/rawfile/id') AS Temp(node);

    DECLARE ID_ITERATOR CURSOR FOR SELECT ID FROM @TEMP_TABLE;

        CREATE TABLE #TEMP_TABLE_REPO_ID 
        (
        [ID] BIGINT,
        [REPOSITORY_ID] BIGINT
        );

    OPEN ID_ITERATOR
        DECLARE @ID BIGINT, @ROUTE_ID BIGINT
        FETCH NEXT FROM ID_ITERATOR INTO @ID

    WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SELECT @ROUTE_ID= NEXT VALUE FOR RC_ROUTE_REPOSITORY_SEQ;           


            INSERT INTO RC_ROUTE_REPOSITORY(ID,TITLE)
                    (SELECT @ROUTE_ID,FILE_NAME from RC_RAW_FILES WHERE ID=@ID);


            INSERT INTO RC_ROUTE_WAY_POINTS(ID,ROUTE_REPOSITORY_ID)
            SELECT  NEXT VALUE FOR RC_ROUTE_WAYPOINTS_SEQ,@ROUTE_ID
                    FROM RC_STAGE_WAY_POINTS S_ID                       
                    WHERE S_ID.RAWFILES_ID = @ID;

        FETCH NEXT FROM ID_ITERATOR INTO @ID;
        END
        CLOSE ID_ITERATOR
        DEALLOCATE ID_ITERATOR

END

The value to parameter @RAWFILES_ID is a XML for eg. -

<rawfile>
        <id>
             1001
        </id>
        <id>
             1002
        </id>
        <id>
             1003
        </id>
</rawfile>

My c# code is this

public int AddToRepository(string rawfileid_list)
{
    int routesInserted = 0;

                    SqlCommand cmd = new SqlCommand("[RC_SP_ROUTE_REPOSITORY_DML]", connString)
                    {
                        CommandType = CommandType.StoredProcedure
                    };
                    cmd.CommandTimeout = 5000;

                    cmd.Parameters.Add("@RAWFILES_ID", SqlDbType.Xml);
                    cmd.Parameters["@RAWFILES_ID"].Value = rawfileid_list;
                    connString.Open();

                    DataSet set = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(set);

                    foreach (DataRow item in set.Tables[0].Rows)
                    {
                        var val = item["routes"];
                    }
                    connString.Close();
                    return routesInserted;
}

The expected output is routesInserted=3.

My question is how do I return the number of records inserted in RC_ROUTE_REPOSITORY table in current call to the procedure. The basic question how do you return an integer or a string from procedure ?

My approach to achieve this was incrementing a count variable in cursor and select it to get it in dataset and getting the value from datarow which was not there when I debugged that code. You can seet it in my c# code.

Any better approach to this please suggest .

Answer 1

To answer both your question and show how to do it without a cursor, and assuming that your return value is supposed to be the sum of all rows affected (you can change this to be whatever you like) the stored proc is:

alter PROCEDURE [dbo].[RC_SP_ROUTE_REPOSITORY_DML]
    @RAWFILES_ID xml
AS
BEGIN

    declare @RowCount int;

    DECLARE @TEMP_TABLE TABLE(ID varchar(10), REPOSITORY_ID BIGINT);

    INSERT INTO @TEMP_TABLE(ID, REPOSITORY_ID) SELECT node.value('(.)[1]','BIGINT'), NEXT VALUE FOR RC_ROUTE_REPOSITORY_SEQ FROM @RAWFILES_ID.nodes('/rawfile/id') AS Temp(node);

    INSERT INTO RC_ROUTE_REPOSITORY(ID,TITLE)
    SELECT REPOSITORY_ID,[FILE_NAME]
    from @TEMP_TABLE t
    join RC_RAW_FILES r on r.ID=t.ID;

    set @RowCount=@@ROWCOUNT

    INSERT INTO RC_ROUTE_WAY_POINTS(ID,ROUTE_REPOSITORY_ID)
    SELECT  NEXT VALUE FOR RC_ROUTE_WAYPOINTS_SEQ,REPOSITORY_ID
    from @TEMP_TABLE t
    join RC_STAGE_WAY_POINTS S on s.RAWFILES_ID=t.id

    set @RowCount=@RowCount+@@ROWCOUNT

    return @RowCount
END
go

and the c# code is this (written as a console app but you can see what its doing)

    static void Main(string[] args)
    {
        int routesInserted = 0;
        SqlConnection connString = new SqlConnection("Server=(LocalDb)\\MSSQLLocalDB;Database=test;Trusted_Connection=True;");
        SqlCommand cmd = new SqlCommand("[RC_SP_ROUTE_REPOSITORY_DML]", connString)
        {
            CommandType = CommandType.StoredProcedure
        };
        cmd.CommandTimeout = 5000;

        cmd.Parameters.Add("@RAWFILES_ID", SqlDbType.Xml);
        SqlParameter retValue = cmd.Parameters.Add("@RowCount", SqlDbType.Int);
        retValue.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters["@RAWFILES_ID"].Value = "<rawfile><id>1001</id><id>1002</id><id>1003</id></rawfile>";
        connString.Open();

        cmd.ExecuteNonQuery();

        int x = (int)(retValue.Value);
    }