Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

(UPDATE: If you are using SQL Server 2005 or above, there is a much easier and more efficient way to page your data.)

Here is a completely dynamic version of my paging technique in the form of a stored procedure.  Just pass in a short SQL statement, the Order By clause, and the start row and end row you’d like to return in the resultset.  The stored procedure then will process your SQL in the order specified and return only the rows indicated.  It is based on the same principals I presented in my blog several months ago, in which the paging technique is described.

The basic idea is to process as few rows as possible; this means finding the starting point, and then returning all rows “past“ that starting point until the desired number of rows has been returned.

The stored procedure is called “ReturnPage“ and uses the following arguments:

• @Select = the select statement to return
• @OrderBy = the order by clause; don’t include the “ORDER BY“ part, just the columns.  You must include ASC or DESC for each column in the sort
• @StartRow = the first row to return
• @EndRow = the end row to return

A minor tweak in the code could allow for a @RowCount argument instead of @EndRow if you wish.

Let’s start with some examples from northwind:

returnpage ’select contactTitle, City, CustomerID from customers’, ‘ContactTitle ASC, City DESC, CustomerID ASC’, 1, 10

returnpage ’select * from Orders’,'EmployeeID ASC, OrderDate DESC, OrderID ASC’,12,31

returnpage ’select * from [order details]‘,’productID ASC, Quantity DESC, OrderID asc’,30,45

And a couple of caveats / notes about this procedure:

• First off, it is still in beta testing so use at your own risk.  Please give me some feedback !
• As written, the ASC/DESC designation is required for each column in the @OrderBy argument, unlike in a regular SELECT, in which ASC is the default
• Currently, all column names in the @OrderBy argument must consist of only 1 word; even using [ ] around a column name will not work.  I didn’t feel like spending too much time on the parsing routine, but feel free to improve upon it in your own implementation.
• Feel free to print out the @SQL statement generated by the routine to see how it works
• As a reminder, a requirement of my algorithm is that the sort columns must form a unique constraint for the resultset; you cannot sort only by “Customer Name“ if it is not always unique for each row — you would need to include “CustomerID“  as a secondary sort.  Notice how I have done this in all of the Northwind examples.
• There should not be any Null values in the @OrderBy columns for this technique to work

I hope you enjoy and it works fine for you.  It might seem like this routine is doing a lot of work to parse the arguments and generate the T-SQL to execute on each call, but it is all done on the server and the parsing is extemely quick since it requires no I/O or database access — it’s all just in-memory manipulation of variables. 

Anway, like the other paging implementations, it’s not perfect, but it is truly a 100% server side implementation and it will process as few rows as theoretically possible for a dynamically sortable paging solution.

Final note: if anyone is interested, please let me know and I can provide some more information about how this procedure was written and how it works.  To keep the code somewhat compact, I’m sure it’s not very clear.  The algorithm to generate the WHERE clause was one of the more challenging ones I’ve written recently.

The original post describing the paging technique can be found here: http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

 

---

 

 

CREATE PROCEDURE ReturnPage(@Select varchar(1000), @OrderBy varchar(1000),

                            @StartRow int, @EndRow int)

AS

BEGIN

 

declare @ColList varchar(2000);

declare @Where varchar(2000);

declare @i int; 

declare @i2 int;

declare @tmp varchar(1000);

declare @dec varchar(1000);

declare @f varchar(100);

declare @d varchar(100);

declare @Symbol char(2);

declare @SQL varchar(5000);

declare @Sort varchar(1000);

 

set @Sort = @OrderBy + ‘, ‘

set @dec = ”

set @Where  = ”

set @SQL = ”

 

set @i = charindex(‘,’ , @Sort)

while @i != 0

 begin

  set @tmp = left(@Sort,@i-1)

  set @i2 = charindex(‘ ‘, @tmp)

  set @f = ltrim(rtrim(left(@tmp,@i2-1)))

  set @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))

  set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))

  set @i = charindex(‘,’, @Sort)

  set @symbol = case when @d = ‘ASC’ then ‘>’ else ‘<’ end +

                case when @i=0 then ‘=’ else ” end

 

  set @dec = @dec + ‘declare @’ + @f + ‘ sql_variant; ‘

  set @ColList = isnull(replace(replace(@colList,‘>’,‘=’),‘<’,‘=’) + ‘ and ‘,”) +

                 @f + @Symbol + ‘ @’ + @f

  set @Where = @Where + ‘ OR (‘ + @ColList + ‘) ‘

  set @SQL = @SQL + ‘, @’ + @f + ‘= ‘ + @f

 end

 

set @SQL = @dec + ‘ ‘ +

           ‘SET ROWCOUNT ‘ + convert(varchar(10), @StartRow) + ‘; ‘ +

           ‘SELECT ‘ + substring(@SQL,3,7000) + ‘ from (‘ + @Select + ‘) a ORDER BY ‘ +

           @OrderBy + ‘; ‘ + ‘SET ROWCOUNT ‘ +

           convert(varchar(10), 1 + @EndRow – @StartRow) + ‘; ‘ +

           ’select * from (‘ + @Select + ‘) a WHERE ‘ +

           substring(@Where,4,7000) + ‘ ORDER BY ‘ + @OrderBy + ‘; SET ROWCOUNT 0;’

 

exec(@SQL)

END

Introduction to Dynamic SQL (Part 2)

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2

In a follow up to the Introduction to Dynamic SQL we take you through some of the tricks and pitfalls of this technique. We’ll cover Create Table, sp_executesql, permissions, scope and more.

<!–

---

–>

Welcome to Part 2 of my Introduction to Dynamic SQL.

In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.

The IN Clause

The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.

If in an asp page you have a Select list with multiple allowed values, the value of request.form(“myList”) on the processing page might look like this “1,3,4,6″.

So we try to write a stored proc around this

Create Procedure Search
	@strIDs VarChar(100)
AS

SELECT *
FROM
	Products
WHERE
	ProductID in (@strIDs)

GO

Oooops! No Go.

This will work

Create Procedure Search
	@strIDs VarChar(100)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'

Exec ( @SQL)

GO

N.B. This can also be solved using a technique like this.

Aliases

Giving a table or column a dynamic alias is a use for dynamic SQL.

This will not work

Select UserName FROM Table as @Alias

This will

