Click here to Skip to main content
14,454,912 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am executing a stored procedure and trying to get the 'after execution' value of an output parameter (@parmRecID), which should be the value of the RecID of the record the stored procedure added to a table.

The value of the output parameter is always "1" although the stored procedure correctly sets the it to the RecID of the newly added record.

The below code instantiates a class then uses a function from that class to execute the stored procedure and then (unsuccesfully) retrieve the value of the output parameter and return that value as the return value of the called method (function).

SQLDatabase MySQLClass = new SQLDatabase();
string TheDatabase = "CustomerDB";
string TheProcedure = "usp_AddCustomer";

Dictionary<string, ParameterValues> TheParams = new Dictionary<string, ParameterValues>();
ParameterValues values = new ParameterValues();
values.Value = TxtFirstName.Text; values.Direction = "Input";
TheParams.Add("@parmFirstName", values);
values = new ParameterValues();
values.Value = TxtLastName.Text; values.Direction = "Input";
TheParams.Add("@parmLastName", values);
values = new ParameterValues();
values.Value = TxtAddress.Text; values.Direction = "Input";
TheParams.Add("@parmAddress", values);
values = new ParameterValues();
values.Value = TxtCity.Text; values.Direction = "Input";
TheParams.Add("@parmCity", values);
values = new ParameterValues();
values.Value = TxtZipCode.Text; values.Direction = "Input";
TheParams.Add("@parmZipCode", values);
values = new ParameterValues();
values.Value = ""; values.Direction = "Output";
TheParams.Add("@parmRecID", values);

string RecID = MySQLClass.ExecStoredProcedure(TheDatabase, TheProcedure, TheParams);

MessageBox.Show("From FrmMain, RecID is " + RecID);


Below is the called method (MySQLClass.ExecStoredProcedure):

public string ExecStoredProcedure(string Database, string Procedure, Dictionary<string, ParameterValues> Parameters)
        {
            string TheConnectionString = $"Data Source=DESKTOP-EL4GU86;Initial Catalog={Database};Integrated Security=true";
            using (SqlConnection Conn = new SqlConnection(TheConnectionString))
            {
                using (SqlCommand Cmd = new SqlCommand(Procedure, Conn))
                {
                    Conn.Open();
                    Cmd.CommandType = CommandType.StoredProcedure;
                    foreach (KeyValuePair<string, ParameterValues> entry in Parameters)
                    {
                        ParameterValues vals = entry.Value;
                        Cmd.Parameters.AddWithValue(entry.Key, vals.Value);
                        switch (vals.Direction)
                        { 
                            case "Output":
                                Cmd.Parameters[entry.Key].Direction = ParameterDirection.Output;
                                break;
                            default:
                                Cmd.Parameters[entry.Key].Direction = ParameterDirection.Input;
                                break;
                        }
                    }

                    Cmd.ExecuteNonQuery();

                    foreach (SqlParameter param in Cmd.Parameters)      // This 'foreach' is just to show the 'after execution'
                    {                                                   // value of the output parameter..
                        string pName = param.ParameterName;
                        if (pName == "@parmRecID")
                        {
                            string pValue = param.Value.ToString();
                            MessageBox.Show("From SQLClass, " + pName + " = " + pValue);
                        }
                    }

                    return Cmd.Parameters["@parmRecID"].Value.ToString();
                }
            }
        }
    }

    public class ParameterValues
    {
        public string Value { get; set; }
        public string Direction { get; set; }
    }


While researching this problem I found "System.Data.ParameterDirection.ReturnValue;"
which would allow me to get a return value from the stored procedure, but I'm just now trying to figure out why I can't get the value of the output parameter after executing the stored procedure...

While I'm at it, here's the stored procedure...

CREATE OR ALTER procedure [dbo].[usp_AddCustomer] 

	@parmFirstName as varchar(50),
	@parmLastName as varchar(50),
	@parmAddress as varchar(50),
	@parmCity as varchar(50),
	@parmZipCode as varchar(50),
	@parmRecID as varchar(10) output

as

	if exists
				(
					select	* 
					from	CustomerDB.dbo.SmallCustomerTable 
					where	FirstName = @parmFirstName
					and		LastName = @parmLastName
					and		Address = @parmAddress
				)
			set @parmRecID = '0'
	else
			begin
				insert into	CustomerDB.dbo.SmallCustomerTable
				select		@parmFirstName,
							@parmLastName,
							@parmAddress,
							@parmCity,
							@parmZipCode 
				set @parmRecID = (select convert(varchar, max(RecID)) from SmallCustomerTable)
			end

GO


What I have tried:

The above code is what I have tried...
Posted
Updated 13-Feb-20 9:06am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Because the value of your parameter is set to an empty string, and you don't set the parameter's Size property, the parameter defaults to nvarchar(1).

