Wanted: Named Parameters For Functions

Riley Major would like to be able to specify parameter names in function calls:

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.

Related Posts

Comparing System Metadata Between SQL Server Versions

Aaron Bertrand shows how he finds hidden features in new SQL Server builds: One of the areas I like to focus on is new features in SQL Server. Under both MVP and Microsoft Partner programs, I get to see a lot of builds of SQL Server that don’t make it to the public, and documentation […]

Read More

The Problems With NOLOCK

Rob Farley demonstrates the downside of the READ UNCOMMITTED isolation level: I’m going to create a table and insert exactly 1 million rows. This particular table will be a clustered index, and will contain 1 million GUIDs. 1 2 CREATE TABLE dbo.demoNOLOCK (someguid uniqueidentifier NOT NULL PRIMARY KEY); INSERT dbo.demoNOLOCK (someguid) SELECT TOP (1000000) NEWID() […]

Read More


January 2017
« Dec Feb »