Exec('Select UserName FROM Table as ' @Alias)

DDL

A common question asked of SQL Team is “How do I write a stored procedure that will create a table/database. I want to pass in the name”

SQL Server will not allow this

Create Table @TableName (
	ID int NOT NULL Primary Key,
	FieldName VarChar(10)
	)

Once again, dynamic SQL to the rescue

Declare @SQL VarChar(1000)

SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)

Similarly, the code to create a database would look like this:

Exec('Create Database ' + @myDBName)

sp_executesql

sp_executesql is a system stored procedure that you can use in place of “exec” to execute your dynamic sql.

This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.

An example

Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'

Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'

The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.

sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it’s database, rather than the one it was called from.

Try this from a database that is not Pubs

Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)

You will get this error: ‘CREATE VIEW’ does not allow specifying the database name as a prefix to the object name.

So you build the dynamic sql, then run it in Pub’s copy of sp_executesql

I.E.

Declare @SQL nVarChar(1000)

Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'

Execute pubs.dbo.sp_executesql @sql

Permissions

When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.

Scope

When you run dynamic sql, it runs in it’s own scope.

This

exec('set rowcount 3')

Select * from Authors

exec('set rowcount 0')

Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.

This would be solved by this

exec('set rowcount 3 Select * from Authors Set rowcount 0')

Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.

declare @i int
Exec ('Select @i = 1')

Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.

Create Table #tempauth(
	au_id VarChar(100),
	au_fname VarChar(100),
	au_lname VarChar(100)

)

declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)

Select * from #tempauth

drop table #tempauth

Summary

That wraps up my intro to dynamic SQL. I hope it is a little clearer now than it was before. Dynamic SQL is a very powerful tool to have in your arsenal as long as it doesn’t substitute for bad application design. If you can avoid it with better data modelling, then that is the best way to go as your code will end up neater and generally faster.

Until next time

Happy coding.

Introduction to Dynamic SQL (Part 1)

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

One of the most common questions we get involves Dynamic SQL. We have some articles that cover it but none that really start with the basics. So Merkin sat down and wrote this introduction to dynamic SQL. Using dynamic SQL you can put a SQL statement inside a variable and execute that statement. It’s what you have to do when you’re trying to run Select * from @TableName. Thanks Merkin!

<!–

---

–>

Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.

In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.

Dynamic SQL on the client

If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:

dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = "
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)

or slightly more elaborate

dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)

Or for a generic table viewer

dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)

In each case, you are building your sql statement as a string, then executing that statement against an active database connection.

Dynamic SQL in a stored procedure

Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.

Create Procedure GetArticle
	@ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
	Articles
WHERE
	ArticleID = @ArticleID

GO

However, SQL Server doesn’t like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:

Create Procedure GenericTableSelect
	@TableName VarChar(100)
AS
SELECT *
FROM @TableName

GO

You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.

Create Procedure GenericTableSelect
	@TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM '
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO

Try that. That should do it.

The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.

The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.

The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.

That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.

Until then have fun.

Implementing a Dynamic WHERE Clause

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

Garth Wells passed on another article to us. This one covers building a dynamic where clause. And the really neat part is that you don’t actually need to write dynamic SQL. This is soooo much simpler.

<!–

---

–>

One of my first Internet-related projects was a data-driven web site for a commercial real estate firm. One of the features they wanted the site to support was an interface that allowed potential customers (tenants) to search for properties using one or more search criteria. At the time, the only way I knew how to create a dynamic WHERE clause was by using dynamic SQL. With dynamic SQL, you build the SELECT statement based on the search criteria supplied.Let’s look at an example that shows how this works. Assume you have a table called Customers created with the following statement.

CREATE TABLE Customers
(
 Cus_ID int PRIMARY KEY IDENTITY,
 Cus_Name varchar(30) NOT NULL,
 Cus_City varchar(30) NOT NULL,
 Cus_Country varchar(30) NOT NULL
)

The users want to query the table on the Cus_Name, Cus_City and Cus_Country columns independently or in combination. In other words, they want to be able to specify none, one, or more than one criteria and have the resultset filtered accordingly. Using dynamic SQL, the statements (partial) needed to create the query are shown here.

DECLARE @SQL varchar(1000)

SET @SQL = 'SELECT Cus_Name, Cus_City, Cus_Country FROM Customers '

IF @Cus_Name IS NOT NULL OR @Cus_City IS NOT NULL OR _
    @Cus_Country IS NOT NULL
 SET @SQL = @SQL + 'WHERE '

IF @Cus_Name IS NOT NULL
 SET @SQL = @SQL + 'Cus_Name = ' + @Cus_Name
...
EXEC(@SQL)

The final SELECT is a function of the variables used to store the criteria specified by the users and is executed with the EXEC statement.

The dynamic SQL approach will certainly work, but it has two downsides. The first is that it is cumbersome to implement. Building SQL statements in this manner is an error-prone endeavor that takes a lot of time to get right. The second downside has to do with query performance speed. It is impossible for SQL Server’s query processor to re-use execution plans produced by dynamic SQL statements. An execution plan is how the database engine actually retrieves the data from the database. When static SQL is used, execution plans can be re-used by different calls to the same statement. This results in faster query processing time because one less step is required to process the query.

The COALESCE Function

A more efficient approach to creating dynamic WHERE clauses involves using the COALESCE function. This function returns the first non-null expression in its expression list. The following example shows how it works.

DECLARE @Exp1 varchar(30),
        @Exp2 varchar(30)

SET @Exp1 = NULL
SET @Exp2 = 'SQL Server'

SELECT COALESCE(@Exp1,@Exp2)

--Results--

------------------------------
SQL Server

The function processes the expression list from left-to-right and returns the first non-null value. The COALESCE function can process an infinite number of expressions (e.g., COALESCE(@Exp1,@Exp2,@Exp3,@Exp4,…)), but for the example presented in this article only two are needed.

Now that you know how COALESCE works, lets see how it is used to produce an efficient SELECT that supports a multi-parameter search. Each comparison operation in the WHERE clause must be modified to handle NULL values. The following shows the modified statement.

SELECT Cus_Name,
       Cus_City,
       Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)

The COALESCE function will return the first non-null value, so when a value is provided for a parameter it is used in the comparison operation. When a value is not supplied for a parameter, the current column value is used. A column value always equals itself, which causes all the rows to be returned for that operation.

