TSQL try catch blocks

TSQL in SQL 2k8 has lots of functionality that reminds me of excellent tools available in the .net world but not TSQL until now. Well, in some cases there was new functionality available in 2k5. One example is the "try catch" block.

BEGIN TRY

whatever sql statement you want to execute

END TRY

BEGIN CATCH

what to do when there is an error....

END CATCH


As with any type of application programming, error handling is often an overlooked "luxury" item in the eyes of everyone except the folks that have to debug when there is an error. As an application programmer that you be you! (and me). My advice is to create a generic table to hold error info when it happens. Something like this:

CREATE TABLE [dbo].[error_log](
[error_number] [varchar](500) NULL,
[error_message] [varchar](500) NULL,
[error_severity] [varchar](500) NULL,
[error_state] [varchar](500) NULL,
[error_procedure] [varchar](500) NULL,
[inserted] [datetime] default getdate() NULL,
[inserted_by] [varchar](500) default user_name() NULL,
[defactodate] [datetime] NULL,
[machine_name] [varchar](255) NULL
) ON [PRIMARY]

GO

Of course I wrap almost all of my statements in stored procedures, so you'll need a proc to perform the insert. Something like this:

create proc [dbo].[pr_ins_error_log]
@error_number varchar(500),
@error_message varchar(500),
@error_severity varchar(500),
@error_state varchar(500),
@error_procedure varchar(500),
@inserted_by varchar(500)
as
insert into error_log
([error_number],[error_message],[error_severity],[error_state],[error_procedure],inserted_by)
values
(@error_number,@error_message,@error_severity,@error_state,@error_procedure,@inserted_by)

GO

There are an array of excellent functions that will return tons of information on the error that just occured. A good example of this is error_number(). You can get creative with your own implementation but based on the objects created above, an example of how to use this would look something like this:

begin try

some generic insert statement

end try
begin catch

declare @user varchar(500) = user
exec error_logging.dbo.pr_ins_error_log error_number,error_message,error_severity,error_state,error_procedure,@user

end catch

Good luck with catching error and actual capturing them so that you have a set of "eyes" and there isn't as much guessing on what is really going wrong.

TSQL object_id replaces if exists for object creation

Database object creation and scripting has a new friend. Enter the object_id() function. I have been using it instead of the old

if exists (select somthing with a name)

drop .....

create .....

Use it like so:

if object_id('fully qualified object name','xtype from sysobjects') is not null
drop object

Definately a little cleaner that the if exists. look it up in BOL.

Please note the remark in BOL.

When a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. For example: SELECT OBJECT_ID('tempdb..#mytemptable').

I find it easier to use in my deployment scripts as well.

TSQL Functions - First and last day of month.

As outlined in a previous post located here.
I discussed needing to group by day and needed a simple in line function to set the current date to midnight. The next step was to allow grouping on the month. I needed to create function(s) that allow me to set a date to the first (or last) day of the month that is part of the date. Hre's what I came up with:

Create Function [dbo].[udf_DateTimeToFirstOfMonth] (@inputdate)
returns datetime
Begin
return DATEADD(mm, DATEDIFF(mm,0,@inputdate), 0)
End;
go

A quick look at the code reveals a familiar call to datediff for day 0 (zero) AKA 1900-01-01. This code gets the number of months since 1900-01-01 and adds then to 1900-01-01. This will give us the first day of the inputdate month.

I also needed the ability to set the dates to the last day of the input month. Here's what I came up with:

create function [dbo].[udf_DateTimeToEndOfMonth](@inputdate datetime)
returns datetime
as
begin
return DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @DateAndTime), 0)))
end

This function adds a month and subtracts a day from that date. Pretty straight forward stuff. Of course I would need a way to set dates to the first and last of the week, month, quarter, year, etc... So here they all are. Please note that some of the functions are nested.


create function [dbo].[udf_DatetimeToFirstOfQuarter](@Date datetime)
returns datetime
as
begin
declare @QuarterDate datetime
set @QuarterDate = dateadd(month, (ceiling(month(@date) / 3.0) - 1) * 3, dbo.udf_DatetimeToFirstOfYear(@Date))
return @QuarterDate
end
go

