redgate SQL Source Control Revision Numbers

Anyone that’s developed with me in the last 7 years knows that I’m a HUGE fan of redgate. Honestly, now that I use their tools, I don’t know how I would work on MS SQL databases without them.

Recently, I’ve been taking a deep dive into SQL Source Control and SQL Compare. So far, I love it.

• SQL Source Control: Is a Microsoft SQL Server Management Studio plug-in which allows me to commit my database schema, static data, and migration scripts into version control.
• SQL Compare: Is a stand alone tool that allows me to compare and deploy schema changes. From a Database, Backup, Snapshot, Script Folder, or directly from source control to any combination there of.

In my case, I am using SQL Compare to deploy schema changes directly from source control, to my database.

One notable feature, is SQL Compare’s use of Extended Properties to track the version control revision number, in the database.

Here is an example of the script they use:

   1: DECLARE @RG_SC_VERSION BIGINT

   2: SET @RG_SC_VERSION = 13670

   3: IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))

   4:   EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL

   5: EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL

   6: GO

This means you can figure out the revision number of your database by querying the Extended Property.

   1: SELECT Name, Value FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL)

Why does this matter?

Well, when the time comes to update a client’s database to the latest version, it’s as easy as.

1. Figure out the version of their database (using the script above)
2. Using SQL Compare: Set your “Source” to the Head of your source control, set the “Target” to the version of the database the client is at
3. Using SQL Compare: Compare the two database schemas, and generate a deploy script

So awesome.

Using a delimited list for SQL ‘IN’ statement

Sometimes I want to pass a list of items from my C# code to my stored procedures so they can be used as part of an ‘IN’ clause.

In my greener years I would do something like:

Unsafe Way

<WARNING>Do NOT write code like this. This opens the door to SQL injection. </WARNING>

   1: -- @ProductIDs contains a list of id's delimited by a ',' (i.e. '1,34,56,7,8,98')

   2:  

   3: sp_executesql 'SELECT * FROM Products WHERE ProductID IN (' + @ProductIDs + ')'

Safer Way

My safer solution is adding a function to my database called “SPLIT.”

Function:

   1: CREATE FUNCTION [dbo].[SPLIT] (

   2:     @str_in VARCHAR(8000),

   3:     @separator VARCHAR(4) )

   4: RETURNS @strtable TABLE (strval VARCHAR(8000))

   5:  

   6: AS

   7:  

   8: BEGIN

   9:     DECLARE

  10:         @Occurrences INT,

  11:         @Counter INT,

  12:         @tmpStr VARCHAR(8000)

  13:     

  14:     SET @Counter = 0

  15:  

  16:     IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator 

  17:         SET @str_in = @str_in + @separator

  18:  

  19:     SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

  20:     SET @tmpStr = @str_in

  21:  

  22:     WHILE @Counter <= @Occurrences 

  23:     BEGIN

  24:         SET @Counter = @Counter + 1

  25:         INSERT INTO @strtable

  26:             VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

  27:  

  28:         SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

  29:  

  30:         IF DATALENGTH(@tmpStr) = 0

  31:  

  32:         BREAK

  33:     END

  34:  

  35: RETURN 

  36: END

This function takes a “str_in” parameter representing the delimited String, and separator parameter representing the delimiter of the string.

Using these two values, the delimited string is broken apart into a table that can be queried.

Usage:

   1: SELECT *

   2: FROM Products

   3: WHERE

   4: ProductID IN ( SELECT * FROM SPLIT(@ProductIDs, ','))

I’m sure there is a more efficient way to perform the same task, but it works well enough for my needs. However, I am open to suggestions.

 

Happy Coding,

Justin