As a result, your code will only return the first character of the output parameter value.

The quick fix is to set Size = -1 on the output parameter.
case "Output":
    Cmd.Parameters[entry.Key].Direction = ParameterDirection.Output;
    Cmd.Parameters[entry.Key].Size = -1;
    break;
There are various other improvements you could make. For example, your code will currently only work if you have a parameter called @parmRecID, and it will only return the value of that parameter. Any other output parameters will be ignored.

You should also use SCOPE_IDENTITY within your stored procedure, rather than selecting the maximum ID.
SCOPE_IDENTITY (Transact-SQL) - SQL Server | Microsoft Docs[^]

Here's a start:
public class ParameterValue
{
    public object Value { get; set; }
    public string Direction { get; set; } = "Input";
    public int? Size { get; set; }
}

public class SQLDatabase
{
    private readonly string _connectionString;
    
    public SQLDatabase(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    public SQLDatabase() : this("Data Source=DESKTOP-EL4GU86;Integrated Security=true")
    {
    }
    
    public int ExecuteNonQuery(string database, string commandText, IReadOnlyDictionary<string, ParameterValue> parameters, CommandType commandType = CommandType.StoredProcedure)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            connection.ChangeDatabase(database);
            
            using (var command = new SqlCommand(commandText, connection))
            {
                command.CommandType = commandType;
                
                var outputParameters = new Dictionary<SqlParameter, ParameterValue>();
                foreach (var pair in parameters)
                {
                    var param = command.Parameters.AddWithValue(pair.Key, pair.Value.Value);
                    if (pair.Value.Size != null) param.Size = pair.Value.Size.GetValueOrDefault();
                    
                    switch (pair.Value.Direction)
                    {
                        case "Output":
                        {
                            param.Direction = ParameterDirection.Output;
                            outputParameters.Add(param, pair.Value);
                            break;
                        }
                        case "ReturnValue":
                        {
                            param.Direction = ParameterDirection.ReturnValue;
                            outputParameters.Add(param, pair.Value);
                            break;
                        }
                    }
                }
                
                int result = command.ExecuteNonQuery();
                
                foreach (var pair in outputParameters)
                {
                    pair.Value.Value = pair.Key.Value;
                }
                
                return result;
            }
        }
    }
}
Usage:
var db = new SQLDatabase();
const string theDatabase = "CustomerDB";
const string commandText = "usp_AddCustomer";

var parameters = new Dictionary<string, ParameterValue>
{
    ["@paramFirstName"] = new ParameterValue { Value = TxtFirstName.Text, Size = 50 },
    ["@paramLastName"] = new ParameterValue { Value = TxtLastName.Text, Size = 50 },
    ["@paramAddress"] = new ParameterValue { Value = TxtAddress.Text, Size = 50 },
    ["@paramCity"] = new ParameterValue { Value = TxtCity.Text, Size = 50 },
    ["@paramZipCode"] = new ParameterValue { Value = TxtZipCode.Text, Size = 50 },
    ["@paramRecID"] = new ParameterValue { Direction = "Output", Size = -1 }
};

db.ExecuteNonQuery(theDatabase, commandText, parameters);

object RecID = parameters["@paramRecID"].Value;
MessageBox.Show("From FrmMain, RecID is " + RecID);
Stored procedure:
CREATE OR ALTER PROCEDURE [dbo].[usp_AddCustomer] 
(
    @parmFirstName As varchar(50),
    @parmLastName As varchar(50),
    @parmAddress As varchar(50),
    @parmCity As varchar(50),
    @parmZipCode As varchar(50),
    @parmRecID As int OUTPUT
)
As
    If Exists
    (
        SELECT * 
        FROM dbo.SmallCustomerTable 
        WHERE FirstName = @parmFirstName
        And LastName = @parmLastName
        And Address = @parmAddress
    )
    BEGIN
        SET @paramRecID = 0;
    END
    Else
    BEGIN
        INSERT INTO dbo.SmallCustomerTable (FirstName, LastName, Address, City, ZipCode)
        VALUES (@parmFirstName, @parmLastName, @parmAddress, @parmCity, @parmZipCode);
        
        SET @paramRecID = SCOPE_IDENTITY();
    END;
GO
   
Comments
Member 14633063 13-Feb-20 14:33pm
   
Thank you Richard
I had earlier set the parameter = "xxxx" (trying to get it to work) before execution, but that didn't work. I, using your comments as an "Aha!" moment, changed the Direction to InputOutput then it recognized the Size of "xxxx" and returned all of the value instead of just the first character.
So now it's working. I'm also going to look at .Size and the other suggestions for improvements that you made.

Thanks again...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100