Development

Getting started with dynamic SQL

TimChapman — Mon, 07 Dec 2009 15:16:00 GMT

Sometimes you need dynamic SQL. Sometimes you don't know at run time where you're querying or what fields you need to bring back, or what your criteria will be, so there will always be a place for the use of dynamic SQL. You have to be careful how you use it, but if you do use it correctly it can be a powerful tool that you have in your tool belt.

So, today I am going to show you a nice little general purpose procedure that you can write to pass in a few object strings and execute dynamic SQL. Im not going to go into the ins and outs of low-level dynamic SQL or what you need to do to prevent certain types of SQL injection...I'll leave that discussion for a later time.

First, lets just setup a table and insert some values into it.

use tempdb

IF OBJECT_ID('DynamicQueries','U') IS NOT NULL
DROP TABLE DynamicQueries
GO
CREATE TABLE DynamicQueries
(
    ID SMALLINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Fld1 INT, 
    Fld2 INT
)
SET NOCOUNT ON

DECLARE @x SMALLINT
SET @x = 1

WHILE @x < 32000
BEGIN
    INSERT INTO DynamicQueries(Fld1, Fld2)
    SELECT @x % 10, @x % 7
    
    SET @x = @x + 1
END
GO

Below is our "general purpose" dynamic SQL stored procedure. It requires a table name and a field list (technically you could provide * for the field list) and an optional parameter for the query criteria. It has the limitation of only querying one table for the time being, but I may expand upon that idea at a later point to allow for the addition of other tables with joins, etc. If you wanted to make this procedure really general purpose, you could make it a system stored procedure. I didn't do it in this example, but you could easy just change the procedure name from usp_ to sp_, put it in the master database and mark it as a system stored procedure. Then you'd be able to run this procedure in any database w/o having to worry about the database context.


