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:
- Using ordinal position, for e.g. EXEC proc_MyProcedure 1,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,
SQLTwins by Nakul Vachhrajani 