Now try this:DECLARE @OrderID int = NULL, @OrderType int = 1, @Qty int = 2, @ServiceSpeed int = 3; SET @OrderID = dbo.GetOrderID (@OrderType, @Qty, @ServiceSpeed); SELECT @OrderID 'Using SET Syntax';
Now you get a NULL back from the final SELECT. What happened? If you are a careful code reviewer, you might have spotted that the function definition has the @Qty and @ServiceSpeed parameters flipped as compared to the table definition and how we’re calling the function.
But this isn’t an error. There’s no obvious indication that anything is wrong. Imagine if instead of NULL, which would probably break something, you got a different order ID back. Your program would silently continue, oblivious to what is essentially data corruption.
And if you build a function with a large number of parameters, it gets that much easier accidentally to swap just two of them. Click through for the rest of the story, and check out Riley’s Connect item.