CREATE PROCEDURE usp_GetDynamicValues
(
    @TableName NVARCHAR(255),
    @FieldList NVARCHAR(1000),
    @Criteria NVARCHAR(1000) = NULL
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    BEGIN TRY
        SET @SQL = N'SELECT ' + @FieldList + ' FROM ' + @TableName 
        SET @SQL = @SQL + 
                        CASE 
                            WHEN NULLIF(LTRIM(RTRIM(@Criteria)), '') IS NULL 
                            THEN '' 
                            ELSE ' WHERE ' + @Criteria 
                        END
                        
        EXECUTE sp_executesql 
        @stmt = @SQL
    END TRY
    BEGIN CATCH
        PRINT 'An error has occurred.'
    END CATCH
END
GO

Notice in the above example that I am using sp_executesql rather than the EXECUTE command. sp_executesql allow for using parameters when executing dynamic sql statements. Doing so encapsulates your parameter values which can in some instances prevent SQL injection attacks. The parameters also lead to better plan reuse than does the EXECUTE command. So, try to always use sp_executesql over EXECUTE.

Once the procedure is created, all that is left to do is to call it, which I am doing below.


EXECUTE usp_GetDynamicValues
@TableName = 'DynamicQueries', 
@FieldList = 'ID, Fld2', 
@Criteria = 'ID = 4'

While this example was relatively simple, it shows you how useful (and powerful dynamic SQL can be). Look for future articles where I expand upon this idea and start to develop a more elaborate setup where you can really harness the power of using dynamic SQL.

HTH,
Tim

Creating Indexes with IGNORE_DUP_KEY

TimChapman — Mon, 16 Nov 2009 01:04:00 GMT

Unique indexes and unique constraints limit the values that are allowed in a table column to unique values. This uniqueness is almost always a result of some type of business rule. Programming these types of business rules are usually not overcomplicated to write. However, SQL Server has an option that you can set when creating unique indexes that allows you to control how duplicate values are handled so that you can potentially get away from writing these rules by hand.

The unique index option, IGNORE_DUP_KEY, allows you to indicate how records that would violate a unique index should be handled. When the option is set to OFF, any records that violate the unique constraint will result in error and the entire statement will be rolled back. This option will prevent any records that would not have violated the unique index from getting into the table as well. This is typically where custom programming needs to be written to only insert records that are not already in the table. When the option is set to ON, the records that do not violate the constraint are inserted while the records that do violate the constraint are ingored. You can see where this option may come in quite handy.

To take a look at how IGNORE_DUP_KEY works, lets take a look at an example. I'll contrast how SQL Server handles unique violations with the option set ON and OFF. To start the example off, lets create a table.

IF OBJECT_ID('TestingDuplicateValues','U') IS NOT NULL
    DROP TABLE TestingDuplicateValues
GO
CREATE TABLE TestingDuplicateValues
(
    IDColumn SMALLINT NOT NULL
)
GO

Now I'll create a unique index on my single column table. By default the IGNORE_DUP_KEY option is set to OFF when a unique index is created I included it here to illustrate the TSQL syntax for how to create the index.

CREATE UNIQUE NONCLUSTERED INDEX idx_TestingDuplicateValues_IDColumn
ON TestingDuplicateValues(IDColumn)
WITH (IGNORE_DUP_KEY = OFF)
GO

To test our new unique index lets create a batch to violate the unique index. Since the IGNORE_DUP_KEY option is set to OFF, I will recieve an error telling me that I cannot insert a duplicate value into the table. Consequently, the entire batch is rolled back.

INSERT INTO TestingDuplicateValues(IDColumn)
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 2
G0

To validate that no records were inserted into the table, run the following query. 0 records should be returned.

SELECT * 
FROM TestingDuplicateValues

Now, lets drop our unique index and recreate it. This time I will create the unique index with the IGNORE_DUP_KEY option set to ON.

DROP INDEX TestingDuplicateValues.idx_TestingDuplicateValues_IDColumn
GO
CREATE UNIQUE NONCLUSTERED INDEX idx_TestingDuplicateValues_IDColumn
ON TestingDuplicateValues(IDColumn)
WITH (IGNORE_DUP_KEY = ON)
GO

When I run the same INSERT statement as before, 2 records are now inserted into the table. The duplicate value from the statement is ignored. No error is returned, rather I receive the informational message "Duplicate key was ignored."

INSERT INTO TestingDuplicateValues(IDColumn)
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 2
G0

You can validate that 2 records were inserted into the table w/ the following query.

SELECT * 
FROM TestingDuplicateValues

The IGNORE_DUP_KEY is a very handy option when creating unique constraints. However, it does not necessarily take the place of crafty programming when it is necessary. Check out the option and determine if it works for your scenario. If it does...GREAT, glad to help. If not, don't worry, a little programming goes a long way.

Tim

SQL Server 2008 Syntactic Sugar

TimChapman — Tue, 29 Sep 2009 00:21:00 GMT

As a developer, I always enjoy new TSQL language features that can save me some time and potentially effort in accomplishing the same task that I otherwise could in a different fashion. These features aren't really a big deal to me to have but are sometimes fun. SQL Server 2008 has produced a couple of these features so I thought it might be worthwhile to take a look at them.

Compound Operators
This is a feature that exists in most common-day programming languages. The idea is simple; why use a variable twice inside of an assignment statement when you can get away with using it just once. So, instead of:

DECLARE @IntegerVariable INT =  5
SET @IntegerVariable = @IntegerVariable + 1

The result of the above statement is 6 as you'd expect. However, I did sneak in a new feature that I'd yet to mention. In the variable declaration I actually assigned a value to the variable in the declaration. This is also a new feature in SQL Server 2008. I kinda like not having to break my assignment out onto a new line when I am giving a variable an initial value.

You can alter the statement a bit to compound the operator and accomplish the same task. This syntax is in the form of:

DECLARE @IntegerVariable INT =  5
SET @IntegerVariable += 1

This syntax works pretty much anywhere you'd normally use a variable twice inside an assignment operator. Its neat....not critical, but definitely a feature that is nice to have.

Row Constructors
A Row Constructor in SQL Server 2008 is really nothing more than an easier way to INSERT rows of values into a table at the same time using the VALUES portion of the INSERT statement. Technically, you can do the same thing using a SELECT and UNION ALL statement in versions prior to 2008. I still think it is a kinda neat new feature...

To make use of the new feature I'll need a table that I can INSERT some values into.

IF OBJECT_ID('InsertRowConstructor') IS NOT NULL
DROP TABLE InsertRowConstructor
GO
CREATE TABLE InsertRowConstructor
(
    Field1 INT, Field2 INT
)

Once my table is created (or dropped and recreated), I add some values to it. The only difference with the new way to use the VALUES clause is that you can add as many tuples of data to it as you'd like. You'll need to wrap parentheses around the tuples and seperate them with commas.

INSERT INTO InsertRowConstructor(Field1, Field2)
VALUES  (1,2), 
        (2,3), 
        (3,4), 
        (4,5), 
        (5,6)

Of course, I could have accomplished this using a SELECT statement with a UNION (ALL) command:

INSERT INTO InsertRowConstructor(Field1, Field2)
SELECT 1,2
UNION ALL
SELECT 2,3
UNION ALL 
SELECT 3,4
UNION ALL 
SELECT 4,5
UNION ALL
SELECT 5,6

The SELECT approach isn't quite as fun, but works the same way.

There is also a new MERGE feature in SQL 2008 that I won't discuss here. You can read more about my MERGE blog post on TechRepublic. In a way, its the large piece of syntactic sugar because it allows you to combine INSERT, UPDATE, and DELETE statements into one statement. However, this feature can make those situations where you need to write seperate INSERT and UPDATE statements based on record existence so much easier. A GREAT feature in my opinion.

Tim

Pivot Rolling Periods : Dynamic, But Not!

Kevin Cross — Fri, 18 Sep 2009 03:53:00 GMT

In the Article, Pivot Rolling Periods Without Using Dynamic T-SQL (experts-exchange.com/viewArticle.jsp?aid=654), I explored the pivot of moving date based reports without the use of dynamic T-SQL, but still allow for re-use month after month without re-writing of code.

Here is another introductory tidbit to SQL Server 2005's PIVOT (technet.microsoft.com/en-us/library/ms177410.aspx). We will embrace the limitations of the PIVOT keyword requiring hard coded values / column names, but still provide nice reports that can be used in views or quick queries without getting into multi-line dynamic code.

To play along, you can download and run the following DDL and data insert SQL script:

(Please visit the site to view this media)

Okay, now we can play. The following starts out by getting data greater or equal to last January 1st at midnight "dateadd(year, datediff(year, 0, getdate())-1, 0)". Then we employ a date function to make our datetime value non-specific to year (i.e., month(bk_date) will yield 1-12). For this report, we want to have a year over year analysis of data by month so we also use year(bk_date) to get our grouped column heading which doesn't affect us in the PIVOT since it will remain as data.

;with bks( byear, bmonth, amount )
as
(
select year(bk_date), month(bk_date), bk_amount
from bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate())-1, 0)
)
select byear as "Sales Year",
coalesce("1", 0.00) as January,
coalesce("2", 0.00) as February,
coalesce("3", 0.00) as March,
coalesce("4", 0.00) as April,
coalesce("5", 0.00) as May,
coalesce("6", 0.00) as June,
coalesce("7", 0.00) as July,
coalesce("8", 0.00) as August,
coalesce("9", 0.00) as September,
coalesce("10", 0.00) as October,
coalesce("11", 0.00) as November,
coalesce("12", 0.00) as December
from bks
pivot( sum(amount) for bmonth 
       in ("1","2","3","4","5","6","7","8","9","10","11","12") ) pvt
