Execute stored procedure on multiple databases using dynamic parameter names

Question

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

You can try to reach your goal using the method SqlCommandBuilder/OracleCommandBuilder DeriveParameters that build the parameter collection for you. But you are still a long way from home. You need to create a specific SqlCommandBuilder or OracleCommandBuilder, then you need to create a viable way to setup the parameters values. If you really are ready to do all this work, then why don't you try to learn an ORM that will help you a lot in abstracting your database code from the underlying database system? — Steve
– Steve, Commented Apr 4, 2016 at 11:56
@oldprogrammer it doesn't matter the name , nothing works expect when the parameter name is the same in the sp defenition . — salah smiti
– salah smiti, Commented Apr 4, 2016 at 13:03
@steve i m using entity framework ... i think that what i need is clear — salah smiti
– salah smiti, Commented Apr 4, 2016 at 13:06

Daniel Hilgarth · Accepted Answer · 2016-04-04 11:41:59Z

1

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.

answered Apr 4, 2016 at 11:41

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

salah smiti Over a year ago

even when the parameter name is "@name" this doesn't work , i have to put "userID" only this work but i do not know the parameter name in the sp defenition

Collectives™ on Stack Overflow

Execute stored procedure on multiple databases using dynamic parameter names

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related