Where Should I Keep Stored Procedure Names in my Code?

Question

I've taken over some code at work that has a database repository file that keeps stored procedure names in constants. The file looks like this:

class DatabaseRepository
{
    private const string SP_GET_EMPLOYEES = "GetEmployees";
    private const string SP_GET_DEPARTMENTS = "GetDepartments";
    private const string SP_GET_MANAGERS = "GetManagers";
    // Many more stored procedure constants ...

    public EmployeeList GetEmployees()
    {
        return RunCommand(DatabaseRepository.SP_GET_EMPLOYEES);
    }

    //more methods below that call the stored procedures
}

I find it annoying to deal with these constants when I'm working on the boundary between the code and the stored procedures.

1. If I want to know what stored procedure a method is using, I have to scroll up or go-to-definition for the constant. I then usually have to scroll back to or search for the method in question because I may be working on that method.

2. Going the other direction, if I want to know what method calls a given stored procedure (there's usually only one), I have to search the code for the stored procedure name, copy the string constant's name, and then search the code for that string constant.

In both cases #1 and #2, I could prevent searching and scrolling and going-to-definition if the stored procedure names were not constant strings but were actually hard coded as magic strings in the methods themselves.

I like this approach better:

class DatabaseRepository
{
    public EmployeeList GetEmployees()
    {
        return RunCommand("GetEmployees"); //magic string
    }

    //more methods below that call the stored procedures
}

Are there any benefits that I'm not seeing to keeping stored procedure names in constants? I know that it's helpful if more than one method uses that stored procedure. That way, if the stored procedure name changes, I only need to make one change rather than multiple changes. In my experience however, because of their nature, stored procedure names rarely change.

Is it better to have stored procedure names hard coded in magic strings as opposed to kept in constant strings? I know all of the reasons why we should avoid magic strings, but I think this is an exception.

Answer 1

Short answer: you are fully correct, using constants is not the only possible solution for cases like this, and yours is probably better.

Longer answer: the whole idea of constants fulfills three purposes:

1. creating a "single source of information" in your program, one and only one place where the names are defined

2. mapping a string to something more typesafe, which the compiler can check at compile time, and the "intellisense" in your IDE can pick up

3. making it obvious where to change the code when adding new stored procedures

Given you have exactly one method in the DatabaseRepository class calling the SP, all those methods are listed one after another, and it is the only place where the SPs are called from, then all three purposes are satisfied by your suggestion. Just see this as "a way to define constants for your SP names with different means that the usual const syntax", and not as "magic strings".

Note that the real benefit of using wrapper methods instead of constants comes when your SP have parameters. Then not using constants will keep the full SP signature mapping in one place, namely that function, and not in two places (a constant at the beginning the class, and several hundred lines later a wrapper method).