order by "Sales Year" desc

Results will look similar to below:

The above uses the month number, since it has to be typed multiple times as use explicit select list to use functions like coalesce(). This is more important when doing advanced pivoting like with multiple columns (experts-exchange.com/viewArticle.jsp?aid=1537); however, since we have a simple case here where null may be okay as it quickly answers the question of a missing data point versus a very poor $0.00 booking month, we can use a much simplified code:

with bks("Sales Year", bmonthname, amount)
as
(
select year(bk_date), datename(month, bk_date), bk_amount
from bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate())-1, 0)
)
select *
from bks
pivot( sum(amount) for bmonthname
       in (January, February, March, April, May, June,
           July, August, September, October, November, December) ) pvt
order by "Sales Year" desc

This version looks similar to this:

As you will see, we haven't lost any value in the actual data and gained less code to maintain and a clear cut distinction between 0's and no data as already stated. We utilize the datename() function here to strip out the year and day as we did before, but instead of just a number we return full month. For consistency, you can use left( datename( month, bk_date ), 3 ) and you will get 'Jan', 'Feb', 'Mar', etc. It is normally not a good practice to use wilcard (*) for selecting records, but since we explicitly defined the columns we wanted in the CTE, 'select *' is very safe here as we are still protected from changes in the underlying table structure as we are selecting all from the CTE and not the base table.

