Tip: Use Named Parameters with Stored Procedures when Unit Testing (VSTS Database Professionals)
I've been migrating unit tests to a new Visual Studio Database Professionals project. So far there's been no problems (other than trying to find a naming standard). This problem had me scratching my head for a few minutes today.
I defined a unit test for a stored procedure as:
SELECT TOP 1
@ItemCode = ItemCode,
@PriceLevel = PriceLevel
EXEC dbo.GetItemPrice @ItemCode, @PriceLevel
Running the unit test was resulting in the error:
Test method PricingTests.StoredProcedures.dbo_GetItemPrice threw exception: System.Data.SqlClient.SqlException: Procedure or Function 'GetItemPrice' expects parameter '@PriceLevel', which was not supplied..
Execution test script...
Sql Error: 'Procedure or Function 'GetItemPrice' expects parameter '@PriceLevel', which was not supplied.' (Severity 16, State 4).
Huh? I checked the SQL code twice. Yup, @PriceLevel is passed in as the second parameter. What's up?
So I checked the original code of the sproc:
@Area varchar(64) = NULL,
Ahhhh.... The parameters are defined in a different order. Time to swap from positional parameters to named parameters to ensure values are passed into the correct parameter
The correct way to call a stored procedure is using named parameters:
EXEC dbo.GetItemPrice @ItemCode = @ItemCode, @PriceLevel = @PriceLevel