Maps VBA functions

Video Link

Watch a video demonstrating this feature.

MUST+SQL Features

Maps VBA functions (MUST+SQL/+Web)

MUST has a VBA Function Hunter, which examines your VBA code, and extracts a template for creating an equivalent function in SQL Server.

You will need to learn how to write SQL Server functions to take advantage of this feature, but MUST helps to get you started here, and creates enough of the function to allow you to upsize the SQL and later write the function body.

For example consider a simple function which takes a region and city and adds them together. The ISNULL function works like the VBA NZ function and returns an empty string when not value is supplied. The CONVERT function is required to convert the SQL_VARIANT to a VARCHAR data type to allow for the string concatenation.

Access Function

Function RegionCity(Region As Variant, City As Variant)
RegionCity = Region & " " & City
End Function

MUST VBA Function Hunting Output

CREATE FUNCTION dbo.RegionCity( @Region SQL_VARIANT, @City SQL_VARIANT)
RETURNS VARCHAR(1024)
AS
BEGIN
Return 'RegionCity'
End

Manual Body Added To Function

CREATE FUNCTION dbo.RegionCity( @Region SQL_VARIANT, @City SQL_VARIANT)
RETURNS VARCHAR(1024)
AS
BEGIN
RETURN LTRIM(ISNULL(CONVERT(VARCHAR(30),@Region),'') + ' ' + ISNULL(CONVERT(VARCHAR(30),@City) ,''))
End

CREATE FUNCTION dbo.RegionCity( @Region VARCHAR(30), @City VARCHAR(30))
RETURNS VARCHAR(1024)
AS
BEGIN
RETURN LTRIM(ISNULL(@Region,'') + ' ' + ISNULL(@City ,''))
End

Notice that we can use a VARCHAR(30) parameter and simplify the SQL, as unlike in Access where we had to use a VARIANT because the column could could contain NULL values and using a String variable would cause errors, in SQL Server this is not the case. In general AVOID SQL_VARIANT, as this tends to cause other difficulties.