Wednesday, April 16, 2008

10 Easy Steps to Beautiful Sql Code

Note: Shortly after I put this article together, I realized that someone else wrote something eerily similar, so I decided not to submit as an article to SQL Server Central or another content aggregator. 

Also, the web service I was using stopped working a couple days after I wrote this.  :(

Anyway, this should offer a quick and easy guide to setting up a CLR web service call from SQL Server.  Just don't bother using the web service I'm providing as an example.


This project uses a SQL Server Common-Language Runtime (CLR) Function which calls a 3rd-party Web Service to format and beautify SQL Server Stored Procedures directly from Query Analyzer. Beautiful code is usually easier to read and allows for easier maintainability.

What do I need?

I am using Microsoft SQL Server 2008 CTP 6 for this example, however it should be backwards compatible with SQL Server 2005. I also have Visual Studio 2005 installed.

Alternatives to this method include SQL Pretty Printer plugin for Visual Studio.


I do not recommend using this in a production environment or with confidential stored procedures as it calls a web service and opens the database up to potentially malicious calls. You may need to change the locations where files and databases are created. Use at your own risk.

For this demo, you will need to enable the CLR in SQL Server.

Steps to Implement a CLR in SQL Server

From SQL Server Management Studio, Query Analyzer

--Execute this statement

EXEC sp_configure 'clr enabled', 1;



When sending results to text, you may need to set the "Maximum number of characters displayed in each column" property of SQL Management Studio - Query Analyzer to a higher value than the default of 256. I have this set to 8000, but for larger stored procedures you may require more. Better yet, send the results to a file.


From the Visual Studio Command Prompt

Begin by creating the C# class library required for the solution. You may want to use NAnt, MSBuild, Visual Studio or a batch file to automate these tasks.

Step 1: Create the "wiz-dal" (WSDL) class wrapper for the Web Service

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\wsdl /o:SqlFormatterWebService.cs /n:SqlFormatterWebService.

Step 2: Create the sqlformat.cs class

Open up Notepad and save this as the following file:

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sqlformat.cs

using System; 
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlFormatterWebService.Test;
public partial class StoredProcedures
public static string FormatSql([SqlFacet(MaxSize=-1)]SqlString inputsql)
return GetFormattedSql(inputsql.ToString());
public static string GetFormattedSql(string inputsql)
SQLFormatterWebService sqlformatter = new SQLFormatterWebService();
return sqlformatter.beautifySql("tester","mssql","sql",inputsql,"");

Step 3: Execute the following from the Visual Studio Command Prompt:

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\csc /t:library sqlformat.cs sqlformatterwebservice.cs

Once the class library is created, the sqlformat.dll file will be available to add as an assembly reference in SQL Server.

From Sql Management Studio - Query Analyzer

Step 4: Create a database to store the CLR function (optional)

( NAME = N'ClrWebServices', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ClrWebServices.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
( NAME = N'ClrWebServices_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ClrWebServices_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

Step 5: Change the database owner to a non-domain service account (optional)

sp_changedbowner 'localhostname\localserviceuser

Step 6: Set database as trustworthy

There are many reasons why you would not do this in a production environment. Alternatives are in this forum posting:

USE ClrWebServices 
Step 7: Create the assembly reference for the function
FROM 'C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sqlformat.dll'

Step 8: Create the assembly reference for the function serializer

The XML Serializer is required when using a Web Service in CLR code.

CREATE ASSEMBLY [SqlFormat.XmlSerializers] FROM 'C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sqlformat.XmlSerializers.dll' WITH PERMISSION_SET = SAFE; GO 

Step 9: Create the SQL function wrapper

CREATE FUNCTION SqlFormat(@inputSql nvarchar(max)) 
RETURNS nvarchar(max)
EXTERNAL NAME SqlFormat.StoredProcedures.FormatSql

Step 10: Test the procedure against a single stored procedure

Use AdventureWorks 
set nocount on
select ClrWebServices.dbo.SqlFormat(text)
from sysobjects o
inner join syscomments c on =
where xtype='P'
and name = 'uspGetEmployeeManagers'




This article demonstrates one way of calling a web service from a CLR stored procedure, and how a web service may improve and extend the objects created inside SQL Server.



No comments: