I have this StoredProc which returns value depending on result
CREATE PROCEDURE dbo.Test
AS
SET NOCOUNT ON
DECLARE @ERROR INT,
@ROWCOUNT INT
SELECT
FN,
LN,
MI
FROM
dbo.Table1 WITH (READUNCOMMITTED)
SELECT
@ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT
IF @ERROR<>0
RETURN 1
IF @ROWCOUNT=0
RETURN 2
RETURN 0
GO
in my DAL Layer im checking return value
try{
string conn = "Data Source=127.0.0.1;Initial Catalog=tempdb;User Id=Developer;Password=Developer;";
SqlParameter[] oSQLParam = new SqlParameter[1];
oSQLParam[0] = new SqlParameter("@ReturnValue", SqlDbType.Int);
oSQLParam[0].Direction = ParameterDirection.ReturnValue;
oDRFormatInfo = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure,"Test", oSQLParam);
oDRFormatInfo.Close();
if(null != oSQLParam[0] && (int)oSQLParam[0].Value == 0)
{
// set return object depending on return value from SP
}
else
{
// set return object depending on return value from SP
}
}
catch(Exception ex)
{
throw new ApplicationException(ex.Message);
}
roblem is i dont get returnvalue in oSQLParam[0].Value untill I close the reader. and i dont want to close the reader bcoz i want to use it in my calling fucntion also.I know i can use DataSet to solve this problem,but i want the performance gain. and i'm doing only read operation no Update
any solution
How to get return value from StoredProc using SqlDataReader
Wes1234
Then don't get the integer from the return value; get it from an OUTPUT parameter instead:
CREATE PROCEDURE dbo.Test
@RETURNVAL INT = 0 OUTPUT
AS
SET NOCOUNT ON
DECLARE @ERROR INT,
@ROWCOUNT INT
SELECT FN, LN, MI FROM dbo.Table1 WITH (READUNCOMMITTED)
SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT
IF @ERROR<>0
BEGIN
RETURN 1
SET @RETURNVAL = 1
END
IF @ROWCOUNT=0
BEGIN
RETURN 2
SET @RETURNVAL = 1
END
RETURN 0
GO
Then code C# differently:
try{
string conn = "Data Source=127.0.0.1;Initial Catalog=tempdb;User Id=Developer;Password=Developer;";
SqlParameter[] oSQLParam = new SqlParameter[1];
oSQLParam[0] = new SqlParameter("@RETURNVAL", SqlDbType.Int);
oSQLParam[0].Direction = ParameterDirection.Output;
oDRFormatInfo = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure,"Test", oSQLParam);
oDRFormatInfo.Close();
if(null != oSQLParam[0] && (int)oSQLParam[0].Value == 0)
{
// set return object depending on return value from SP
}
else
{
// set return object depending on return value from SP
}
}
catch(Exception ex)
{
throw new ApplicationException(ex.Message);
}