Tip: Use Named Parameters with Stored Procedures when Unit Testing (VSTS Database Professionals)

VSTS for 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:

DECLARE @ItemCode varchar(32),
@PriceLevel varchar(32)

SELECT TOP 1
@ItemCode = ItemCode,
@PriceLevel = PriceLevel
FROM dbo.Price

EXEC dbo.GetItemPrice @ItemCode, @PriceLevel


Running the unit test was resulting in the error:

Error Message
Test method PricingTests.StoredProcedures.dbo_GetItemPrice threw exception: System.Data.SqlClient.SqlException: Procedure or Function 'GetItemPrice' expects parameter '@PriceLevel', which was not supplied..

Debug Trace
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:

CREATE PROCEDURE dbo.GetItemPrice
@ItemCode varchar(32),
@Area varchar(64) = NULL,
@PriceLevel varchar(32)
...

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



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Tip: Use Named Parameters with Stored Procedures when Unit Testing (VSTS Database Professionals) | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.