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

LISTAGG In Snowflake DB

Koen Verbeeck continues investigating Snowflake capabilities: Since SQL Server 2017, you have the STRING_AGG function, which has almost the exact same syntax as its Snowflake counterpart. There are two minor differences:– Snowflake has an optional DISTINCT– SQL Server has a default ascending sorting. If you want another sorting, you can specify one in the WITHIN GROUP clause. […]

Read More

Capturing SQLCMD Errors

Jack Vamvas shows us how we can capture errors from SQLCMD: I’m executing  code using SQLCMD from a batch file . The code points to a sql file and there is also an output file.  SQLCMD -E -S MYSERVER\INST1 -i “setup_job_entry.sql” -o “setup_job_entry.log” But I noticed that if the actual SQLCMD returns an error , […]

Read More

Categories

January 2017
MTWTFSS
« Dec Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031