Result of restoring a SQL Server stored procedure
I have a stored procedure in SQL Server:
CREATE PROCEDURE [dbo].[GET_AVAILABLE_PLACES]
-- Add the parameters for the stored procedure here
@eventGuid uniqueidentifier,
@placeGuid uniqueidentifier,
@dateGuid dateTime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @ReservedPlaces int;
DECLARE @TotalPlaces int;
SELECT @ReservedPlaces = RESERVED_PLACES FROM dbo.EVENT_DATE_PLACE
WHERE EVENT_GUID = @eventGuid
and DATE_BEGIN = @dateGuid
and PLACE_GUID = @placeGuid
SELECT @TotalPlaces = NUMBER_PLACES FROM dbo.PLACES
WHERE GUID = @placeGuid
RETURN @TotalPlaces - @ReservedPlaces;
END
But I cannot read the result returned by
private int SelectByStoredProcedureGetAvailablePlaces(string entryParam1, string entryParam2, DateTime entryParam3)
{
int results;
//PlanningElement plan = GetPlanningElement(entryParam1, entryParam2, entryParam3.ToString(), "31/12/2012 00:00:00", "150");
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "GET_AVAILABLE_PLACES";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("eventGuid", entryParam1);
sqlCommand.Parameters.AddWithValue("placeGuid", entryParam2);
sqlCommand.Parameters.AddWithValue("dateGuid", entryParam3);
sqlConnection.Open();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
results = sqlDataReader.GetInt32(0);
sqlConnection.Close();
}
return results;
}
What is the problem?
thank
+3
source to share
5 answers
The method GetInt32
will read from the selected output table file. You want to get the return value, so you can change the code
SqlParameter returnValueParam = sqlcomm.Parameters.Add("@ReturnValue", SqlDbType.Int);
returnValueParam.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(returnValueParam);
...
sqlCommand.ExecuteNonQuery();
result = returnValueParam.Value;
+2
source to share
You can change the stored procedure, replace
RETURN @TotalPlaces - @ReservedPlaces;
By:
SELECT (@TotalPlaces - @ReservedPlaces) AS [AvailablePlaces]
RETURN;
You can also get the return value from a stored procedure, but this requires some additional changes. See this question for more information.
+1
source to share
This return type must be read using an additional parameter in the SqlCommand.Parameters collection with the direction System.Data.ParameterDirection.ReturnValue
To read it as you are trying, your SQL procedure should do:
SELECT @TotalPlaces - @ReservedPlaces As MyResult
Then the result will be returned as a result, not a return value.
0
source to share
So my stored procedure should be like this?
CREATE PROCEDURE [dbo].[GET_AVAILABLE_PLACES]
-- Add the parameters for the stored procedure here
@eventGuid uniqueidentifier,
@placeGuid uniqueidentifier,
@dateGuid dateTime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @ReservedPlaces int;
DECLARE @TotalPlaces int;
SELECT @ReservedPlaces = RESERVED_PLACES FROM dbo.EVENT_DATE_PLACE
WHERE EVENT_GUID = @eventGuid
and DATE_BEGIN = @dateGuid
and PLACE_GUID = @placeGuid
SELECT @TotalPlaces = NUMBER_PLACES FROM dbo.PLACES
WHERE GUID = @placeGuid
SELECT @TotalPlaces - @ReservedPlaces As ReturnValue;
END
And my function is like this?
private int SelectByStoredProcedureGetAvailablePlaces(string entryParam1, string entryParam2, DateTime entryParam3)
{
int results;
//PlanningElement plan = GetPlanningElement(entryParam1, entryParam2, entryParam3.ToString(), "31/12/2012 00:00:00", "150");
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "GET_AVAILABLE_PLACES";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("eventGuid", entryParam1);
sqlCommand.Parameters.AddWithValue("placeGuid", entryParam2);
sqlCommand.Parameters.AddWithValue("dateGuid", entryParam3);
SqlParameter returnValueParam = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
returnValueParam.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(returnValueParam);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
result = returnValueParam.Value;
sqlConnection.Close();
}
return results;
}
0
source to share
Try doing this in your processed routine.
CREATE PROCEDURE [dbo].[GET_AVAILABLE_PLACES]
-- Add the parameters for the stored procedure here
@eventGuid uniqueidentifier,
@placeGuid uniqueidentifier,
@dateGuid dateTime,
@ReservedPlaces int output,
@TotalPlaces int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @ReservedPlaces = RESERVED_PLACES FROM dbo.EVENT_DATE_PLACE
WHERE EVENT_GUID = @eventGuid
and DATE_BEGIN = @dateGuid
and PLACE_GUID = @placeGuid
SELECT @TotalPlaces = NUMBER_PLACES FROM dbo.PLACES
WHERE GUID = @placeGuid
SELECT @TotalPlaces - @ReservedPlaces As ReturnValue;
END
And it's in your program
private int SelectByStoredProcedureGetAvailablePlaces(string entryParam1, string entryParam2, DateTime entryParam3)
{
int results;
//PlanningElement plan = GetPlanningElement(entryParam1, entryParam2, entryParam3.ToString(), "31/12/2012 00:00:00", "150");
using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "GET_AVAILABLE_PLACES";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("eventGuid", entryParam1);
sqlCommand.Parameters.AddWithValue("placeGuid", entryParam2);
sqlCommand.Parameters.AddWithValue("dateGuid", entryParam3);
SqlParameter returnValueParam = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
returnValueParam.Direction = ParameterDirection.Output;
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
result = returnValueParam.Value;
sqlConnection.Close();
}
return results;
0
source to share