Let’s add some data to the Customers table with the following INSERTs so we can test the statement.

INSERT Customers VALUES ('Acme','Paris','USA')
INSERT Customers VALUES ('Bouvier','Paris','France')
INSERT Customers VALUES ('Acme','Houston','USA')

The following shows how to return all the rows with Cus_City value equal to ‘Paris.’

DECLARE @Cus_Name varchar(30),
                  @Cus_City varchar(30),
                  @Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

SELECT Cus_Name,
               Cus_City,
               Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)

--Results--

Cus_Name                       Cus_City        Cus_Country
------------------------------ --------------- ---------------
Acme                           Paris           USA
Bouvier                        Paris           France

Wrapping the Statement in a Stored Procedure

The most efficient way to implement this type of search is with a stored procedure. The statement shown here creates a procedure that accepts the required parameters. When a parameter value is not supplied it is set to NULL.

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry
@Cus_Name varchar(30) = NULL,
@Cus_City varchar(30) = NULL,
@Cus_Country varchar(30) =NULL
AS
SELECT Cus_Name,
       Cus_City,
       Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)

The following shows that you can call the procedure with zero, one or more parameters to retrieve the desired resultset.

–Example 1

EXEC ps_Customers_SELECT_NameCityCountry

--Results--

Cus_Name                       Cus_City        Cus_Country
------------------------------ --------------- ---------------
Acme                           Paris           USA
Bouvier                        Paris           France
Acme                           Houston         USA

–Example 2

EXEC ps_Customers_SELECT_NameCityCountry @Cus_Country = 'USA'

--Results--

Cus_Name                       Cus_City        Cus_Country
------------------------------ --------------- ---------------
Acme                           Paris           USA
Acme                           Houston         USA

–Example 3

EXEC ps_Customers_SELECT_NameCityCountry @Cus_Name = 'Acme',
                                         @Cus_City = 'Houston'
--Results--

Cus_Name                       Cus_City        Cus_Country
------------------------------ --------------- ---------------
Acme                           Houston         USA

Dynamic Cross-Tabs/Pivot Tables

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

IMHO, the best feature of MS Access is the TRANSFORM statement, used to create cross-tabs/pivot tables. It does all of the work of dynamically generating the cross-tabulation and the summary calculations. T-SQL unfortunately doesn’t have this statement, so you’re stuck using complicated SQL commands, expensive 3rd party products, or exotic OLAP to make pivot tables…or you can use the following procedure to dynamically create them!

<!–

---

–>

I got the idea from this question, asking how to “undo” a pivot table, and then I started working on how to create them in T-SQL. There are numerous ways of doing pivot tables, and this site has several examples (and lots of other cool stuff). The standard method uses a CASE statement, with one CASE for each pivot value (the column headings created by cross-tabbing the pivot column). The greatest shortcoming is finding a way to handle an unknown or changing number of pivot values. Obviously you have to know these values beforehand, and you must add a CASE for each new, distinct value inserted into the pivot column. The code listed below will do all of the work for you:

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

Yeah, I know, you want to know how it works I won’t go into detail on the mechanics of the code. Let’s just say that if you can follow it, feel free to play with it; if not, DON’T TOUCH IT! The syntax for the procedure call is below, followed by a description of each parameter:

	1	2	3	4
EXECUTE crosstab	SELECT statement,	summary calculation,	pivot column,	table name

1. The SELECT statement can be anything, as long as it has proper syntax and includes a GROUP BY clause. You can use JOINs, but if you use table aliases you should include the alias in the summary calculation expression (2). 
2. The summary calculation must have an aggregate function like SUM(), AVG(), MIN(), MAX(), etc. You’d have to modify the code if you want to use DISTINCT with these functions. COUNT(*) won’t work, you have to COUNT on a column. 
3. The pivot column must be in the table (4). You can use an expression for the pivot column (a+b, LEFT(FirstName,3), etc.) as long as it can be derived from the table listed in (4). A cross-tab heading will be created for each distinct value in the pivot colum/expression. 
4. This table can be any table in your database, or another database if you use the full naming syntax (database.owner.table). Tables in a linked server may also work, but I haven’t tested this. It’s possible that a derived table (nested SELECT) can work, but I haven’t tested this either. You would need to enclose the SELECT statement in parentheses, and use a table alias outside these parentheses, like this: ‘(SELECT LastName FROM myTable) AS Surnames’

I’ll list some cross-tab settings and the results. Here’s two you can run in the pubs database:

EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title', 'sum(qty)','stor_id','stores'

title	6380	7066	7067	7131	7896	8042
But Is It User Friendly?						30
Computer Phobic AND Non-Phobic Individuals: Behavior Variations				20
Cooking with Computers: Surreptitious Balance Sheets						25
Emotional Security: A New Algorithm				25
Fifty Years in Buckingham Palace Kitchens			20
Is Anger the Enemy?	3	75	10	20
Life Without Fear				25
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean			40
Prolonged Data Deprivation: Four Case Studies				15
Secrets of Silicon Valley		50
Silicon Valley Gastronomic Treats					10
Straight Talk About Computers					15
Sushi, Anyone?			20
The Busy Executive’s Database Guide	5					10
The Gourmet Microwave				25		15
You Can Combat Computer Stress!					35

EXECUTE crosstab 'select pub_name, count(qty) as orders, sum(qty) as total
from sales inner join titles on (sales.title_id=titles.title_id)
right join publishers on (publishers.pub_id=titles.pub_id)
group by pub_name', 'sum(qty)','type','titles'

pub_name	orders	total	business	mod_cook	popular_comp	psychology	trad_cook	UNDECIDED
Algodata Infosystems	6	135	55		80
Binnet & Hardley	7	150		50		20	80
Five Lakes Publishing	0
GGG&G	0
Lucerne Publishing	0
New Moon Books	8	208	35			173
Ramona Publishers	0
Scootney Books	0

Here’s one that will run in Northwind:

EXECUTE crosstab 'SELECT LastName FROM Employees INNER JOIN Orders
ON (Employees.EmployeeID=Orders.EmployeeID)
GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'

LastName	1996	1997	1998
Buchanan	11	18	13
Leverling	18	71	38
Fuller	16	41	39
Peacock	31	81	44
Callahan	19	54	31
Suyama	15	33	19
King	11	36	25
Dodsworth	5	19	19
Davolio	26	55	42

Some things to look out for:

• This procedure builds a SQL statement from your parameters, inserts the CASE statements to handle the cross-tabbing, and then executes it. The size of this statement is maxed out at 8000 characters, so there’s a limit on the size and complexity of the SELECT statement and the number of pivoted values you can have. 
• This procedure uses a global temporary table, and will cause an error if two sessions execute it at the same time. (thanks for catching this Garth!) I will have a remedy soon and will post the fixed code in this article. 
• Date values can be pivoted, but remember that the timestamp portion will be included, so you should CONVERT() them in the pivot expression. NULLs can’t be pivoted, you must use IsNull() to replace them with a value. I have not tested nvarchar or nchar data types, and the code may need to be modified to work with them. Nor have I tried ntext or text types, but I’m sure they can’t be pivoted anyway. 
• You can use a HAVING clause, or include WITH CUBE or WITH ROLLUP in the SELECT statement and it will return the appropriate summary rows for each group. 
• You can even ORDER BY cross-tabbed columns, something you can’t do with the TRANSFORM statement in Access.

I’m spending some time working on enhancements to this procedure (for a follow-up article), to allow things like subqueries, multiple pivoting columns, non-aggregate values (e.g. – show the store with the highest sales each month, pivoted by book type). I would love to hear from anyone who has suggestions or solutions on how to improve this code.

Enjoy!

Tony Rogerson’s ramblings on SQL Server

http://sqlblogcasts.com/blogs/tonyrogerson/default.aspx

I’ve been in IT since 1986 starting my career on the IBM mainframe programming in such languages as Application System, PL/1, CICS, DB2, System W. Around 1994 I moved into the client/server environment using Visual Basic and C++, Oracle at first and then Microsoft SQL Server, I now code using VB.NET and C# but am really SQL Server focused.

I’ve been an independent consultant since 1999 based in Harpenden, Hertfordshire. I’ve built long standing relationships with many clients from a Research Fellow right the way through to enterprise covering a lot of business areas, Banking (Retail, Investment), Retail, Knowledge Management, Manufacturing, Motor Racing, Web Industries and a ton more I don’t even know how to classify!

