SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

tSQLt – SQLCop – Checking Naming Conventions

I’ve been using tSQLt a bit to do some testing and one of the things I’ve tested is standards for code. I’ve been using a framework on top of tSQLt called SQLCop. These are a series of tests written to look for specific things. One of the items I do check is for sp_ named procedures. I’ve mostly gotten out of the habit of doing this, preferring spProcName, but at times I make a mistake in typing. This catches those simple errors.

Using SQL Cop

You can Download the SQLCop tests and install them in your database after you’ve setup tSQLt. If you are using SQL Test, then you also get the SQLCop tests installed when you add the framework to a database. For me, I see the tests in the SSMS plugin.

tsqlt7

There are a lot of tests, but in this piece, I’ll look at the Stored Procedures Named sp_ test.

If I edit the test, I see it’s fairly simple code. I’ve included it here.

USE [EncryptionPrimer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SQLCop].[test Procedures Named SP_]
AS
BEGIN
-- Written by George Mastros
-- February 25, 2012
-- http://sqlcop.lessthandot.com
-- http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/don-t-start-your-procedures-with-sp_

SET NOCOUNT ON

Declare @Output VarChar(max)
Set @Output = ''

SELECT @Output = @Output + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + Char(13) + Char(10)
From INFORMATION_SCHEMA.ROUTINES
Where SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE 'sp[_]%'
And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE '%diagram%'
AND ROUTINE_SCHEMA <> 'tSQLt'
Order By SPECIFIC_SCHEMA,SPECIFIC_NAME

If @Output > ''
Begin
Set @Output = Char(13) + Char(10)
+ 'For more information: '
+ 'http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/don-t-start-your-procedures-with-sp_'
+ Char(13) + Char(10)
+ Char(13) + Char(10)
+ @Output
EXEC tSQLt.Fail @Output
End
END;

This code looks at the meta data in the database for an routines, stored procedures, that start with sp_ as part of their name. If any results are returned from the query, the IF statement will be true and the @output will be returned as part of the tSQLt.Fail call.

Using the Test

Let’s write a stored procedure. If I do this:


CREATE PROCEDURE spLetsTestThis
AS
BEGIN

SELECT TOP 10
e.EmployeeID
, e.EmpTaxID
, e.FirstName
, e.lastname
, e.lastfour
, e.EmpIDSymKey
, e.EmpIDASymKey
, e.hashpartition
FROM
dbo.Employees AS e;

RETURN 0;
END;

GO

This is a simple procedure. I wrote it, execute it a few times and be sure it’s what I want. I’ve done basic testing, not let’s check it before I commit it to VCS.

The easy way to execute all the SQLCop tests is to right click them in SQL Test and execute them. I can also use T-SQL to run tests. However since I just want to show this one, I’ll right click it and select "Run Test".

tsqlt8

This runs the test selected. I can also run an entire class, or all tests, but clicking in the right spot. In this case, the test passes and I see a green mark.

tsqlt9

Now let’s write a new procedure:

CREATE PROCEDURE sp_GetArticles
AS
SELECT *
FROM dbo.Articles

GO

This is a bad procedure for a variety of reasons, but let’s execute my test. I see it fail, and a red mark appears next to my test.

tsqlt10

In this case I also get a window from SQL Test popping up with more details. This contains the output from the test, which is also inserted into a table by the tSQLt framework.

tsqlt11

Note that there is a URL with more information on this particular test. That is a part of the SQL Cop test code above. I could easily replace this with something particular to my environment if I chose.

At this point, I can rename the object, drop and recreate it, etc. to correct the issue. However running this test helps me to be sure I’ve gotten good code into the VCS. If I have this also run as a part of a CI process, it then prevents bad code from other developers appearing.

Meeting Standards

There are all sorts of SQLCop tests, and I’ll write about more, but this is an easy one to implement to prevent a bad practice in your coding by a team of developers. Allowing each developer to test themselves, as well as an overall check by some CI process means that our code quality improves.

If I have other standards, I can even write my own tests to enforce them, which I’ll do in another piece.

Downloads


Filed under: Blog Tagged: software development, SQL Test, syndicated, testing, tsqlt

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...