Reusable code

My definition of reusable code: modular code that is flexible enough that it can be used in more than one way.

For example, let's say that you have a table Orders like this:

ID int identity,
OrderDate datetime,
Quantity int

and you want the total orders for one year. Fine. All you need is something like this:

CREATE PROC Test
(
@Year int
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) = @Year

which answers the immediate need.

But if you write it like this, you can execute the sp without a parameter & get the current year:

CREATE PROC Test
(
@Year int = 0
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) =
CASE
WHEN @Year = 0
THEN YEAR( GETDATE() )
ELSE @Year END

Another approach is to add a 2nd parameter for the month, like this:

CREATE PROC Test
(
@Year int = 0,
@Month int = 0
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) =
CASE
WHEN @Year = 0
THEN YEAR( GETDATE() )
ELSE @Year END
AND
(
MONTH(OrderDate) = @Month
OR
@Month = 0
)

which allows you specify a single month, or use a zero to get the whole year.

Granted, these are very basic examples, but if you build in little hooks like these at the outset, you can save time later on.

Tally tables

I first learned about tally tables from the forums at SqlServerCentral which is great resource for information about SQL Server. (Free registration is required to view the content.)

A tally table is simply a single-column table that holds integers from 1 to any arbitrary large number. For example, this code from SSC will create a tally table of numbers from 1 to 11,000:

SELECT TOP 11000
 IDENTITY(int,1,1) AS N
INTO
 dbo.Tally
FROM
 Master.dbo.SysColumns sc1,
 Master.dbo.SysColumns sc2

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO

The benefit of a tally table is that it can be used to implement set-based solutions, thus avoiding cursors or WHILE loops.

Example 1: Let's say that you need to generate a list of all dates from 05/12/2008 to 06/02/2008 inclusive. You might use a temp table or table variable, and construct a loop that inserts records for each date in the range. By using a tally table, all you need is this (using variables to make it flexible):

DECLARE
 @start datetime,
 @end datetime

SELECT
 @start = '05/12/2008',
 @end = '06/02/2008'

SELECT
 DATEADD(d, (N - 1), @start)
FROM
 Tally
WHERE
 N BETWEEN 1 AND DATEDIFF(d, @start, @end) + 1

Example 2: You have an Orders table like this:

ID int IDENTITY (1,1,) NOT NULL,
ProductID int,
OrderDate datetime,
Quantity int

and you need the total orders for one product, for one year, by month. The basic query might look like this:

SELECT
 MONTH(o.OrderDate) AS OrderMonth,
 SUM(o.Quantity) AS Total
FROM
 Orders o
WHERE
 YEAR(o.OrderDate) = 2007
 AND ProductID = 49
GROUP BY
 MONTH(o.OrderDate)
ORDER BY
 MONTH(o.OrderDate)

However, this will not return a row for any month that did not have
an order. You can use a tally table to ensure that you have
totals for all 12 months, even for months without an order:

SELECT
 T.N AS OrderMonth,
 SUM
 (CASE
  WHEN MONTH(o.OrderDate) = T.N
  THEN o.Quantity
  ELSE 0 END
 ) AS Total
FROM
 Orders o
 CROSS JOIN Tally T
WHERE
 YEAR(o.OrderDate) = 2007
 AND T.N BETWEEN 1 AND 12
 AND ProductID = 49
GROUP BY
 T.N
ORDER BY
 T.N

There is one more example that I'd like to share, where you can
insert multiple rows from a single edit record, but it is too long
to post in the blog format, so I will assemble this post into
a Word document and add the URL when complete.

EDIT: The full article with examples is here.

SQL Lookups

I am working with an Access app that was converted to use a SQL Server database, and gradually replacing Access queries with T-SQL or stored procedures for better performance.

If you have used Access for a while, you know that the so-called Domain Aggregate functions such as DCount, DLookup, and DSum are very expensive in terms of resource usage. Even if you write your own VBA function to perform the lookup, the fact is that the Jet database engine is not efficient when looking up a single value.

My challenge was that my form needed to pull data from 3 different queries. However, after binding a query to the form to get the 1st value, using DCount for the 2nd value & then DLookup for the 3rd, the form would open quite slowly. In fact, you could observe the various controls on the form being painted as the 3 values were pulled in.

The solution was to create a stored procedure to look up the values, create a pass-thru query to execute the sp, and then open a DAO recordset in the Form_Open event & grab the values from the pass-thru query.

Each of the Access queries / lookups were re-written using T-SQL. Then we declare 3 variables, use the SQL queries to get the values, and write a SELECT statement to return the values; something like this:

DECLARE
@value_1 int,
@value_2 int,
@value_3 int

SELECT @value_1 = (first query converted to T-SQL)

SELECT @value_2 = (second query converted to T-SQL)

SELECT @value_3 = (third query converted to T-SQL)

SELECT
@value_1 AS value_1,
@value_2 AS value_2,
@value_3 AS value_3

DATEPART and DATEFIRST

I am working on a query that groups data by the week-ending date.

In MS-Access, you can use DatePart to get the day of the week ( 1 to 7 ) and then add 6 thru 0 days to your test date to calculate the week-ending date. For a normal Sunday - Saturday workweek, if your date field is TheDate you would use this:

DateAdd("d", 7 - DatePart("w", TheDate), TheDate)

...and if your workweek if different, e.g. Saturday - Friday you can use the optional argument to specify the 1st day of the week:

DateAdd("d", 7 - DatePart("w", TheDate, vbSaturday), TheDate)

Now, if you are working in SQL Server, the basic Sunday - Saturday expression is similar:

DATEADD(d, 7 - DATEPART(dw, TheDate), TheDate)

However, in T-SQL there is no optional parameter for the 1st day of the week - you would need to do this:

SET DATEFIRST 6

SELECT
DATEADD(d, 7 - DATEPART(dw, TheDate), TheDate)
FROM TheTable

...but the DATEFIRST is a global setting, so it might affect other code that depends on the default setting.

One possible solution is this:

IF @@DATEFIRST != 7
SET DATEFIRST 7

SELECT
DATEADD(d, 6 - DATEPART(dw, TheDate), TheDate)
FROM TheTable

...which forces the 1st day to the default, and then uses 6 instead of 7 to calc the offset days. This will work in a stored procedure, but not in a view.

OTOH if you are certain that the DATEFIRST never varies from 7, you could simply use

DATEADD(d, 6 - DATEPART(dw, TheDate), TheDate)

which does give the correct result for the Saturday - Friday workweek & can be used in sp's and views.

SSMS - INSERT script

SSMS allows you to quickly generate table scripts, such as INSERT, SELECT, and UPDATE, which can save lots of time when writing T-SQL.

However, I noticed that if you have an IDENTITY column the INSERT script will leave that out.

Granted, most of the time you won't be inserting a value into that column, and in fact you need SET IDENTITY_INSERT MyTable ON if you do wish to insert into that column. But I would prefer to see that column in the script, so I can remove it myself according to my needs.

SQL script encoding

During development work with SQL Server, I will generally write a series of change scripts that need to be applied in a specific order. For example, one script might add a new table and another creates a stored procedure that uses that new table. Therefore, when I name the scripts I will prefix them with a sequential number e.g. 01_tblCustomer.sql, 02_prcGetCustomer.sql and so forth. This has several advantages: each script can be written & tested separately; the scripts can be applied in the correct order; the dependencies are recorded correctly in SQL Server.

Originally I would pass the group of scripts to the DBA to be applied to the production database, but as the number of scripts grew the chance for error grew as well.

I wrote a small VBA program to loop thru the script folder & build up a change script, so the DBA only has to apply a single large script. This worked quite well when each individual script was started from a blank query window, but when I generated a Modify script using SSMS there were extra characters at the beginning of the SSMS-generated script; specifically there was an ASCII 255, 254 at the beginning of the first line.

It turns out that when you start a script from a new query window & save it, the default encoding is

Western European (Windows) - Codepage 1252

which corresponds to ANSI or plain text. However, when you use SSMS to generate a script, the default encoding is

Unicode - Codepage 1200

which is the reason that VBA was reading the extra characters from the top of the script file.

So then, when saving the SSMS-generated scripts, all I had to do was to use the Save button option Save with Encoding... and select the 1252 encoding.

Pass-Thru Solutions

Six months ago we converted an Access 2003 application to use MSSQL 2005 as the database, and are gradually converting reports to use pass-thru queries, with either T-SQL or stored procedures, to take advantage of the speed & power of the SQL Server.

There are many complex reports, some of which have embedded summary subreports, and therein lies the snag - Access does not allow you to use a stored procedure for subreports.

The main reports that have user criteria are easy - create a stored procedure that accepts parameter(s) and the pass-thru query is re-written in the button click event. So the VBA code for that might look like this:

CurrentDB.QueryDefs("MyQuery").SQL =
"EXEC dbo.prcMyQuery '" & Forms!MyForm!MyDate & "' "

and the query executes this:

EXEC dbo.prcMyProcedure '07/19/2009'

thus returning the desired records.