In 1998 I founded the UK SQL Server Community (http://sqlserverfaq.com) – I still run this in my spare time, presenting in the Community, doing online webcasts etc…

If you need anything SQL Server centric then give me a call for an initial free chat on your problem, my rates are reasonable and costed such to be affordable for an ongoing relationship which many of my clients do.

If you would like to contact me the email me at tonyrogerson@torver.net or call me on +44(0)1582 346161.

Passing an array of values to SQL Server (Stored Procedure) without parsing/string manipulation

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/18/passing-an-array-of-values-to-sql-server-stored-procedure-without-parsing-string-manipulation.aspx

If you are splitting a CSV that just contains numbers, for instance surrogate key id’s that you are passing in because of a multiple select checkbox or something and you know the values range then why split the string? It’s simpler than that – no splitting required, just use dynamic SQL and an IN coupled with a numbers table…

Create the numbers table, note – if you are just passing in the surrogate key id of say your individual table then you don’t even need a numbers table – just use the id column on the individual table…

create table numbers (

      number      int    not null primary key clustered

 

)

 

declare @i int

 

set @i = 1

 

while @i <= 32767

begin

      insert numbers ( number ) values( @i )

      set @i = @i + 1

 

end

go

 

create proc csv_to_set

      @csv varchar(max)

as

begin

      set @csv = replace( @csv, ””, ””” )

 

      if object_id( ‘tempdb..#csvset’ ) is null

      begin

            print ‘#csvset must already exist before this proc is called’

–          create table #csvset (

–                csv_value smallint not null

–                )

      end

      else

      begin

            insert #csvset ( csv_value )

                  exec( ‘

                              select number

                              from numbers

                              where number in ( ‘ + @csv + ‘ )

                              ‘ )

 

            if @@rowcount <> ( LEN( @csv + ‘,’ ) - LEN( REPLACE( @csv + ‘,’, ‘,’, ” ) ) )

            begin

                  print cast( ( LEN( @csv + ‘,’ ) - LEN( REPLACE( @csv + ‘,’, ‘,’, ” ) ) ) as varchar(50) )

 

                  raiserror( ‘A value exists in the CSV that is not in the numbers table’, 16, 1 )

                  truncate table #csvset

 

                  return

 

            end

 

      end

end

 

The procedure is run using the example below, unfortunately because you can’t put dynamic SQL in a function then you can only do this in a stored procedure (a table valued function would remove the need for the # table).

I’ve also put a check in there that compares the @@ROWCOUNT (inserted rows) against how many values are in the CSV, if there is a mismatch then an error is thrown, but by doing this check you also prevent duplicate values being entered on the @csv, basically the IN clause does an implicit SELECT DISTINCT on your @csv removing the duplicate values – this is a bad thing if you are entering data and can have duplicates but it’s a good thing if you just want the unique values.

–    Create results table

create table #csvset (

      csv_value   smallint not null

      )

 

–    Split the string up

exec csv_to_set ‘55, 99, 1212′

 

–    Results

select *

from #csvset

 

drop table #csvset

Filed under: SQL Server

DATABASE JOURNAL – Featured Database Articles

http://www.databasejournal.com/features/mssql/archives.php

Archives
Configuring Transport Encryption in SQL Server 2005 Express Service Broker Conversation – 11/24/2008
Combine BottomCount() with Other MDX Functions to Add Sophistication – 11/21/2008
Microsoft Windows PowerShell and SQL Server 2008 AMO – 11/18/2008
SQL Server: Measuring Space Allocation and Index Distribution – 11/14/2008
Installing a Two-node SQL Server 2008 Cluster – Advanced option – 11/12/2008
Establishing Distributed SQL Server Express’ Service Broker Conversations Using Certificate-based Authentication – 11/10/2008
SQL Server 2008 Recovery Models and Backups – 11/07/2008
Using Windows PowerShell to get SQL Server connection information – 11/05/2008
Attribute Member Values in Analysis Services – 11/04/2008
Introducing Reporting Services Charts for Analysis Services – 10/29/2008
Reports for SQL Server 2008 System Data Collections – 10/27/2008
Exploring SQL 2005’s Ranking Functions – NTILE() and ROW_NUMBER() – 10/24/2008
Configuring Certificate-based Authentication in SQL Server Express’ Distributed Service Broker Environment – 10/20/2008
Check your SQL Server using Windows PowerShell – Part 7 – 10/15/2008
MSSQL Analysis Services – Attribute Member Names – 10/13/2008
SQL Server 2005 Express Edition – Part 32 – Distributed Service Broker Environment – Conducting Dialogs – 10/10/2008
Setting up a Two-NODE SQL Server 2008 Cluster from the Command Prompt – Integrated Installation – 10/08/2008
Basic Set Functions: The BottomCount() Function, Part I – 10/06/2008
SQL 2008 Backup and Restore Part 1 – 10/03/2008
Check your SQL Server using Windows PowerShell – Part 6 – 10/01/2008
SQL Server 2008 Data Collections and the Management Data Warehouse – 09/29/2008
SqlCredit – Part 19: Exploring SQL 2005’s Ranking Functions – RANK() and DENSE_RANK() – 09/26/2008
Mastering OLAP Reports: Parameterized Grouping – 09/23/2008
SQL Server 2005 Express Edition – Part 31 – Distributed Service Broker Environment – Routing – 09/22/2008
Attribute Member Keys – Pt II: Composite Keys – 09/19/2008
Check your SQL Server using Windows PowerShell – Part 5 – 09/17/2008
Setting up a Two-node SQL Server 2008 Cluster from the Command Prompt – Preparation – 09/16/2008
Intrinsic Member Properties: The MEMBER_VALUE Property – 09/12/2008
SQL Server 2005 Express Edition – Part 30 – Distributed Service Broker Environment – Endpoints – 09/08/2008
What is SQL Server – 09/05/2008
Terminate User processes in SQL Server – 09/03/2008
Attribute Member Keys – Pt 1: Introduction and Simple Keys – 08/29/2008
What Kind of DBA Are You? – 08/26/2008
SQL Server 2005 Express Edition – Part 29 – Implementing Service Broker Conversation – 08/25/2008
SqlCredit, Part 18: Exploring the Performance of SQL 2005’s OUTPUT Clause – 08/21/2008
Audit your Windows domain DBA group using PowerShell – 08/20/2008
Discover SQL Server TCP Port – 08/19/2008
Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part II – 08/18/2008
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property – 08/08/2008
Check your SQL Server using Windows PowerShell – Part 4 – 08/06/2008
SQL Server 2005 Express Edition – Part 28 – Implementing Service Broker Conversation – 08/05/2008
Create Your First SQL Server Database in 3 Quick Steps – 08/01/2008
Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I – 07/29/2008
SQL Server 2005 Express Edition – Part 27 – Implementing Basic Service Broker Objects – 07/28/2008
SqlCredit – Part 17: Exploring SQL 2005’s OUTPUT Clause – 07/25/2008
SQL Server 2008 MERGE Statement – 07/24/2008
Intrinsic Member Properties: The MEMBER_NAME Property – 07/21/2008
Check your SQL Server using Windows PowerShell – Part 3 – 07/16/2008
SQL Server Audit in SQL Server 2008 – Part 2 – 07/14/2008
SQL Server 2005 Express Edition – Part 26 – Introduction to Service Broker – 07/11/2008
Dimension Attributes: Introduction and Overview, Part V – 07/07/2008
SQL Server Profiler Part 2 – 07/02/2008
Check your SQL Server using Windows PowerShell – Part 2 – 07/01/2008
Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part II – 06/27/2008
SQL Server 2008 Date Functions, Part 2 – 06/26/2008
Enhancement in variable declaration – SQL Server 2008 – 06/24/2008
Implementing Upgrade of SQL Server 2005 Express Edition – 06/23/2008
Intrinsic Member Properties: The MEMBER_KEY Property – 06/20/2008
Check your SQL Server using Windows PowerShell – Part 1 – 06/18/2008
Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I – 06/13/2008
SQL Server 2005 Express Edition – Part 24 – Planning Upgrade of SQL Server 2005 Express Edition – 06/09/2008
SQL Server Audit in SQL Server 2008 – Part 1 – 06/06/2008
Compound Assignment Operators in SQL Server 2008 – 06/04/2008
Introduction to SQL 2005 Profiler Part 1 – 06/02/2008
Dimension Attributes: Introduction and Overview, Part IV – 05/29/2008
New Date Data Types with SQL Server 2008 – 05/27/2008
SqlCredit – Part 16: The Cost of Bloat – 05/23/2008
Intrinsic Member Properties: The MEMBER_CAPTION Property – 05/22/2008
SQL Server 2005 Express Edition – Part 23 – Manual Upgrade from Microsoft SQL Server Desktop Engine (MSDE) – 05/22/2008
Table-valued parameters – SQL Server 2008 – 05/21/2008
Dimension Attributes: Introduction and Overview, Part III – 05/15/2008
Policy-based Management in SQL Server 2008 – Part II – 05/13/2008
SQL Server 2005 Express Edition – Part 22 – Upgrading from Microsoft SQL Server Desktop Engine (MSDE) – 05/09/2008
Row Value Constructor in SQL Server 2008 – 05/06/2008
SQL Server Management Studio Reports and Dashboard – 05/02/2008
Support Parameterization from Analysis Services – Parameter Defaults – 04/29/2008
SqlCredit – Part 15: The Cost of Distribution – 04/25/2008
SQL Server 2005 Express Edition – Part 21 – Using Replication Management Objects – 04/24/2008
Connection Strategy for Multiple Database Environments – 04/22/2008
Dimension Attributes: Introduction and Overview, Part II – 04/18/2008
Policy-based Management in SQL Server 2008 – Part I – 04/17/2008
UPSERT Functionality in SQL Server 2008 – 04/16/2008
SQL Server 2005 Express Edition – Part 20 – Authenticating Merge Web Synchronization – 04/11/2008
Set Functions: The StripCalculatedMembers() Function – 04/07/2008
Storing Images and BLOB files in SQL Server Part 4 – 04/04/2008
Top Queries in SQL Server 2005 – 04/02/2008
Exam 70-443 Practice Test from uCertify.com – 03/31/2008
SqlCredit – Part 14: The Cost of Translation – 03/28/2008
Parameterization from Analysis Services – Cascading Picklists – 03/26/2008
SQL Server 2005 Express Edition – Part 19 – Authenticating Merge Web Synchronization – 03/24/2008
Using dtutil to copy SSIS packages stored in SQL Server – 03/20/2008
Find space Usage by Table , Schema in SQL Server 2005 and 2008 – 03/18/2008
Dimension Attributes: Introduction and Overview, Part I – 03/13/2008
SQL Server 2005 Express Edition – Part 18 – Merge Web Synchronization Setup – 03/11/2008
Storing Images and BLOB files in SQL Server Part 3 – 03/07/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 4 – 03/05/2008
Set Functions: The AddCalculatedMembers() Function – 03/03/2008
SQL Server DBA Dashboard – 02/29/2008
Support Parameterization from Analysis Services – 02/26/2008
SQL Server 2005 Express Edition – Part 17 – Merge Web Synchronization – 02/25/2008
SqlCredit – Part 13: More on Indexed Persisted Computed Columns – 02/22/2008
DST checking with Windows Powershell – 02/21/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 3 – 02/20/2008
Dimensional Model Components: Dimensions Part II – 02/15/2008
SQL Server 2005 Express Edition – Part 16 – Transactional and Merge Replication – 02/08/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 2 – 02/06/2008
MDX Numeric Functions: The Min() Function – 02/04/2008
Storing Images and BLOB files in SQL Server Part 2 – 02/01/2008
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets – 01/31/2008
Building Custom Reporting Services Reports for SQL Server Management Studio – 01/30/2008
SQL Server 2005 Express Edition – Part 15 – Snapshot Replication – 01/28/2008
SqlCredit – Part 12: Exploring Indexed Persisted Computed Columns – 01/25/2008
Dimensional Model Components: Dimensions Part I – 01/24/2008
Microsoft Windows PowerShell and SQL Server 2005 WMI Providers – Part 2 – 01/22/2008
Reporting Services: Customize Automatically Created Parameter Support Objects – 01/16/2008
Microsoft SQL Server 2008 – Change Data Capture – Part I – 01/11/2008
SQL Server 2005 Express Edition – Part 14 – Replication Support – 01/07/2008
Storing Images and BLOB files in SQL Server – 01/04/2008
New datetime datatypes in SQL Server 2008 – 01/02/2008
MDX Numeric Functions: The Max() Function – 12/31/2007
SQL Server 2005 – Hacking password Encryption – 12/28/2007
Disk Space Usage and SQL Server Performance – 12/26/2007
SQL Server 2005 Express Edition – Part 13 – ClickOnce Deployment and Security – 12/24/2007
BACKUP compression in SQL Server 2008 – 12/19/2007
Manage Unknown Members in Analysis Services 2005, Part II – 12/14/2007
SQL Server 2005 Express Edition – Part 12 – ClickOnce Deployment and Updates – 12/10/2007
Set Functions: The .AllMembers Function – 12/07/2007
SQL Server 2005 Encryption types – 12/05/2007
Installing SQL Server 2008 – 12/03/2007
Snapshot Reports II: SQL Server Management Studio Perspective – 11/30/2007
SQL Server Security Model – 11/28/2007
SqlCredit – Part 11: Change Tracking Using History Records – 11/26/2007

MS SQL Archives

2008   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2007   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2006   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2005   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2004   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2003   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2002   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2001   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2000   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1999   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1998   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Writing CLR Stored Procedures in C# – Returning Data (Part 2)

http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-returning-data-part-2

In Part 1 I covered a simple stored procedure that printed the current date. In this article I’ll cover writing a stored procedure to return a recordset back to the client. In the process we’ll learn a little bit more about .NET and C# including the SqlConnection, SqlCommand and SqlDataReader classes.

<!–

---

–>

If you’re following along in Visual Studio open up the solution you created in the previous article. If you didn’t save it that’s not a problem. Right click on the Project and choose Add Stored Procedure. Name the file GetAddressTypes and click Ok. As before it creates a shell of a method called GetAddressTypes. Notice the class declaration:

public partial class StoredProceduresIn the last article, the method we created was also in the StoredProcedures class. In previous versions of .NET a class had to be contained in a single file. In .NET 2.0 a class can be split over multiple files if you use the partial keyword. This lets us put each method in its own file if we want (and makes my article writing easier).

At a minimum you need to make sure you reference the following namespaces:

using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server;In earlier beta releases of SQL Server 2005 you used a different set of class libraries when you called SQL Server from inside a stored procedure than when you called SQL Server from a client application. With the April CTP we now use the “client” libraries in both cases. Much of the code in this article will look very familiar to application developers.

The first thing we need is a connection object to connect to SQL Server. We declare that like this:

SqlConnection conn = new SqlConnection(); conn.ConnectionString = “Context Connection=true”;This creates a variable of type SqlConnection called conn. This class resides in the System.Data.SqlClient namespace in the .NET Framework. One of the properties of a SqlConnection is ConnectionString. We reference this using the notation VariableName.PropertyName. You may be more familiar with connection string that look like this:

Data Source=YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;Using the “Context Connection” connection string tells the SqlConnection object to connect to the existing context inside SQL Server. Since this code will run inside a stored procedure inside SQL Server we don’t need to “relogin”.

Now we need a SqlCommand object to actually run a SQL statement. That looks like this:

SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = @”SELECT AddressTypeID, [Name] FROM Person.AddressType Order by AddressTypeID”;The SqlCommand object also resides in the System.Data.SqlClient namespace. This creates a new SqlCommand variable named cmd. The first property we set for this variable is the Connection property. The Connection property will only accept objects of type SqlConnection. So we assign conn, our SqlConnection variable, to the Connection property of cmd, our SqlCommand variable.

The CommandText property holds the SQL statement that we wish to execute. Remember that in C# each statement is ended by a semi-colon. This means that statements can wrap multiple lines. In this case it’s my string that wraps multiple lines. In C# you can embed special escape characters inside a string by using a backslash to “escape” them. If you ran the following code

string s = “First Line\r\nSecondLine”;and printed the result it would appear on two lines. \r and \n are the character escapes for carriage return and line feed. Placing an @ sign in front of a string says to ignore any escape characters inside the string literal. It also allows us to just hit the Enter key and have the string continue on the next line.

Now that we have our variables setup we can execute the our SQL statement. That code looks like this:

conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); SqlContext.Pipe.Send(rdr); rdr.Close(); conn.Close();The first step is to open the connection to our SQL Server. We this by calling the Open method of our SqlConnection variable. We’re going to store these results in something called a SqlDataReader. This is used to store a stream of read-only data from SQL Server. In later articles we’ll look at how to step through the individuals rows in the result set. One of the methods of our SqlCommand variable is ExecuteReader. This method executes the SQL in the SqlCommand object and returns the result as a SqlDataReader. Other methods allow you to execute the SQL statement and not return any values or return only a single scalar value. We’ll discuss those in a future article.

