CODEWITHSUNDEEP
c#sql-serverstored-procedures
user2956437
user2956437

Reputation: 37

Return multiple values from Stored Procedure in C#

I need to return 2 values from stored procedure in my application. Below is the code snippet in my application. I need to get the values of SureveyID & InputID below after the respective insert statements. 

        int surveyId = 0;
        int inputId = 0;    
        SqlDataManager manager = new SqlDataManager();
        manager.AddParameter("@Name", surveyInstance.SurveyName);
        manager.AddParameter("@Type", surveyInstance.SurveyType);
        manager.AddParameter("@UserId", surveyInstance.UserId);
        manager.AddParameter("@InputType", surveyInstance.InputType);
        manager.AddParameter("@DisplayName", surveyInstance.DisplayName);
        manager.AddOutputParameter("@SurveyID",System.Data.DbType.Int32,surveyId);
        manager.AddOutputParameter("@InputID", System.Data.DbType.Int32,inputId);
        manager.ExecuteNonQuery("pr_CreateSurvey");

AddParameter & AddOutputParameter is custom method as below

 public void AddParameter(string parameterName, DbType parameterType, object        parameterValue)
    {
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = parameterName;
        parameter.DbType = parameterType;
        parameter.Value = parameterValue;
        parameters.Add(parameter);
    }

public void AddOutputParameter(string parameterName, DbType parameterType, object parameterValue)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = parameterName;
            parameter.DbType = parameterType;
            parameter.Value = parameterValue;
            parameter.Direction = ParameterDirection.Output;
            parameters.Add(parameter);
        }

Below is code snippet from stored procedure

ALTER PROCEDURE [dbo].[pr_CreateSurvey] 
    -- Add the parameters for the stored procedure here
    @Name varchar(50),@Type varchar(50),@UserId varchar(50),@InputType varchar(50),@DisplayName varchar(50),
    @SurveyID int OUTPUT,@InputID 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
    Insert into surveys(name,user_id,display_name,type) values(@Name,@UserId,@DisplayName,@Type)
    SET @SurveyID = SCOPE_IDENTITY()
    Insert into input_types(name) values (@InputType)
    SET @InputID = SCOPE_IDENTITY()
END

The insert statements are working fine but I am not getting back any value in my application. Its 0. I tried returning 1 value(SurveyID) by using below statement but still not getting correct value. Its returning -1 everytime.

surveyId = manager.ExecuteNonQuery("pr_CreateSurvey");

I tried a lot but no luck. Please advise.

Upvotes: 2

Views: 7758

Answers (3)

user3036342
user3036342

Reputation: 1045

ExecuteNonQuery returns the number of rows affected, not anything else.

I've never used output parameters in my stored procedures for this. What I usually do is select out the values I want to return.

So after you do this:

SET @SurveyID = SCOPE_IDENTITY()
SET @InputID = SCOPE_IDENTITY()

I will do this

select @SurveyID as SurveyID,@InputID as InputID

Of course I'll declare those variables within the stored procedure and not as OUTPUT

This should give you what you want. If it's the correct way? Not sure. But it sure as hell works and is easy ;)

Upvotes: 1

PrfctByDsgn
PrfctByDsgn

Reputation: 1050

find the values of the output parameters in the Parameters collection of your SqlCommand ... like

mySqlCommand.Parameters["@SurveyID"].Value

after you executed 

mySqlCommand.ExecuteNonQuery();

Upvotes: 3

nvoigt
nvoigt

Reputation: 77285

Keep a variable of your ouput parameters around and check the .Value afterwards. The ouput gets written to the parameter, but due to boxing, it does not get written to your int. Basically, your int was copied into the parameter and your original variables do not change.

Upvotes: 1

Related Questions