April 2009 Blog Posts
I was browsing some of my older solutions, and I stumbled across this interesting piece.
The object was to mark any employees with Duty 'B' for those employees having both Duty 'O' and Duty 'D'.
DECLARE @Roles TABLE (Person VARCHAR(5), [Role] VARCHAR(1))
SELECT 'Smith', 'O' UNION ALL
SELECT 'Smith', 'D' UNION ALL
SELECT 'Jones', 'O' UNION ALL
SELECT 'White', 'D' UNION ALL
SELECT 'Brown', 'X'
SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty
WHERE [Role] IN ('D', 'O')
GROUP BY Person
This approach is not dependent on the 32-level recursion as most other algorithms are for this problem.
CREATE FUNCTION dbo.fnGCD
DECLARE @c INT
IF @a IS NULL OR @b IS NULL OR (@a = 0 AND @b = 0)
IF @a = 0 OR @b = 0
RETURN ABS(@a) + ABS(@b)
IF ABS(@a) < ABS(@b)
SELECT @c = ABS(@a),
@a = ABS(@b),
@b = @c
SELECT @a = ABS(@a),
@b = ABS(@b)
SET @c = @a % @b
WHILE @c > 0
SELECT @a = @b,
@b = @c,
@c = @a %...
With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement.
The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there.
With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML?
Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query!
In this example I am showing you how to audit certain...
Today I am showing you the difference between @@ERROR, BEGIN TRY/CATCH and XACT_ABORT.
The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process.
First I am going to show you the ordinary @@ERROR check which most of you are used to.
IF OBJECT_ID('uspTest_2000') IS NOT NULL
Today I am going to show you one way to calculate a decimal year. This is not a easy as it sounds because some years (leap years) include a leap day so the number of days in a year is not consistent.
The longest defined consistent time period there is, is a week. A week is seven days or 168 hours. However, a week can be defined to have different start weekday in different regions of the world. Enough said about that.
First, set up a test environment by declaring and setting two datetime variables like this
declare @f datetime...