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))
INSERT @Roles
SELECT 'Smith', 'O' UNION ALL
SELECT 'Smith', 'D' UNION ALL
SELECT 'Jones', 'O' UNION ALL
SELECT 'White', 'D' UNION ALL
SELECT 'Brown', 'X'
SELECT Person,
SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty
FROM @Roles
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
(
@a INT,
@b INT
)
RETURNS INT
AS
BEGIN
DECLARE @c INT
IF @a IS NULL OR @b IS NULL OR (@a = 0 AND @b = 0)
RETURN NULL
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
ELSE
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...