Notice, we still following the same syntax on the pivot, just changed the actual values in play.

pivot( aggregate_function( column_to_aggregate ) for column_to_pivot
       in ( pivot_value1 [, pivot_value2 [, ... pivot_valuen]] )
) pivot_alias

Hopefully that was helpful!

This was my first entry, so hopefully you liked it. Will work on adding more to it if folks are interested as I use these kinds of queries frequently for things like item inventory turns over or dependent / independent usage over the past 12 months, so have played with it a bit.

Happy coding!

-- Kevin

Unstringing a comma delimited column

mark_wills — Wed, 27 May 2009 18:07:00 GMT

Ever had a column which had a pile of information delimited by commas that you want to unpack ?

Well, it is not that hard so long as the information is pretty short, and tightly controlled...

-- step 1 create a sample table used as a datasource...

if object_id('tempdb..#tmp_colors','U') is not null drop table #tmp_colors

create table #tmp_colors (Date datetime, Name varchar(60), Choices varchar(100))
insert #tmp_colors
select '01 Mar 2009','John S','Red,Blue,Yellow' union all
select '02 Mar 2009','Jane D','Green, Orange, Pink, Purple' union all
select '01 Mar 2009','Mark W','Yellow, Blue, Black, Dark Blue' union all
select '02 Mar 2009','Brandon G','Dark Blue, Navy, Yellow, Gray, Purple' union all
select '01 Mar 2009','Tim C','Blue, Cyan, Yellow, Gray' union all
select '02 Mar 2009','Bob J','Blue'

-- now lets run a query on that and return the results.

SELECT name, date, ltrim(SUBSTRING(choices, n, CHARINDEX(',', choices + ',',n) - n)) AS colours
FROM #tmp_colors
CROSS JOIN (SELECT number n FROM master..spt_values WHERE type = 'P') AS Numbers
WHERE SUBSTRING(',' + choices, n, 1) = ','
AND n < LEN(choices) + 1

Two things I love about Management Studio 2008

TimChapman — Sat, 04 Apr 2009 01:34:00 GMT

I've been using the SQL Server product for quite some time now, and I almost always use the IDE programs that ship with the database engine. Enterprise Manager and Query Analyzer certainly had their quicks, but were overall nice tools. Management Studio 2005 was a great improvement over these tools because it integrated Enterprise Manager and Query Analyzer into one tool, and it added some neat bells and whistles. Now that Management Studio 2008 is out, there are two really neat new features I'd like you to take a look at if you haven't already.

First, is the ability to expand and collapse code blocks. This is a subtle tool, but so useful when you really just want to focus on a single set of code. Its not a huge feature for the product, but one that I think makes all of the difference in the world. In the following screenshot, I define a table and then use a BEGIN...END statement to insert some rows into the table...simple stuff.

Here's the code that I use in the above screenshot.

IF OBJECT_ID('RecordLoad') IS NOT NULL
DROP TABLE RecordLoad
GO
CREATE TABLE RecordLoad
(
    ID INT IDENTITY(1,1),
    ValueColumn INT
)
GO

BEGIN
    ;WITH CounterCTE
    AS
    (
        SELECT 1 AS ValRow
        UNION ALL
        SELECT ValRow + 1
        FROM CounterCTE
        WHERE ValRow < 100
    )
        INSERT INTO RecordLoad(ValueColumn)
        SELECT ValRow 
        FROM CounterCTE
END

Notice the expanded brackets to the left of the code blocks. If I am done writing that block, or I just want to come back to it later, I can click on the box and collapse the code block so that it takes up only one line in the IDE. I can even highlight this line and run the entire block of code contained in it. This is very handy for when you want to run large snippets of data.

The second thing I really like about Management Studio 2008 is its built in IntelliSense. Sure, there are other tools out there from third parties that provide IntelliSense, but I don't think they're as good as what comes packaged with Management Studio 2008. In the following example, I modify my INSERT statement to show the available columns to use in the INSERT list. It provides a list of the fields along with the data type of the highlighted column. This is very handy information, and can make you a lot more productive.

Both of these features are based are technically part of IntelliSense, which can be enabled by right-clicking in the Query window and selecting "IntelliSense Enabled".

While I certainly don't think these are the best features that came with SQL Server 2008, or even the best enhancements to Management Studio 2008, I love them. I think they made code easier to write, and debug. Check them out if you haven't already.

Using TRY...CATCH IN SQL Server 2005/2008

TimChapman — Fri, 03 Apr 2009 19:16:00 GMT

New to SQL Server 2005 (and present in SQL Server 2008) is the ability to use TRY..CATCH constructs inside of your TSQL code to catch error statements. If you've done any type of programming in other programming languages, you know that this is a very handy tool to have. Whats even better in my opinion is the ability to write a generic error handling procedure you can use inside of your CATCH blocks to capture and record errors when they occur.

So, lets take a look at how we can take advantage of these new error handling features. In the following script I create a table which will be used by my stored procedure which will contain my TRY...CATCH blocks.

IF OBJECT_ID('RecordLoad') IS NOT NULL
DROP TABLE RecordLoad
GO
CREATE TABLE RecordLoad
(
    ID INT IDENTITY(1,1),
    ValueColumn INT
)
GO

Here I define a stored procedure that uses a CTE to insert 100 records into the table I just created. This is done inside of the TRY block, which will immediately revert any execution to the CATCH block once an error is incurred for which it is able to handle. After the insert of the 100 records, I cause a divide by zero error. This error is caught by my CATCH block, which then displays information to me regarding the error details. To get at this error related information, I use 6 new error handling functions.

IF OBJECT_ID('usp_ErrorExample') IS NOT NULL
DROP PROCEDURE usp_ErrorExample
GO
CREATE PROCEDURE usp_ErrorExample
AS
BEGIN
    BEGIN TRY
        
        ;WITH CounterCTE
        AS
        (
            SELECT 1 AS ValRow
            UNION ALL
            SELECT ValRow + 1
            FROM CounterCTE
            WHERE ValRow < 100
        )
            INSERT INTO RecordLoad(ValueColumn)
            SELECT ValRow 
            FROM CounterCTE
            
        SELECT Error = 1/0  --THIS WILL ERROR, AND INVOKE THE CATCH BLOCK
    END TRY
    BEGIN CATCH
        --USE NEW ERROR HANDLING FUNCTIONS TO GET ERROR DATA
        SELECT 
            ErrorLine = ERROR_LINE(), 
            ErrorMessage = ERROR_MESSAGE(), 
            ErrorNumber = ERROR_NUMBER(),
            ErrorProcedure = ERROR_PROCEDURE(),
            ErrorSeverity = ERROR_SEVERITY(),
            ErrorState = ERROR_STATE()
    END CATCH
END

Here's a screenshot of what my output from the above procedure execution looks like. As you can see, this is some very handy information to have.

The following procedure is almost the same as above, except that I am using the RAISERROR command to generate a custom error message, which is also caught by the CATCH block.

IF OBJECT_ID('usp_ErrorExample') IS NOT NULL
DROP PROCEDURE usp_ErrorExample
GO
CREATE PROCEDURE usp_ErrorExample
AS
BEGIN
    BEGIN TRY
        
        ;WITH CounterCTE
        AS
        (
            SELECT 1 AS ValRow
            UNION ALL
            SELECT ValRow + 1
            FROM CounterCTE
            WHERE ValRow < 100
        )
            INSERT INTO RecordLoad(ValueColumn)
            SELECT ValRow 
            FROM CounterCTE
            
        RAISERROR('This is a custom error.', 16,1,1)  --THIS WILL ERROR, AND INVOKE THE CATCH BLOCK
    END TRY
    BEGIN CATCH
        --USE NEW ERROR HANDLING FUNCTIONS TO GET ERROR DATA
        SELECT 
            ErrorLine = ERROR_LINE(), 
            ErrorMessage = ERROR_MESSAGE(), 
            ErrorNumber = ERROR_NUMBER(),
            ErrorProcedure = ERROR_PROCEDURE(),
            ErrorSeverity = ERROR_SEVERITY(),
            ErrorState = ERROR_STATE()
    END CATCH
END

And here is the output from that procedure call.

Finding out about these errors are great when they occur, but having to duplicate the code to display to the screen or insert to a table can be a cumbersome and potentially error prone task. Whats great about using the TRY...CATCH blocks is that I can define a generic stored procedure to save these messages to a table for later review. The following code snippets I have borrowed from an article I had previous written for TechRepublic. I define a table to store the errors when they occur, and a procedure that I can call in all of my CATCH statements to log the error information. The great thing is that since the procedure executes in the context of the CATCH block, there is no need to pass in any parameters, the functions take care of the details for me.

CREATE TABLE ProcedureErrors
(      
    ErrorLogID INT IDENTITY(1,1) PRIMARY KEY,      
    ErrorDate DATETIME DEFAULT(GETDATE()),      
    ErrorLine INT,      
    ErrorMessage NVARCHAR(4000),      
    ErrorNumber INT,      
    ErrorProcedure NVARCHAR(126),      
    ErrorSeverity INT,      
    ErrorState INT,       
    DatabaseName NVARCHAR(255)
)

CREATE PROCEDURE [usp_LogProcedureError]
AS
BEGIN
    IF ERROR_NUMBER() <> 0 AND @@TRANCOUNT > 0
        ROLLBACK TRAN

    INSERT INTO ProcedureErrors
    (
        ErrorLine, ErrorMessage, ErrorNumber, 
        ErrorProcedure, ErrorSeverity, ErrorState, DatabaseName
    )
    VALUES
    (
        ERROR_LINE(), ERROR_MESSAGE(), ERROR_NUMBER(),
        ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE(), DB_NAME()
    )

    PRINT 'An Error has occurred in ' + ERROR_PROCEDURE() + '.'
    PRINT 'Error Description: ' + ERROR_MESSAGE()

END

And here is the altered procedure script with the new call to my usp_LogProcedureError procedure.

IF OBJECT_ID('usp_ErrorExample') IS NOT NULL
DROP PROCEDURE usp_ErrorExample
GO
CREATE PROCEDURE usp_ErrorExample
AS
BEGIN
    BEGIN TRY
        
        ;WITH CounterCTE
        AS
        (
            SELECT 1 AS ValRow
            UNION ALL
            SELECT ValRow + 1
            FROM CounterCTE
            WHERE ValRow < 100
        )
            INSERT INTO RecordLoad(ValueColumn)
            SELECT ValRow 
            FROM CounterCTE
            
        RAISERROR('This is a custom error.', 16,1,1)  --THIS WILL ERROR, AND INVOKE THE CATCH BLOCK
    END TRY
    BEGIN CATCH
        EXECUTE usp_LogProcedureError
    END CATCH
END

As you can see, it makes writing error handling routines much much easier. So, try to start infusing this error handling logic (or SOME type of error handling logic) into your SQL Server 2005 or 2008 code if you haven't already. It has saved me many, many hours of troubleshooting time.

Concatenating Results through SELECT statements

TimChapman — Fri, 20 Mar 2009 20:53:00 GMT

In this SQL tip I am going to show you a neat little trick which involves using a simple SELECT statement to concatenate row values into a single variable value.

First, I'll create a temp table named #Results for which I'll store FirstName and LastName data.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    FirstName varchar(25), 
    LastName varchar(25)
)

GO

This script adds some names to the temp table I just created.

INSERT INTO #Results(FirstName, LastName)
SELECT 'Tim', 'Chapman' 
UNION ALL
SELECT 'Brandon', 'Galderisi' 
UNION ALL
SELECT 'Aaron','Akin' 
UNION ALL
SELECT 'Mark', 'Wills' 
UNION ALL
SELECT 'Angel','Eyes' 
UNION ALL
SELECT 'Daniel','Wilson'

GO

The following script does the meat of the work. I declare a variable to hold the name values and use the variable in my SELECT statement. The SELECT statement essentially takes every value from the #Results table and appends it to the @FullNames variable.

DECLARE @FullNames VARCHAR(200)

SELECT
    @FullNames = ISNULL(@FullNames,'') + LastName + ', ' + FirstName + ' '
FROM #Results

PRINT @FullNames

The result of printing the value in the variable is below.

Chapman, Tim Galderisi, Brandon Akin, Aaron Wills, Mark Eyes, Angel Wilson, Daniel

If I want to add a line feed to the varaible for formatting purposes, it is easy enough to do by adding the CHAR(10) character to the variable string. This method will insert a new line feed between each value.

DECLARE @FullNames VARCHAR(100)

SELECT
    @FullNames = ISNULL(@FullNames,'') + LastName + ', ' + FirstName + ' ' + CHAR(10)
FROM #Results

PRINT @FullNames

Printing the contents of the @FullNames variable leaves me with the following output.

Chapman, Tim 
Galderisi, Brandon 
Akin, Aaron 
Wills, Mark 
Eyes, Angel 
Wilson, Daniel

Stripping the time from a DATETIME value

Aaron Akin — Fri, 20 Mar 2009 02:39:00 GMT

Many times there is a need to strip the time off of a DATETIME value so you are left with the date only. I've seen a few different ways to handle this. To clarify, when I say a DATETIME value with no time, I really mean midnight (00:00:00.000) on that date.

Converting to VARCHAR

One way to remove the time from a DATETIME value is to convert it to a VARCHAR data type. This is handy when you need the date returned as a VARCHAR value and when you need the option to return the date in any number of different formats, with or without the century (i.e. 2009 vs 09). The full list of the different formats that are available using CONVERT is located in Books Online. Here is an example of a few the different formats.

DECLARE @dt DATETIME
SET @dt = GETDATE()

SELECT CONVERT(VARCHAR,@dt,1) AS Date1
     , CONVERT(VARCHAR,@dt,101) AS Date101
     , CONVERT(VARCHAR,@dt,12) AS Date12
     , CONVERT(VARCHAR,@dt,112) AS Date112

Date1      Date101    Date12     Date112
---------- ---------- ---------- ----------
03/19/09   03/19/2009 090319     20090319

One thing to consider when comparing a DATETIME value to another DATETIME value that has been converted to VARCHAR, you should always convert it back to DATETIME. This is called an explicit conversion when you use CAST or CONVERT to change a value to a different data type. The comparison will still work properly without the explicit conversion, but this will cause SQL Server to convert it at runtime, called an implicit conversion, putting unneccesary overhead on the server.

Using DATEADD and DATEDIFF

Another way to remove the time from a DATETIME value is to use a combination of the DATEADD and DATEDIFF functions. This is a good way to not only strip the time off of a particular DATETIME value, but you can also use that value to get other dates without the time. Here is an example of a few different dates that can be returned using the current date as a reference point.

DECLARE @dt DATETIME
SET @dt = GETDATE()

SELECT DATEADD(DAY,DATEDIFF(DAY,0,@dt),0) AS Today
     , DATEADD(DAY,DATEDIFF(DAY,0,@dt),-1) AS Yesterday
     , DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0) AS FirstOfThisMonth
     , DATEADD(MONTH,DATEDIFF(MONTH,-1,@dt),-1) AS LastOfThisMonth
     , DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),-1) AS LastOfPrevMonth
     , DATEADD(YEAR,DATEDIFF(YEAR,0,@dt),0) AS FirstOfYear

Today               Yesterday           FirstOfThisMonth    LastOfThisMonth     LastOfPrevMonth     FirstOfYear
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2009-03-19 00:00:00 2009-03-18 00:00:00 2009-02-28 00:00:00 2009-03-31 00:00:00 2009-02-28 00:00:00 2009-01-01 

00:00:00

ODBC Date/Time Escape Clauses

Another interesting method I found recently involves ODBC date and time escape clauses. I don't really know much about these, but I thought they were intriguing. There are 3 extensions, one for date (d), time (t), and timestamp (t). The only one I thought might be useful was the time extension. Here's an example of using this to retrieve today's date with no time value.

SELECT {t '00:00:00'} AS Today

Today
-------------------
2009-03-19 00:00:00

I could see this being handy if you only ever wanted to get the current date at midnight, but I haven't found a way to pass in a variable or column to this, so it is limited to the current date only. Just for kicks, here's how you would use the date and timestamp escape clauses.

