CODEWITHSUNDEEP
c#sql-serverstored-procedures
Papi
Papi

Reputation: 555

Calling a stored procedure that has dynamic values based on parameters passed

I am attempting to call a stored procedure from C# that basically allows me to pass two parameters to it, an Invoice number and a claim amount.

Based on the values passed to the stored procedure, it validates the Invoice number and claim amount and returns a result which changes (dynamically) based on what you pass.

My problem is that it fails on certain test scenarios and succeeds in others. My result returned can either return 

St_Key, Supplier_Claim, Orig_Inv, System_Cost, Error

columns. But different invoices and claims passed have different result, so for a Invoice where the number does not exit it return just the STKey and the Error message, some validation scenarios can return all, some 3 columns based on the result.

My issue is that the WCF service I test with fails the call if some of the values are null. I hope this makes sense, Please find code here:

public Supplier_Claim_Upload_Result ExcludeFailedValidationRecords(string lineNumber, decimal totalClaim)
{
        Supplier_Claim_Upload_Result supplierClaimUplaod = new Supplier_Claim_Upload_Result();
        var sqlConnection = "data source=WMVSQL02;initial catalog=Embrace;integrated security=True;";

        using (SqlConnection conn = new SqlConnection(sqlConnection))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 60;
            SqlDataReader reader;

            cmd.CommandText = "CRM.Supplier_Claim_Upload";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Invoice", SqlDbType.NVarChar).Value = lineNumber;
            cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = totalClaim;
            cmd.Connection = conn;

            conn.Open();
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                supplierClaimUplaod.ST_Key = reader["ST_Key"].ToString();
                supplierClaimUplaod.Supplier_Claim = reader["Supplier_Claim"].ToString() != null ? reader["Supplier_Claim"].ToString() : string.Empty;
                supplierClaimUplaod.Orig_Inv = reader["Orig_Inv"].ToString();
                supplierClaimUplaod.System_Cost = reader["System_Cost"].ToString();
                //supplierClaimUplaod.Error = reader["Error"] == null ? reader["Error"].ToString() : null; // this line fails on 1st example
            }

            conn.Close();
            return supplierClaimUplaod;
        }
}

Here are two examples of different result returned

DECLARE @return_value int

EXEC    @return_value = [CRM].[Supplier_Claim_Upload]
        @Invoice = NCNTA5000229001-1,
        @Amount = 663.800

SELECT  'Return Value' = @return_value
GO

Here I pass Invoice number NCNTA5000229001-1 and claim amount 663.800, this is the response I get

First Response Example

And here is a second example where only STKey and Error is returned from the stored procedure, I pass non existing Invoice and incorrect amount

Second Response

My problem that I am having is that when I call the stored procedure for example 1, it fails as my result type has no error column there my service fails. Any idea how I can return only the result required for each scenario in a generic way, as the result changes on different calls and it fails my code?

Upvotes: 0

Views: 461

Answers (1)

sll
sll

Reputation: 62484

You shouldn't return different schema by the same stored procedure, to indicate known error you should throw exception from stored procedure with known error code identifier then handle it on application side.

You might find useful

Upvotes: 1

Related Questions