create function [dbo].[udf_DatetimeToEndOfQuarter]
(@Date datetime)
returns datetime
as
begin
declare @QuarterDate datetime

set @QuarterDate = dateadd(day, -1, dateadd(month, ceiling(month(@Date) / 3.0) * 3, dbo.udf_DatetimeToFirstOfYear(@Date)))

return @QuarterDate
end
go

create function [dbo].[udf_DateTimeToFirstOfYear](@DateAndTime datetime)
returns datetime
as
begin
return DATEADD(YEAR, DATEDIFF(YEAR, 0, @DateAndTime), 0)
end
GO

create function [dbo].[udf_DateTimeToEndOfYear]
(
@DateAndTime datetime
)
returns datetime
as
begin
return DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @DateAndTime), 0)))
end
GO

create function [dbo].[udf_DatetimeToFirstOfWeek](@Date datetime)
returns datetime
as
begin
declare @Result datetime
set @Result = dateadd(day, datediff(day, '1/1/1753', @Date) / 7 * 7 - 1, '1/1/1753')
return @Result
end
go

create function [dbo].[udf_DatetimeToQuarter](@Date datetime)
returns char(4)
as
begin
declare @Quarter char(4)

select @Quarter = case
when month(@Date) > 9 then 'Q1' + right(cast(year(@Date) + 1 as varchar(4)), 2)
when month(@Date) > 6 then 'Q4' + right(cast(year(@Date) as varchar(4)), 2)
when month(@Date) > 3 then 'Q3' + right(cast(year(@Date) as varchar(4)), 2)
else 'Q2' + right(cast(year(@Date) as varchar(4)), 2)
end

return @Quarter
end
go

create function [dbo].[udf_DatetimeToFiscalQuarter](@Date datetime)
returns char(4)
as
begin
declare @Quarter char(4)

select @Quarter = case
when month(@Date) >= '7' then 'Q1' + right(cast(year(@Date) as varchar(4)), 2)
when month(@Date) >= '1' then 'Q2' + right(cast(year(@Date) as varchar(4)), 2)
when month(@Date) >= '3' then 'Q3' + right(cast(year(@Date) as varchar(4)), 2)
else 'Q4' + cast(year(@Date) as varchar(4))
end

return @Quarter
end

GO

Good luck with dates!

TSQL Function - Current Date to Midnight

I find that I perform an awful lots of aggregations for the reports that I am writing these days. In Tsql this basically means plenty of sum, count, avg etc. But it also means that I am using alot of group by. These days I am grouping by date and each given day. I needed a TSQL inline function to set the current date to midnight. Here's what I cam up with in 1 line!

Create function dbo.SetDatetoMidnight(@inputdate datetime)
returns datetime
begin
return dateadd(d,datediff(d,0,@inputdate),0)
end

Quick breakdown of the code here. If you run the following select statement "select datediff(d,0,getdate())" in query analyzer or SQL Server Management Studio you will see an int value result depending on the actual date you use as an input parameter. This int value is the number of days between day 0 (zero) and the input date. 1900-01-01 is widely recognized as day 0. Don't believe me? Run this test:

declare @d datetime
set @d = GETDATE() -- or whatever date you want.

select @d

declare @num_of_days int
set @num_of_days = (select datediff(d,0,getdate()))

select @num_of_days

select DATEADD(d, - @num_of_days ,@d)


Now for the datadd portion of the function. By adding the number of days that is represented by a datediff between the inputdate and day zero I get the same number of days between day zero and the input date. this effectively strips off the time portion and gives me the YYYY-MM-DD with a midnight representation for time.

select sum(sales_number),dbo.SetDatetoMidnight(datetime_column)
from sales_data_table
where datetime_column between "some start" and "some end"
group by dbo.SetDatetoMidnight(datetime_column)\

I have also created functions for month, quarter and year for longer term reporting. Those will be posted in the coming days and weeks. Good luck with dates and grouping.