CODEWITHSUNDEEP
c#sql-serveroracle-databasestored-procedures
salah smiti
salah smiti

Reputation: 1

Execute stored procedure on multiple databases using dynamic parameter names

I have a method which is supposed to run stored procedure on an oracle and sql server databases using dynamic parameters (you don't know the parameter names).

IDbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;

int i = 0;
foreach (string parameterName in names)
    {
    var param2 = cmd.CreateParameter();

    param2.ParameterName = parameterName + "__" + i.ToString();
    param2.DbType = DbType.String;
    param2.Direction = ParameterDirection.Input;
    param2.Value = parameterName;
    cmd.Parameters.Add(param2);
    i++;
}
cmd.CommandText = "spName";

This code works on Oracle but sql server throws

Procedure or function 'pspName' expects parameter '@userID', which was not supplied

Because the parameter name in my code is not '@userID'. Thanks

Upvotes: 0

Views: 297

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

There is a difference between Oracle and SQL Server:

The parameter names in Oracle need to be the name only, but for SQL Server, you need to add the @ sign before the parameter name.

You need to consider this somehow, somewhere in your code.

Upvotes: 1

Related Questions