asked on

SQL Stored Procedure Output C#

I have a stored procedure that perform a query, and outputs a COUNT of the records.

When I run it in SQL Server Management Console; I get a @totalRows value of 32,545 count.

BUT, when I execute this in Asp.net C#, the value of @totalRows always comes back as 2, not 32,545...

Am I doing something wrong?

USE [database]
GO

DECLARE	@return_value int,
		@totalRows int

EXEC	@return_value = sp_PagingNewPubs
		@startRowIndex = 1,
		@maxRows = 6,
		@totalRows = @totalRows OUTPUT

SELECT	@totalRows as N'@totalRows'

C# Code
======
        SqlConnection sqlConn = new SqlConnection();
        sqlConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString.ToString();

        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.Connection = sqlConn;
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.CommandText = "sp_PagingNewPubs";
        sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int).Value = GridPubs.PageIndex;
        sqlCmd.Parameters.Add("@maxRows", SqlDbType.Int).Value = GridPubs.PageSize;
        sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int).Value = ParameterDirection.Output;

        SqlDataAdapter sqlDad = new SqlDataAdapter();
        sqlDad.SelectCommand = sqlCmd;

        DataTable sqlDt = new DataTable();

        sqlDad.Fill(sqlDt);

        int totalPages = (int)sqlCmd.Parameters["@totalRows"].Value;

Open in new window

Jesus Rodriguez

Check this link http://www.sqlteam.com/article/stored-procedures-returning-data
on the section for output variables and try to check you code to

EXEC      @return_value = sp_PagingNewPubs
            @startRowIndex = 1,
            @maxRows = 6,
            totalRows = @totalRows OUTPUT

SELECT      totalRows = N'@totalRows'

ASKER CERTIFIED SOLUTION

Carlos Villegas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carlos Villegas

Explaining it:

In your code your @totalRows parameter has the value of 2 because it is never filled by your sp (you never specify the direction of your parameter), instead it is filled with 2 when you do this:

sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int).Value = ParameterDirection.Output;

Open in new window

That is because the integer value of ParameterDirection.Output is 2, you need to specify the direcction of your parameter by using the Direction property of your SqlParameter class, as in the next code:

SqlParameter paramTotalRows = sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int);
paramTotalRows.Direction = ParameterDirection.Output;

Open in new window

Then, after execute your query, paramTotalRows.Value will contain the value assigned to @totalRows by your SP.