• Madeira Team

Let SQL Server Write Code for You

Introduction

Many times I happen to find myself in a situation where, as a DBA, I need to write some long and cumbersome code (usually for maintenance purposes).

For example:

  1. Write a script to copy a bunch of tables from one database to another.

  2. Import the tables from one database to another database.

  3. Rebuild a bunch of indexes.

  4. Update a bunch of statistics.

  5. Write scripts for querying a lot of tables.

  6. Write scripts to backup or restore a lot of databases.

  7. And so on and so forth.

A lot of DBAs would spend hours (and even days) sitting in front of their computer and write a stupendous amount of lines of code… Then take care of a lot of copy-paste and typing errors… Test the code and find errors… Fix the code… Run it again… And so on. All the while having to navigate within a huge forest of code.

I wouldn’t think that it takes a special kind of mind to stand up and say “just hold on a minute! Why am I working so hard with this mundane task?? Why can’t SQL Server do all of this for me??” Because in fact… It can!

If you were working with SQL Server for more than a couple months, most probably you’ve heard about ‘Dynamic SQL’, right? I personally covered several topics about it in this blog of mine (namely, SQL Injection among other things).

Also, I hope you heard about the huge list of ‘system tables’ and ‘catalog views’ that SQL Server has to offer, right? And finally, you should have also heard that you can concatenate a list of values using queries, right?

If you haven’t yet, then now is your chance. Because we’re about to bring all of these elements together to make SQL Server do your mundane work for you! [cue maniacal laughter now]

Simple Concept Example: Auto-Generate Insertion Procedures

Here’s a basic scenario to demonstrate the concept for what I’m talking about.

The task goes like this:

You have a list of tables, and you need to generate a simple insertion procedure per each of the tables. Each stored procedure should receive as parameters the values for each of the table columns, except the IDENTITY column.

Alright, so first, let’s take one such table as an example and see how such a procedure should look like. This is the table we’ll use for our example:

Transact-SQL

CREATE TABLE [dbo].[Invoices]( [InvoiceID] int IDENTITY(1,1) PRIMARY KEY, [Title] nvarchar(50), [OrderDate] date, [Qty] int, [Total] money )

1

2

3

4

5

6

7

CREATE TABLE [dbo].[Invoices](

[InvoiceID] int IDENTITY(1,1) PRIMARY KEY,

[Title] nvarchar(50),

[OrderDate] date,

[Qty] int,

[Total] money

)

An insertion procedure for such a table would look like this:

Transact-SQL

CREATE PROCEDURE [InvoicesInsert] @Title nvarchar(50), @OrderDate date, @Qty int, @Total money AS INSERT INTO [dbo].[Invoices] ([Title], [OrderDate], [Qty], [Total]) VALUES(@Title, @OrderDate, @Qty, @Total)

1

2

3

4

5

6

7

8

9

CREATE PROCEDURE [InvoicesInsert]

@Title nvarchar(50),

@OrderDate date,

@Qty int,

@Total money

AS

INSERT INTO [dbo].[Invoices] ([Title], [OrderDate], [Qty], [Total])

VALUES(@Title, @OrderDate, @Qty, @Total)

All right, now let’s try and break it down and isolate the per-table dynamic parts:

Transact-SQL

CREATE PROCEDURE [{TableName}Insert] {ColumnParametersListWithTypes} AS INSERT INTO [{TableName}] ({ColumnsList}) VALUES({ColumnParametersList})

1

2

3

4

5

6

CREATE PROCEDURE [{TableName}Insert]

{ColumnParametersListWithTypes}

AS

INSERT INTO [{TableName}] ({ColumnsList})

VALUES({ColumnParametersList})

Yeah, this looks much simpler. We’ll call this our ‘template’. Now let’s review each dynamic part and see how we can generate them:

  1. {TableName} This one should be a no-brainer, seeing as we’ll be receiving this as a pre-set value.

  2. {ColumnsList} This one should be fairly simple to get. We can use the sys.columns catalog view to get the list of columns per each table. We’ll just need to concatenate them all with commas between them and we’re set. Also, the is_identity column in the sys.columns catalog view will help us determine which of the columns has an IDENTITY property.

  3. {ColumnParametersList} This one is very similar to {ColumnsList}, except we just need to add the @ symbol before each column.

  4. {ColumnParametersListWithTypes} This one is a little trickier, because it needs to include the relevant data type of each column, and not just the name. No fear, though! The sys.types catalog view, and some CASE WHEN scripting will help us here.

Right, so let’s go right in and see how we can implement this:

Transact-SQL

DECLARE @ColumnsList NVARCHAR(MAX); DECLARE @ColumnParametersList NVARCHAR(MAX); DECLARE @ColumnParametersListWithTypes NVARCHAR(MAX); SELECT @ColumnsList = ISNULL(@ColumnsList + N', ', N'') + QUOTENAME(c.name), @ColumnParametersList = ISNULL(@ColumnParametersList + N', ', N'') + '@' + REPLACE(c.name, ' ', ''), @ColumnParametersListWithTypes = ISNULL(@ColumnParametersListWithTypes + N', ', N'') + '@' + REPLACE(c.name, ' ', '') + N' ' + CASE WHEN t.name IN ('char','varchar','nchar','nvarchar','varbinary','binary') THEN t.name + '('+ CASE WHEN c.max_length=-1 THEN 'MAX' ELSE CONVERT(VARCHAR(4), CASE WHEN t.name IN ('nchar','nvarchar') THEN c.max_length/2 ELSE c.max_length END ) END + ')' WHEN t.name IN ('decimal','numeric') THEN t.name + '(' + CONVERT(VARCHAR(4),c.precision) + ',' + CONVERT(VARCHAR(4),c.Scale) + ')' ELSE t.name END FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id where c.object_id = object_id(@TableName) AND c.is_identity = 0 ORDER BY c.column_id PRINT @ColumnsList PRINT @ColumnParametersList PRINT @ColumnParametersListWithTypes

1

2

3

4

5

6

7

8

9

10

11

12

13