But what to do about the subreport? That query is often far more complex than the main query, since it provides a summary with aggregates. . .

The solution is to create a View, link to the view (which Access treats as an attached table) and then create a simple Access query to apply the filter criteria. So if the view is attached as

viwMyView

then the Access query might look like this:

SELECT viwMyView.*
WHERE viwMyView.DateField = Forms!MyForm!MyDate

which then applies the same filter to the subreport, that is applied to the main report. We still get the power of SQL Server to build up the summary data, and the number of rows is small enough that the Access query overlay does not impact the performance.

Using IF EXISTS

When I work with stored procedures, I will always test if the procedure exists, drop if it does exist, and then create the stored procedure. This produces nice clean code which can be saved to source control & run at any time as needed. Note that the create code includes the grant permissions.

I had been using "the long version" of the IF EXISTS statement, for example:

IF EXISTS (
SELECT * FROM sys.sysobjects
WHERE [name] = 'TESTME' AND [type] = N'P'
)

This does work just fine, but MSSQL does not allow more than one schema-scoped object (table, view, sp, udf) with the same name. So, testing for the type is not necessary. So we could use just this:

IF EXISTS (
SELECT * FROM sys.sysobjects
WHERE [name] = 'TESTME'
)

However, there is a unique ID for every object & a built-in function OBJECT_ID() to get that ID, so this is all we need to test if an object exists:

IF OBJECT_ID('TESTME') IS NOT NULL

External Change Tracking

This article relates to my previous post that describes a change-tracking system for MS-Access. You can view the complete code with comments here: Change Tracking in Access

In a very active database, the tracking table can become quite large, so here are two methods to move the tracking table to a separate file.

Remember - always make a backup copy before making significant changes to your database.

Method 1

Create a new blank database.
Import the tracking table from the original file.
Delete the tracking table from the original file.
In the original file, create a link to the table in the new file.

...all of the above can be done using the Access GUI.

Method 2

This module will create a new file in the same folder as the original file, move the tracking table and link to it. You would run this only one time...

Sub External_Change_Tracker()
Const MyTable = "tbl__ChangeTracker"
'
Dim dst As DAO.Database
Dim MyNewFile As String
Dim src As DAO.Database
Dim tdf As DAO.TableDef
'
On Error GoTo err_sub
'
' create new db
MyNewFile = CurrentProject.Path & "\Change_Tracker.mdb"
Set dst = CreateDatabase(MyNewFile, dbLangGeneral)
' copy the tracking table
DoCmd.CopyObject dst.Name, MyTable, acTable, MyTable
' drop local tracking table
DoCmd.DeleteObject acTable, MyTable
' link to table in external file
Set src = CurrentDb
Set tdf = src.CreateTableDef(MyTable)
With tdf
.Connect = ";DATABASE=" & dst.Name
.SourceTableName = MyTable
End With
src.TableDefs.Append tdf
exit_sub:
Exit Sub
'
err_sub:
MsgBox Err.Description, vbCritical, "Error # " & Err.Number
Resume exit_sub
'
End Sub

You can view the complete code here: External_Change_Tracker

Whether you use method 1 or 2, you will still need to perform a Compact & Repair to reclaim the space used by the deleted table.

Abort SQL batch

I am working in an environment that has three databases:

database ABC on server ABC - the production system

database ABC_QA on server ABC2 - for QA testing

database ABC_DEV on server ABC3 - for development

The developer writes scripts to create / alter objects & manipulate data during development on ABC_DEV, and then sends them to the DBA to run against ABC_QA for QA testing. When QA is complete, the developer prepares a final script for the DBA to run against ABC. In both cases, the "script" file is a compilation of multiple individual scripts, each of which ends with a GO statement.

A problem arose when the DBA ran an intermediate QA script against production - the script does not include a USE statement; but even if it did, and that failed, the script would continue to run, and any create object statements would execute against whatever the current database connection was (usually master). Not good.

I did some research on RAISERROR and found that a severity level of 20 of higher will actually close the database connection, and thus abort the entire script, preventing any extra objects from being created. Adding the following to the beginning of the script takes care of this:

IF NOT EXISTS
(SELECT 1
FROM sys.sysdatabases
WHERE [name] = 'ABC')
RAISERROR ('INCORRECT DATABASE', 21, 1) WITH LOG

which yields the following message to the DBA:

Msg 2745, Level 16, State 2, Line 5
Process ID 57 has raised user error 50000, severity 21. SQL Server is terminating this process.
Msg 50000, Level 21, State 1, Line 5
INCORRECT DATABASE
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

...and the rest of the script is aborted without any further processing.