CODEWITHSUNDEEP
c#sqlsql-serverado.net
HenryACN
HenryACN

Reputation: 27

Calling stored procedure error on return value error data type nvarchar(max) to int

I calling a stored procedure and it has an int return value. However there is an error on returning the value back to my back end. 

public async Task<string> CreatePortfolio(Portfolio portfolio)
{
    string statusMessage;

    using (SqlConnection conn = new SqlConnection(Connection))
    {
        SqlParameter returnValue = new SqlParameter(); //Holds the bit that determines if insert was successful or not

        SqlCommand command;
        command = new SqlCommand();
        command.Connection = conn;

        conn.Open();

        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "USP_Add_Portfolio";

        command.Parameters.AddWithValue("@portfolioName", portfolio.PortfolioName);
        command.Parameters.AddWithValue("@description", portfolio.Description);
        command.Parameters.AddWithValue("@createID", portfolio.CreateID);
        command.Parameters.AddWithValue("@updateID", portfolio.UpdateID);
        command.Parameters.AddWithValue("@statusMessage", SqlDbType.NVarChar).Direction = ParameterDirection.Output;
        returnValue.Direction = ParameterDirection.ReturnValue;
        command.Parameters.Add(returnValue);

        int i = await command.ExecuteNonQueryAsync().ConfigureAwait(false);

        if(i == 1)
        {
            statusMessage = command.Parameters["@statusMessage"].Value.ToString();
        }
        else
        {
            statusMessage = "Error while adding please contact your administrator";
        }
    }
    return statusMessage;
}

This is the stored procedure:

create procedure USP_Add_Portfolio
    (@portfolioName as nchar(30) = null,
     @description as nvarchar(200) = null,
     @createID as nvarchar(40) = null,
     @updateID as nvarchar(40) = null,
     @statusMessage as nvarchar(max) output)
as
    declare @success as int = 0

    if @portfolioName is null
        raiserror('Stored procedure USP_Add_Portfolio - Missing Parameter @portfolioName', 16,1)
    else if exists( select * from Portfolio where [Portfolio_Name] = @portfolioName COLLATE SQL_Latin1_General_CP1_CI_AS)
    begin
        set @statusMessage = rtrim(@portfolioName) + ' already exists please try another portfolio name'
        raiserror('Stored procedure USP_Add_Portfolio - Already exists @portfolioName', 16,1)
        return 0
    end
    else if @createID is null
        raiserror('Stored procedure USP_Add_Portfolio - Missing Parameter @Create_ID', 16,1)
    else if @updateID is null
        raiserror('Stored procedure USP_Add_Portfolio - Missing Parameter @Update_ID', 16,1)
    else
        begin
            insert into Portfolio ([Portfolio_Name], [Long_Description], [Create_ID], [Create_TS], [Update_ID], [Update_TS])
            values (@portfolioName, @description, @createID, getdate(), @updateID, getdate())
            --Check to see if insert worked
            set @statusMessage = case when @@ROWCOUNT = 1 then 'Successfully added ' + @portfolioName else 'Unable to add please try again' end
            set @success = case when @@ROWCOUNT = 1 then 1 else 0 end
        end
        return @success
go

The stored procedure finishes and it adds the new record but it errors on 

int i = await command.ExecuteNonQueryAsync().ConfigureAwait(false);

Error:

expecting an int but gets nvarchar

Upvotes: 0

Views: 607

Answers (2)

MPost
MPost

Reputation: 535

ExecuteNonQuery (not worrying about the async for the moment...) returns the number of rows affected for UPDATE, INSERT, DELETE, and -1 otherwise. It does NOT return information directly from the stored procedure.

In your case above, I think you should call the "await" without the "int i =" and not worry about the return value from the ExecuteNonQueryAsync call. Instead, after the value, look at the value in returnValue.Value, which would be the value of the "return" parameter. It is an object, so verify the type or use Convert.ToInt32().

Upvotes: 1

AlwaysLearning
AlwaysLearning

Reputation: 8819

This doesn't look correct because SqlDbType.NVarChar is an enumeration value:

command.Parameters.AddWithValue("@statusMessage", SqlDbType.NVarChar).Direction = ParameterDirection.Output;

What happens if you use this instead:

command.Parameters.Add(new SqlParameter("@statusMessage", SqlDbType.NVarChar, -1)).Direction = ParameterDirection.Output;

Upvotes: 0

Related Questions