Convert VBA Code to Transact-SQL
Introduction
Most of DBAs that work with database servers will never use a local application such as MS Access to manage data, but once in a while they get to convert an existing application based on MS Access to use SQL Server. Unfortunately, most SQL Server DBAs do not have basic knowledge of VBA. Moreover, the object-oriented nature of VBA language is inherently different from the set-based nature of T-SQL. This article introduces the basics of converting VBA commands or logic to their T-SQL equivalent keywords and functions.
* If you have any needs which are not already covered in this article, then please feel free to ask in the comments to this article. If you have any idea of a solution which is not yet covered in the article please feel free to edit and add to this Wiki. Remember, this is a community shared article, which anyone can edit.
Before we start, you have to remember - Translating a logic from one technology to another is not always a good idea, as each technology has fundamental differences. For example, using the "Do Until" loop for each record in VBA can be implemented in SQL Server using a "While" loop and/or using cursor looping. But most of the time this row-by-row (sometimes called RBAR, pronounced ReeBAR) parsing of data set will be very bad idea, as SQL Server works with data-sets and can implement most loop operations without any explicit looping logic.
* This article's target audience are DBAs, and therefore it will not include in-depth explanations of the Transact-SQL equivalent solutions we're looking for, or the implementation of action on SQL Server. We will just point to the way we can implement the same VBA action using Transact-SQL language.
Converting from MS Access [VBA] to SQL Server [T-SQL]
Functions
VBA basically uses one type of method (function), those functions can return any type of element that we want, including a user defined type, a collection of elements, or a single element.
T-SQL basically uses three main functions types: (1)Scalar Functions, (2)Table-Valued Functions, and (3)System Functions.
There are sub type of those functions. For example "Scalar Functions" can be (1.1) CLR function or (1.2) TSQL Function, "Table-Valued Functions" can be (2.1) CLR function, (2.2) inline Table-Valued Function or (2.3) multi-statement Table-Valued function.
First step in converting a VBA function into T-SQL function is to understand the different T-SQL types, and to choose the right one. This link can help you to do so: http://technet.microsoft.com/en-us/library/ms191007.aspx
The next link brings us some simple examples on creating functions in T-SQL: http://technet.microsoft.com/en-us/library/ms191320.aspx
Next step is choosing the right type in T-SQL. In order to select the correct type of function, you need to answer what type will the function return? Compare the answer to the T-SQL different function's behavior. For example will it be a single element which fit an SQL Server element (like an integer, String)? If so, then we can probably use a Scalar Function. If not then maybe it returns a collection of elements, and therefore might fit to Table-Valued Functions.
On Error GoTo
SQL Server does not have anything like VBA "On Error GoTo" construct, but it is has something like "On Error Resume Next" statement + GoTo statement. Together it give you the ability to implement the same action as in VBA code.
* There is a GoTo statement in T-sql just like in VBA, but it is highly recommended NOT to use it! You can implement GoTo like this example:
GOTO Branch_Two --Jumps to the second branch & skips Branch_One.
Branch_One:
SELECT 'Select Branch One.'
Branch_Two:
SELECT 'Select Branch Two.'
Branch_Three:
SELECT 'Select Branch Three.'
* Handling Errors can be done in several ways. For example using the Variable @@ERROR, And / Or using try/catch block, as shown in these scripts:
BEGIN TRY
BEGIN TRAN
--Do Some action here
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Error occurred!'
IF XACT_STATE() <> 0
ROLLBACK TRAN
END CATCH
create table QQ (id int)
GO
DECLARE @MyError int
BEGIN TRAN
insert QQ values(1/0)
-- Divide by zero error encountered.
SELECT @MyError = @@ERROR
IF @MyError != 0 GOTO HANDLE_ERROR
-- do some work if all is OK
COMMIT TRAN -- No Errors, so we can commit our work
HANDLE_ERROR:
ROLLBACK TRAN
Select @MyError
GO
* Notice that the original error message return, but the code proceeded to capture and return the error value, and move to the HANDLE_ERROR block. This allows client applications to capture the message and return it to their caller.
* @@ERROR is reset after each and every SQL statement! Therefore, you must capture it immediately after each SQL statement that might produce an error. In the above example we insert the value to a variable in order to use it later on.
Do Until
A "do until" loop can be implement using a WHILE loop in T-SQL, as shown here:
DECLARE @QQ INT = 0
WHILE (@QQ < 10)
BEGIN
PRINT @QQ
SET @QQ = @QQ + 1
END
PRINT 'ENDED'
Working with RecordSets
Working with recordsets, record by record is a very common action in VBA, but is not recommend for most needs in T-SQL. T-SQL handles records as 'one record set element' better. SQL executes exponentially faster than a VBA recordset. It is also far more flexible, especially when working with nested sets of data.
With that said, let's see how we do implement it when it is necessary.
Working with tables, VBA code uses the element type Recordset and assigns our table records to it. For our example we will use the name "RecSet".
There are five main types of Recordset objects, which defines how we handle the records.
- Table-type [dbOpenTable]
- Dynaset-type [dbOpenDynaset]
- Snapshot-type [dbOpenSnapshot]
- Forward-only-type [dbOpenForwardOnly]
- Dynamic-type [dbOpenDynamic]
The command will look like:
Dim MyRecordset As Recordset
Set MyRecordset = RecSet.OpenRecordset (<Table_Name>,< Recordset_Type>)
* The Recordset implicitly uses cursors to navigate through the Recordset, But using VBA we control it a bit different and the cursors are distinct from the cursors used in SQL Server.
The implementation of the same behavior in T-SQL is done using a cursor element.
SQL Server use four main cursor types:
- Static cursors
- Dynamic cursors
- Forward-only cursors
- Keyset-driven cursors
A basic use of T-SQL cursor will look like:
IF OBJECT_ID('dbo.QQ', 'U') IS NOT NULL DROP TABLE QQ
create table QQ (id int)
insert QQ values(1),(2),(4)
GO
DECLARE @ID int
DECLARE MyCursor CURSOR FOR
SELECT id from QQ
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ID
FETCH NEXT FROM MyCursor INTO @ID
END
CLOSE MyCursor
DEALLOCATE MyCursor
RecSet.MoveNext
VBA command to move to the next record will be implemented in T-SQL cursor using "FETCH NEXT" command.
RecSet.EOF
VBA EOF is a marker for the End Of File, or in our case the last record in the Recordset object. There is no direct option to check if this is the last record using a cursor, but there is a way to implement it by, (1) move to the next record, and (2) check the status of the cursor. The status will be equal to zero if the FETCH statement was successful. That means that we did not get to the end and we got another record to work with. But if we try to fetch the next record and there are no more records to fetch, then the @@FETCH_STATUS will be -1.
RecSet.BOF
VBA BOF is a marker for the Beginning Of file or in our case the first record in Recordset object. There is no direct option to check if this is the first record using cursor, but we can use the same logic as checking if this is the last record. We can use "FETCH PRIOR" command to get previous record and then check the status of the cursor.
Do Until RecSet.EOF
VBA loop until we get the last record will convert into WHILE @@FETCH_STATUS = 0
For Each
Using VBA we can loop a collection of an unknown number of elements using the "for each" command. There is no equivalent for this type of loop in T-SQL but it can be implemented using a simple "For loop" or a "While Loop" and the COUNT function in order to get the number of elements.
IIF
The VBA build-in IIF function returns one of two values, depending on whether the Boolean expression evaluates to true or false. In SQL Server 2012 we have the same feature (function) which we can implement directly, but previous versions do not have an equivalent function. In this case we can implement the same behavior in several ways using IF/ELSE, or using a CASE expression, or even using ISNULL, as can be seen in the next examples:
SELECT IIF(2 > 1, 'YES','NO') -- SQL SERVER 2012 Use the same function as VBA
-- Implement IIF using ISNULL
SELECT ISNULL((SELECT 'YES' WHERE 2 > 1),'NO')
-- Implement IIF using CASE
SELECT CASE
WHEN 2 > 1 THEN 'YES'
ELSE 'NO'
END
-- Implement IIF using IF/ELSE
DECLARE @MyResult VARCHAR(10)
IF 2>1
SET @MyResult = 'YES'
ELSE SET @MyResult = 'NO'
SELECT @MyResult
Nested IIF statement
DECLARE @A INT = 2, @B INT = 1, @C INT = 3
/************** Nested IIf statement: Example 1 */
-- SQLSERVER 2012 & VBA
SELECT IIF(
@A > @B,
IIF(@C > @B,'A>B & C>B', 'A > B, but C not > B'),
'NOTHING FIT'
)
-- Implement using nested IF statement
DECLARE @MyResult VARCHAR(10)
IF @A > @B
IF @C > @B SET @MyResult = 'A>B & C>B'
ELSE SET @MyResult = 'A > B, but C not > B'
ELSE SET @MyResult = 'NO'
SELECT @MyResult
/************** Nested IIf statement: Example 2 */
-- SQL SERVER 2012 & VBA
SELECT IIF(
@A < @B,
'A < B',
IIF (@A > @B, 'A > B','A = B')
)
-- Implement using CASE
SELECT CASE
WHEN @A < @B THEN 'A < B'
WHEN @A > @B THEN 'A > B'
ELSE 'A = B'
END
-- Implement using COALESCE
SELECT COALESCE ((SELECT 'A < B' WHERE @A < @B), (SELECT 'A > B' WHERE @A > @B),(SELECT 'A = B'))
Data type's bounders
When writing code we sometimes need to bound our values, in order that SQL Server or our application (Access) will recognize they start and end. The format conventions of the boundaries which are used by the VBA language are slightly different from those of the Transact-SQL language.
Text
Using VBA, text must be enclosed in either single quotes e.g. 'Our String' or double quotes e.g. "Our String". The default implementation in T-SQL is using single quotes, depending on the settings of QUOTED_IDENTIFIER.
SET QUOTED_IDENTIFIER ON -- This is the default for SQL Server
select "fgsdgfg gsfg df gdf"
-- Using SQL Server will raise exception!! "Invalid column name 'fgsdgfg gsfg df gdf'". But this work fine on ACCESS by default.
SET QUOTED_IDENTIFIER OFF
select "fgsdgfg gsfg df gdf"
-- Work OK
Date
Using VBA, a Date should be enclosed in hash marks (#) also called pound or number signs, for example: #09/27/1950#, while implementing this to T-SQL can be done in several ways, as shown in the next examples:
-- as long as we use a date format which fit SQL Server Date's format, we can use those TSQL examples
declare @D datetime
select @D = 2014-01-16 -- value not enclosed work OK
select @D
select @D = '2014-01-16' -- value enclosed with single quotes work OK
select @D
select @D = "2014-01-16" -- value enclosed with double quotes, will work only if SET QUOTED_IDENTIFIER OFF
select @D
select @D = #2014-01-16# -- value enclosed with # will not work
select @D
Rnd Function (ACCESS Random Function)
MS ACCESS and SQL Server both have a built-in random function, but these functions behave differently. Both functions can accept 4 types of input: (1) NONE, (2) negative numbers, (3) positive numbers, (4) Zero. The input type defines the behavior of the function.
Using ACCESS we have three basic options: (A) positive numbers, and none input, behave the same. The function return next random number in the sequence. (B) negative numbers input, the function return the same number every time, using number as the seed. (C) Zero as input, the function return the most recently generated number.
Using SQL Server (T-SQL) we have two basic options: (A) using a number as input (negative, positive, or zero) the function return the same random number every time, using number as the seed. (B) using no input, return next random number in the sequence.
select Rand(-1) as 'First_1',Rand(-1) as 'Second_1'
-- return the same number every time, using number as the seed. Same as ACCESS
SELECT Rand() AS ['First_1'], Rand() AS ['Second_1'];
-- return the next random number in the sequence. Same as in ACCESS
SELECT Rand(0) AS ['First_1'], Rand(0) AS ['Second_1'];
-- return the same number every time, using number as the seed. Not as ACCESS!
select Rand(1) as 'First_1',Rand(1) as 'Second_1'
-- return the same number every time, using number as the seed. Not as ACCESS!
LAST (Access Function)
The LAST function is only supported in MS Access. We can implement the same idea of LAST(column_name), by using "ORDER BY column_name DESC" as shown in this example:
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;
First (Access Function)
The FIRST() function in access returns the first value of the selected column. In SQL Server you use "top 1" with "order by" hint, same as implementing the LAST() function, but without the "desc" in the Order by hint.
General Formatting issues
>> In Access, you can use sub-query in brackets, and there is no need to give it a name. For example "select from (select x from Table)". In SQL Server you have to use a name for each virtual SET of data:
SELECT * from (
-- subquery
select Column_Name from Table_Name
) as T
Resources
SQL Server User-Defined Functions
* http://technet.microsoft.com/en-us/library/ms191007.aspx
Create SQL Server User-defined Functions (samples)
* http://technet.microsoft.com/en-us/library/ms191320.aspx
XACT_STATE (Transact-SQL)
* http://technet.microsoft.com/en-us/library/ms189797.aspx
WHILE (Transact-SQL)
* http://technet.microsoft.com/en-us/library/ms178642.aspx
VBA Recordset Object
* http://msdn.microsoft.com/en-us/library/office/ff197799.aspx
Recordsets for Beginners" is actually a full tutorial not just for beginners! Highly recommended!
* http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners
Cursor Types (Database Engine)
* http://technet.microsoft.com/en-us/library/ms188644(v=sql.105).aspx
* http://www.dotnet-tricks.com/Tutorial/sqlserver/RLID060512-SQL-Server-Different-Types-of-Cursors.html
SQL Server Cursor Tutorials
* http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/
* http://www.dotnet-tricks.com/Tutorial/sqlserver/4L7I050512-SQL-Server-Basics-of-Cursors.html