In last
month's article, I introduced a Microsoft Access Add-In I created some
years ago, which I affectionately call the DataFast
Utility. It includes a number of analysis tools that assist with the
process of upsizing an Access application to SQL Server. If all of your queries
are simple, the utility can even create stored procedures and push them up to
your database. However, odds are your queries are not that simple and they're
going to need a little tweaking before they will meet T-SQL syntax standards.
This month's article will focus on a few of the more common conversion
scenarios you will face. While this is going to look a lot like a T-SQL
article, it is designed to help developers who are accustomed to the VBA
environment of Access to make the transition to the world of SQL Server script.
Here are the conversion topics we will address:
1. Replacements
for VBA functions; Date() and Now()
2. T-SQL
equivalents for InStr() and Mid()
3. How
to convert the IIF() function to T-SQL
4. Merciless
T-SQL string concatenation explained
5. Stored
Procedure verses View dilemma
6. Suggestions
for advanced issues: T-SQL Functions
One last point that isn't grand enough to be a topic, but deserves mention is
the DISTINCTROW keyword. This is an Access-specific SQL keyword that doesn't
exist in SQL Server. I've read the help topic and hashed out the details with
my Access developer friends, but I still can't remember the difference between
DISTINCTROW and DISTINCT. Suffice it to say, that you will need to convert any
reference of DISTINCTROW to DISTINCT if you want it to run in SQL Server. With
that out of the way, let's get on to the fun stuff.
Replacements for VBA functions; Date() and Now()
Date functions should be really easy, but I have to admit
that it's my biggest T-SQL disappointment. Maybe it's because I come from the
world of VBA, with simple, logical date functions and intelliSense (thank you
Red-Gate for SQL Prompt and Microsoft for SQL Server 2008!) Truth is, working with dates in SQL
Server is challenging for Access developers. Below is a 'cheat sheet' for some
common date methods that will hopefully ease the pain a bit.
Date() and Now()
Simply stated, you use the GETDATE() function in T-SQL to return what you get
with the Now() method in VBA. That includes the TIME portion of the date. To
get just the date portion (Date() in VBA is not so simple. You need to call GETDATE()
and convert it to another format.
You can look this up the syntax in SQL Server Books Online, but basically, the
Help system sucks. After years of reading how to use the CONVERT() function, I
only finally figured it out when a fellow developer explained the 'trick' to
me. I had always thought that the CONVERT function (see image below) required a
DateTime datatype in connection with the date style argument:
CONVERT(DateTime, GETDATE(), 101)
The last argument, 101 in my example above, determines the
date format. (See MSDN
for a full explanation of the CONVERT function.) However, this is entirely
wrong. In order for any of the Styles to be applied, you must convert the date
to a string, like this:
CONVERT(VARCHAR(10), GETDATE(), 101)
This format will give you
the results shown below. This is the T-SQL, poor-excuse-of-an-equivalent for
the awesome-and-flexible Format() function in VBA. Yes, VBA, the ugly step-child
of development has once again surpassed the robust, grown-up development
languages. Maybe SQL Server 2020 will finally provide the power and simplicity
of what Access developers have enjoyed for nearly two decades.
Am I being too harsh? Hmmm. Notice the last results column, in the screen shot
below. The TIME portion returns 6:56AM. What if you wanted a space in front of
the AM? Sure, it can be done in T-SQL, but not without some pretty verbose
string manipulation calls. VBA Rules!
For a full list of T-SQL date and time functions, see this MSDN article:
Date
and Time Functions (Transact-SQL)
T-SQL equivalents for InStr() and Mid()
I often use other VBA functions in my Access queries,
especially Trim(), Left(), InStr() and Mid(). The LEFT() function in T-SQL
works the same as that for VBA, and the Trim() function requires only a simple
adjustment. T-SQL, not being as intelligent or flexible as VBA, requires that
you trim from both sides. There is no TRIM() function, but you can do a Left
Trim and Right Trim combination that approximates the Trim() of VBA:
SELECT LTRIM(RTRIM(' Now is the time ')) AS TrimResults
The T-SQL equivalent of InStr() is the CHARINDEX() function.
The syntax for it's arguments is flip-flopped from the VBA version. The first
argument should be the string you are looking for, while the second is the string
to be interrogated. The last argument is the starting location. This takes some
getting used to, but it works pretty much the same way as its VBA counterpart:
SELECT CHARINDEX('is the', ' Now is the time ', 0) AS InStrResults
The VBA function to extract characters out of the middle of
a string, the Mid() function, is accomplished in T-SQL with the SUBSTRING()
function. If you are familiar with the Mid() function, you won't find the
syntax confusing. It works the same way, as you can see from this example.
SELECT SUBSTRING(' Now is the time ', 6, 6) AS MidResults
Below are the results as shown when run from SQL Server
Management Studio.
How to convert the IIF() function to T-SQL
The IIF() function in VBA is also called the Conditional If
because it evaluates a single condition and then branches accordingly. It might
look like this ...
IIF([ytd_sales] > 4000, "Great!", "Ok")
The T-SQL replacement for this
would be a CASE statement. The full description is available on MSDN in the
following article: CASE
(Transact-SQL). The screen shot below shows how it might be applied to the
titles table of the Pubs database. Once you get used to the syntax, you'll find
the CASE statement of T-SQL to be more powerful and flexible than nested IIF()
statements, which can get really confusing to edit.
Yes, that's right! I said something nice about T-SQL. Yes, it actually
out-shines VBA in this instance. Even a broken clock is right twice a day.
Merciless T-SQL string concatenation explained
Ok, we're not going to harp about this one for a long time,
but just enough to prepare you for what is to come, as you delve into T-SQL a
bit deeper. In Access VBA, you can concatenate all kind of things into a single
string. It wasn't unusual for me to do something like this in a query ...
ResultMsg: "You have " & [Order Count] & " orders ready to ship on " & [Ship Date] & "."
Yeah, forget about doing that in T-SQL. Just forget it! Don't
try it. It doesn't work. To concatenate strings in T-SQL, every piece of it
must be a string. Plus, you don't 'ampersand' them together ... you 'add' them
together, with the PLUS operator, like this ...
SELECT 'You have ' + CONVERT(VARCHAR(8), [Order Count]) +
' orders ready to ship on ' +
CONVERT(VARCHAR(10), [Ship Date], 1) + '.'
You have 23 orders ready to ship on 10/10/07.
You should also note the use of the single quote, instead of
a double quote. You'll get used to this too. However, on another positive note,
you can wrap the SELECT statement to the next line without a line continuation
character. T-SQL scores again!
Stored Procedure verses View dilemma
This topic is simpler yet. If you are converting an Access
MDB to link to SQL Server tables, views and procs, you really only have a
couple of choices. Stored Procedures are not updatable in an MDB. (If you have
an Access Data Project, you can set a stored proc as the updatable recordsource
of a form.) So, you use stored procs for the rowsources of combo and list
boxes, for read-only forms, and for reports. If you need to update the data,
use the table, or link to a view.
To improve performance on the read-only datasets, you should investigate the
use of Pass-Through queries. See my article in the archives, How
to Execute SQL Stored Procedures from Microsoft Access.
Suggestions for advanced issues: T-SQL Functions
The last category has to do with custom functions you may
have created in Access VBA. Perhaps you have a call that performs a calculation
or one that looks up the sales rep name. These will have to be painstakingly
reproduced as T-SQL functions. The good news is, once you migrate the logic,
probably using the techniques above, you can implement your function in pretty
much the same way it's done within Access.
For example, let's say we wanted to encapsulate the logic shown above
CREATE FUNCTION fnc_GetSalesStatus (@SalesAmount MONEY)
RETURNS NVARCHAR(8)
AS
BEGIN
DECLARE @ResultMsg NVARCHAR(8)
SELECT @ResultMsg =
CASE
WHEN @SalesAmount 7lt; 1000 THEN 'poor'
WHEN @SalesAmount BETWEEN 1000 AND 4000 THEN 'Ok'
WHEN @SalesAmount > 4000 THEN 'GREAT!'
ELSE 'unknown'
END
RETURN @ResultMsg
END
Once created, you simply call the function from your SELECT
statement, like this ...
SELECT title, dbo.fnc_GetSalesStatus(ytd_sales) As Sales_Status FROM title
You must remember to prefix the function call with the
function owner, which defaults to DBO if an owner is not specified when
creating it.
This is, of course, just a single example, and an oversimplification of the
process. Migrating the logic of your functions to T-SQL is not trivial, but it
basically follows the above template. If you take the time to migrate your
logic, you will likely be rewarded with a performance boost. It's nearly always
going to be faster to allow SQL Server to process the data, than to cobble it
in the front end, especially with complex functions that require database
lookups.
Conclusion
Whew! That was a lot and honestly, there are still more
topics that could be considered in depth when it comes to converting Access
queries to T-SQL. I hope that the examples above are sufficient to get you started.
Writing code in SQL Server can be fun, once you get the hang of it.
»
See All Articles by Columnist Danny J. Lesandrini