SELECT {ts '2009-03-19 00:00:00'}
     , {d '2009-03-19'}

Both of these will return today's date at midnight as well. I'd be interested to see if anyone knows any other uses for these.

Aaron
http://aaronakinsql.wordpress.com

Finding the major version number of your SQL Server instance

Aaron Akin — Thu, 19 Mar 2009 02:32:00 GMT

Occasionally, I need to determine which version of SQL Server I am connected to within a script so that I can write the proper code for that version. I’m never really concerned with the build number or which service pack I have installed. Instead, all I want is the major version number (7, 8, 9, 10, etc).

One of the difficult things about something that seems like it should be so simple is that some of the ways to check the version number only exist on certain versions. Other ways give you way more info than you really need.

Recently, I was digging through a system stored procedure, which is always the best place to find cool code, and I came across the undocumented system function @@MICROSOFTVERSION which is used internally by Microsoft. It returns a seemingly random number, but you can use a bitwise AND operator to find the major version number.

SELECT @@MICROSOFTVERSION / 0x01000000

This will return the following based on your version of SQL Server:

7 (SQL 7)
8 (SQL 2000)
9 (SQL 2005)
10 (SQL 2008)

This will work on any version of SQL Server as of now, but since it is undocumented, it’s always possible that it could be removed in the future.

Aaron
http://aaronakinsql.wordpress.com

Nested Common Table Expressions

Aaron Akin — Thu, 19 Mar 2009 02:03:00 GMT

If you've looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can't define a CTE in the definition of another CTE. Although this is technically true, you can still accomplish the same functionality in a different manner. Here is an example of a simple CTE using the AdventureWorks database that will return a list of orders.

USE AdventureWorks

;WITH CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT * FROM CTE_Orders

OrderID     OrderDate
----------- ----------
1           2001-05-17             
2           2001-05-17             
3           2001-05-17             
4           2001-05-17             
5           2001-05-31             
...

What isn't very clear is the use of multiple CTE definitions in one statement. Here is an example of multiple CTEs used in one statement. Each CTE in the statement has its own definition, and the final SELECT statement is able to query both CTEs. This will return a list of dates that orders were made along with the number of products ordered and the total amount of those products for each date.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty, SUM(od.OrderDetailAmt) AS TotalOrderAmt
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

OrderDate  TotalOrderQty TotalOrderAmt
---------- ------------- -------------
2001-05-17 563           9491.518
2001-05-31 2765          94404.303
2002-01-14 718           39874.7685
2002-01-15 11931         259365.2145
2002-02-08 2768          50781.36
...

If you take this concept of having multiple CTE definitions in one statement, you can then apply it to a statement with nested CTEs. I'm using the following statement to return the same result set as in the previous example, but this time I want to know the number of orders for each date that had more than 100 products ordered.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate, IsLargeOrder) AS
(
  SELECT PurchaseOrderID, OrderDate
       , (
         SELECT CASE
                  WHEN SUM(OrderDetailQty) >= 100 THEN 1
                  ELSE 0
                END
           FROM CTE_OrdersDetail d
           WHERE h.PurchaseOrderID = d.OrderID
         )
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty
   , SUM(od.OrderDetailAmt) AS TotalOrderAmt, SUM(o.IsLargeOrder) AS NumLargeOrders
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

OrderDate  TotalOrderQty TotalOrderAmt         NumLargeOrders
---------- ------------- --------------------- --------------
2001-05-17 563           9491.518              1
2001-05-31 2765          94404.303             5
2002-01-14 718           39874.7685            1
2002-01-15 11931         259365.2145           26
2002-02-08 2768          50781.36              5
...

As you can see from this example, the CTE_OrdersDetail expression is being referenced within the definition of the CTE_Orders expression, giving you the functionality of nested CTEs. In order for a statement like this to work properly, you must declare each CTE definition before it is used in another CTE. This means that since CTE_OrdersDetail is being reference within CTE_Orders, I must declare CTE_OrdersDetail first. Otherwise, if the two definitions were switched, SQL would not be able to find the CTE_OrdersDetail object when it goes to verify the CTE_Orders expression.

Aaron
http://aaronakinsql.wordpress.com