In the last article we used a SqlPipe object to send data back to our client. We do the same here but we do it a little differently. The Send method of the SqlPipe class is defined as a static method. static is a C# keyword that says we don’t need to instantiate an object of type SqlPipe to call the Send method. In the previous article, the Send method accepted a string and returned that back to the client. The Send method can also accept a SqlDataReader as a parameter. This has the effect of sending the result of the query back to the client as a rowset just like if we had directly executed the SQL statement. Having a method that accepts multiple different types of parameters is called overloading.

Finally we have to clean up our objects. First we close the SqlDataReader and then we close the SqlConnection. We use the Close method in both of these cases. The final method should look like this:

SqlConnection conn = new SqlConnection(); conn.ConnectionString = “Context Connection=true”; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = @”SELECT AddressTypeID, [Name] FROM Person.AddressType Order by AddressTypeID”; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); SqlContext.Pipe.Send(rdr); rdr.Close(); conn.Close();If you Deploy this Project to SQL Server it will create a stored procedure called GetAddressTypes that we can execute just like any other stored procedure. It will return a resultset just like any other stored procedure. That looks like this:

EXEC GetAddressTypes AddressTypeID Name ————- —————————- 1 Billing 2 Home 3 Main Office 4 Primary 5 Shipping 6 Archive (6 row(s) affected)That gives us a very basic stored procedure to return a record set back to the client. There are some tricks we can use to simplify our code though. We can rewrite our method to look like this:

