VBA to T-SQL via TDD: Step 5

This is Part 5 of my series on converting a VBA function to a SQL Server scalar function using test driven development.  As a proof of concept, I will be re-implementing the VBA Round function (which uses bankers rounding) as a SQL Server scalar function.

Now that we have our test data in SQL Server, we need to create a skeleton of our SQL Server scalar function.

The goal at this point is to match the data types for the inputs and outputs with those of the SQL Server table you created in Step 3.  Do not add any logic to the function yet.

VBA Round Example

As a reminder, this is the CREATE TABLE script for the SQL Server table I created in Step 3:

CREATE TABLE dbo.RoundTestData (
 ID int NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED
,NumberToRound sql_variant not null
,NumDigitsAfterDecimal int not null 
,ExpectedValue float not null
)

Let's create a skeleton scalar function to match the above fields:

CREATE FUNCTION dbo.VBA_Round(
    @OriginalVal sql_variant,
    @RoundDigits int
)
RETURNS float
AS
BEGIN
    RETURN 0
END;

After executing the above T-SQL statement ([F5]), we can verify that the function was successfully created by calling it from within a SELECT statement:

Note that this dummy version of the function returns 0 regardless of the inputs we pass to it.  This is an important part of the test-driven development process.  DO NOT be tempted to start writing the business logic for your function at this point.  Don't worry.  We'll get to that part soon.

Referenced articles