Experiences teach us a lot of things – and one of them is to always take some more time when developing code to make sure it is isolated from future changes that one may make. Today’s post is based on one such incident that recently took place at the office.

How do you pass parameters to your stored procedures?

Stored procedures can be called in multiple ways, but when we talk about passing parameters to a procedure, there are essentially only two ways:

1. Using ordinal position, for e.g. EXEC proc_MyProcedure 1,2
2. Using parameter names, for e.g. EXEC proc_MyProcedure @param1 = 1, @param2 = 2

Now, we know that using ordinal positions is not a recommended best practice when using them in a query. Yet, I am quite sure that a large portion of the code currently being used production uses ordinal positions to pass parameters to a stored procedure. I will honestly admit here that I used to be one of the many who are victims of this bad practice – but all it takes is one experience to understand the importance of doing things right the first time around.

An example

Let’s take an example to understand why passing parameters using ordinal positions is not a recommended practice. We will create a simple procedure as shown below.

USE AdventureWorks2012
GO

CREATE PROCEDURE proc_GetEmployeesBySalariedFlagAndOrganizationalLevel
            @salariedFlag BIT,
            @organizationLevel TINYINT
AS
    BEGIN
        SET NOCOUNT ON

        SELECT Employee.BusinessEntityID,
               Employee.OrganizationLevel,
               Employee.SalariedFlag,
               Employee.NationalIDNumber,
               Employee.BirthDate,
               Employee.JobTitle,
               Employee.SickLeaveHours,
               Employee.VacationHours
        FROM HumanResources.Employee
        WHERE Employee.OrganizationLevel = @organizationLevel
          AND Employee.SalariedFlag = @salariedFlag
    END
GO

As you can see, our demo procedure has 2 parameters – a flag indicating whether we want salaried employees or not and the other to indicate the organizational level of the employees that we need to fetch the data for.

Passing parameters using ordinal positions

In order to execute this stored procedure, we can simply use the ordinal positions as shown below:

--Executing using ordinal positions
USE AdventureWorks2012
GO
EXEC proc_GetEmployeesBySalariedFlagAndOrganizationalLevel 0, 4

Execution of the stored procedure returns us some 187 odd records.

Passing parameters using parameter names

In order to execute the procedure using parameter names, we can use the following statement (takes more number of key strokes to write, but will reap benefits in the future):

--Executing using parameter names
USE AdventureWorks2012
GO
EXEC proc_GetEmployeesBySalariedFlagAndOrganizationalLevel @salariedFlag = 0, @organizationLevel = 4

Executing this also returns us the same 187 records.

What can go wrong?

As part of a review, assume that the architect asks the developers to swap out the two parameters – the stored procedure remains the same, but the architect believes that the organization level holds a higher priority from a business perspective and therefore requests the change.

USE AdventureWorks2012
GO

ALTER PROCEDURE proc_GetEmployeesBySalariedFlagAndOrganizationalLevel
            @organizationLevel TINYINT,
            @salariedFlag BIT
AS
    BEGIN
        SELECT Employee.BusinessEntityID,
               Employee.OrganizationLevel,
               Employee.SalariedFlag,
               Employee.NationalIDNumber,
               Employee.BirthDate,
               Employee.JobTitle,
               Employee.SickLeaveHours,
               Employee.VacationHours
        FROM HumanResources.Employee
        WHERE Employee.OrganizationLevel = @organizationLevel
          AND Employee.SalariedFlag = @salariedFlag
    END
GO

Which of the two methods to you think would continue to work after the change? Let’s run them and see:

--Executing using ordinal positions
USE AdventureWorks2012
GO
EXEC proc_GetEmployeesBySalariedFlagAndOrganizationalLevel 0, 4

--Executing using parameter names
USE AdventureWorks2012
GO
EXEC proc_GetEmployeesBySalariedFlagAndOrganizationalLevel @salariedFlag = 0, @organizationLevel = 4

Result:

The execution using ordinal positions now returns only 1 result, whereas the execution mode with the parameter names continues to return 187 records.

Conclusion

As a matter of fact, if one would observe the prepared statements being fired against a database through a data adapter or via any automated code generator, one would observe that they are always parameterized using parameter names, not using ordinal positions.

In conclusion, all I would say is that using ordinal positions can lead to troubles in case the underlying object changes sometime in the future. Named referencing and parameterization is always the safer approach, and while it takes a lot more key strokes (unwelcome with the development community), they save a lot of rework when the change is actually to be made.

This experience was enough to make me use named parameterization when making stored procedure calls always.

How do you execute your stored procedures – I am interested to know. Do share your observations/thoughts/approaches before you leave.

Until we meet next time,

Be courteous. Drive responsibly.