SqlConnection conn = new SqlConnection(“Context Connection=true”); SqlCommand cmd = new SqlCommand(@”SELECT AddressTypeID, [Name] FROM Person.AddressType Order by AddressTypeID”, conn); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); SqlContext.Pipe.Send(rdr); rdr.Close(); conn.Close();We used the following two short cuts to shrink the code: When a SqlConnection is created it can accept the connection string as a parameter. When a new object is created (in this case a SqlCommand object) its constructor method is executed. Even when you create an object without any parameters it still have a constructor method that runs. I’ll cover constructors and overloading in more detail in a future article. Notice that the SqlCommand object can accept both the SQL statement and the connection as parameters when it’s created.

In this article we wrote a basic stored procedure that returned a record back to the calling program. We discussed the SqlConnection, SqlCommand and SqlDataReader objects. Next we’ll look at different ways to deploy the stored procedure to SQL Server.

Writing CLR Stored Procedures in C# – Introduction to C# (Part 1)

http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

This is the first article in a series on writing stored procedures using the Common Language Runtime (CLR). This article focuses on basic C# syntax and using Visual Studio to build a stored procedure. It’s targeted at DBA’s and anyone else who primarily writes in Transact-SQL and hasn’t had much exposure to .NET yet. (Update: Fixed the title.)

<!–

---

–>

As SQL Server 2005 rolls out DBA’s are going to be forced to learn either C# or Visual Basic or both. Until now these were client side languages and not knowing them had little impact on your job. And if you write code in these languages your going to have to learn to use Visual Studio. This article covers the basics of C# and Visual Studio using a basic stored procedure as an example. It’s written using the April CTP of SQL Server 2005.

C# vs. Visual Basic

When I first started working in .NET I was faced with the choice of a language. The two most popular are C# and Visual Basic. The debate on these two languages has ben on-going in the .NET community — sometimes friendly, sometimes not. I’ve done Visual Basic development in the past and could find my way around the language. All the web sites I’ve built were written in VBScript. C# is similar to C, C++ and Java. My skills in C are old and rusty and I don’t have any experience in C++ or Java. On the other hand C# is a brand new language designed from the ground up for .NET. It’s simple and not very verbose (less typing!). Visual Basic carries the baggage of numerous previous versions and needed some fairly significant changes to make it play well in the .NET world. Many of the .NET features such as class declarations and object inheritence seemed more elegant in C#. I chose C#.

I’ve been very happy with this decision. The language wasn’t difficult to learn. After a few weeks of using it I’ve never looked back. That’s not to say C# isn’t quirky for an ex-Visual Basic developer. The language itself and its string comparisons are case-sensitive. A comparison of equality (==) is different than an assignment (=). Those are two of the biggest areas that caused me problems.

If you’re faced with choosing a language to write stored procedures I’d encourage you to choose C#. Anyone with a strong Transact-SQL background will have no problem picking it up. I’m fairly certain there isn’t a right answer to which language to choose — only one that’s right for you. All my examples in this article and the ones that follow will be written in C#.

Visual Studio

In SQL Server 2000 DBA’s primarily used Query Analyzer and Enterprise Manager. SQL Server Managment Studio combined these tools in SQL Server 2005. However if you want to build CLR-based database objects or Integration Services packages (formerly DTS) you’ll have to learn Visual Studio. (You can build CLR-based objects without Visual Studio but it’s much more difficult. I’ll cover that in a future article.)

Visual Studio uses Solutions and Projects to group its work. A Solution is just a group of one of more Projects. A Solution can have different types of Projects in it. For example, you can have a web project, a class library (DLL) project and an Integration Services project all in the same solution.

After launching Visual Studio 2005 choose File -> New Project. In the dialog box under Project Type choose Visual C# -> Database and then choose SQL Server Project on the right side. I named my project CSharpPart1 and I cleared the checkbox to create a directory for the solution. This creates a solution and a project both named CShartPart1. If I’m working on a larger project I usually create a blank solution and then add projects to it as needed. Visual Studio will also ask you to create a database reference or use an existing one. I created one for the AdventureWorks database for this article.

A key area to understand in Visual Studio is the Solution Explorer. This lists all the projects and files in your solution.

Creating a Stored Procedure

We’ll start by having Visual Studio create a shell of a stored procedure for us. In the Solution Explorer, right click on the project and choose Add. From the submenu choose Stored Procedure and name the file “PrintToday.cs”. Visual Studio will add the file to your project and create a stored procedure shell for you like the one shown below.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void PrintToday()
    {
        // Put your code here
    }
};

The basic structure of this code is a series of using directives, a class (StoredProcedures) and method (PrintToday) within the class. You’ll notice that C# uses curly brackets to define the start and end of “things”. In Transact-SQL we use BEGIN and END for this. The class itself is contained in these curly brackets as is the PrintToday method

The first keyword that we see is using. .NET is an object-oriented development environment and uses the concept of a hierarchical namespace to group classes. The root of the namespace on most of the classes that ship with .NET is System. The classes that are used for data access are grouped under System.Data. The using keyword allows us to reference classes in the particular namespace without having to fully qualify them.

The class definition is next. The template created a class called StoredProcedures for us. The public keyword is an access modifier. It defines what other types of classes can instantiate objects of this type. Some type of access modifier is required. The partial keyword says we may use multiple physical files to store the definition for this class. This is new in .NET 2.0. The partial keyword is optional and is only really needed when we want to split the class up. The class keyword says we’re defining a class.

Next we define a method in the class. This method is called PrintToday and doesn’t take any parameters. (I’ll talk about the text in the square brackets in a minute.) In the case of a method, the public keyword doesn’t restrict who can call this method. Another common access modifier is private which says that this method can only be called from within this class. The PrintToday method doesn’t return anything back so its return type is void. If it returned a number its return type might be int. The static keyword says we can call this method without having to instantiate an instance of this class first.

The text in the brackets above the method declaration is called an attribute. Attributes can be used to “decorate” certain entities such as methods and properties (which we haven’t discussed yet). This is used by Visual Studio to deploy this stored procedure to SQL Server. This attribute tells Visuall Studio that this is a stored procedure for SQL Server.

Compiling and Deploying our Stored Procedure

The first step to using our stored procedure is to compile it. You do this by choosing Build -> Build Solution from the Visual Studio menu (or pressing control-shift-B). That will build (compile) all the projects in our solution. You can also build individual projects in a solution by right-clicking them in the Solution Explorer and choosing Build. This compiles our source code into a DLL. If you look in the directory for the project you’ll see a directory called bin. Under this directory you’ll find a Debug directory (and possibly a Release directory). On the top Visual Studio toolbar (right below the menu) you’ll see a drop down with choices for Debug and Release. These are the two default build configuartions. The debug build configuration adds debugging information to the compiled DLL. If you’ll look in bin/Debug in the project directory you should see a file called CSharpPart1.DLL. That’s our compiled DLL. When you choose Release, the DLL will be placed in the Release directory and will be built without the debugging information.

Next we need to put this DLL inside SQL Server and create a stored procedure that references it. This is called Deploying. You can deploy a stored procedure by right-clicking the solution or the project and choosing Deploy. This will compile the DLL, copy it into SQL Server and create a stored procedure called PrintToday. You have the same options by using the Build menu. At this point you can start SQL Server Management Studio and execute our stored procedure:

USE [AdventureWorks]
GO
EXEC [dbo].[PrintToday]
GO

It doesn’t really do anything but at least it doesn’t generate an error. In future articles I’ll cover the Transact-SQL commands to deploy assemblies and create stored procedures from them.

Writing Code

Now let’s add some actual code to our stored procedure. All I want to do is have it print today’s date. Add the following lines after it says “Put your code here” but inside the curly brackets for the method.

SqlPipe p;
p = SqlContext.Pipe;
p.Send( System.DateTime.Today.ToString() );

In Transact-SQL a variable declaration uses the DECLARE keyword and has the variable name prefixed with an “@” sign followed by the datatype and you can’t assign a value to a variable when you declare it. In C# you declare a variable by starting with the type of variable you want to declare and then listing the variable. The first line above declares a variable p of type SqlPipe (which I’ll discuss in a second). Each statement in C# must be following by a semi-colon and statements can be split over multiple lines. SqlPipe is in the namespace Microsoft.SqlServer.Server and we can reference it directly because indicated we’re using this namespace is in a using directive at the top of the procedure.

Next we assign a value to our variable. There is an object called SqlContext (also in the Microsoft.SqlServer.Server namespace) that has a property called Pipe. A property is referenced (or set) as Object.Property. This CLR DLL is going to run inside the SQL Server process space. The SqlContext object is our “hook” into that process. The Pipe object (which is a property of the SqlContext object) is what we’ll use to send data back to the calling program (our query in Management Studio that called this stored procedure). So p is now defined as the “pipe” back.

In C# you can combine a variable declaration and assignment. The first two lines of our custom code could be combined into this line:

SqlPipe p = SqlContext.Pipe;

A SqlPipe object has a series of methods and properties. One of these methods is the Send method. There are three different versions of the Send method but we’re only concerned with one of them for now. It accepts a string as a parameter and returns it to the calling program. We pass it a string parameter inside the parenthesis. This has the same result as doing a PRINT inside Transact-SQL.

The parameter we’re going to pass to the Send method is today’s date. In Transact-SQL we get the date using GETDATE(). In any one of the .NET languages we get the current date by referencing System.DateTime.Today. Today is a property of the DateTime class. It’s a static property so we don’t have to instantiate an instance of DateTime to call the property. It returns a DateTime datatype. Nearly every datatype in .NET can be converted to a string using the ToString method. Since this is a method it has to be called with parenthesis. System.DateTime.Today returns a DateTime and that can be converted to a string so we can just do it directly in a single statement:

System.DateTime.Today.ToString()

This will return the current date as a string. 

We pass our string into the Send method and it will be “printed” by the stored procedure. Now we can build, deploy and test the stored procedure again:

USE [AdventureWorks]
GO
EXEC [dbo].[PrintToday]
GO

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

6/5/2005 12:00:00 AM

Notice that it strips out the time but displays it anyway. I believe this is the result of the build I’m using. In a later article I’ll talk about string conversions and format strings.

Summary

This article discussed basic C# syntax and using Visual Studio to write CLR stored procedures. Future articles will cover database access, programmitically deploying assemblies and more complex logic in .NET.