Archive for category SQL Development

Optional Column Updates with your Stored Procedures

Recently, I needed a way to quickly update some bulk data based on a part number… I started by building 2 stored procedure that each updated different columns, but a few minutes later I realized I was going to need at least 2 more permutations of my update procedure since there were more columns combinations I needed to update. I didn’t want to maintain a bunch of SP for different types of updates, I just wanted a handy update SP that would update a column/field with data when I passed it in or ignored it if left it blank/null.

I came up with a T-SQL Optional Update Parameter solution, which is based on using the “COALESCE” function along with typed null values. Normally, you pass in a bunch of values and it returns the first non null value, but… There is a hidden gem, you can pass in typed null values and it will return null… Knowing this, I created the code below.

-- CREATE DEMO TABLE
CREATE TABLE [dbo].[Product](
	[PartNumber] [nvarchar](20) NULL,
	[Description] [nchar](20) NULL,
	[Comments] [nchar](20) NULL
) ON [PRIMARY];

GO

-- DEMO TABLE DATA
PRINT '';
PRINT 'INSERT SAMPLE DATA';
PRINT '---------------------------------------------------------- '
INSERT INTO [PRODUCT] VALUES (N'PART1', N'PART 1 Description', N'PART 1 Comment');

GO

-- CREATE DEMO PROCEDURE
CREATE PROCEDURE [dbo].[spProduct_Update]
	@PartNumber AS NVARCHAR(20),
	@Description AS NCHAR(20),
	@Comments AS NCHAR(20)
AS
BEGIN
	DECLARE @BEFORE AS NVARCHAR(200);
	DECLARE @AFTER AS NVARCHAR(200);

	SELECT @BEFORE = 'BEFORE: ' + [PartNumber] + '  |  ' + [Description] + '  |  ' + [Comments] FROM [Product] WHERE [PartNumber] = @PartNumber;

	UPDATE [Product]
		SET [Description] = COALESCE(@Description,[Description]),
			[Comments] = COALESCE(@Comments,[Comments])
	WHERE [PartNumber] = @PartNumber;

	SELECT @AFTER = ' AFTER: ' + [PartNumber] + '  |  ' + [Description] + '  |  ' + [Comments] FROM [Product] WHERE [PartNumber] = @PartNumber;

	PRINT @BEFORE;
	PRINT @AFTER;

END

GO

-- Fails : Both values are non-typed null values
PRINT '';
PRINT '';
PRINT 'FAIL = COALESCE(null,null)';  --2 non-typed null value

GO

DECLARE @MyField AS NVARCHAR(50);
SET @MyField = COALESCE(null, null);
PRINT @MyField;

GO

-- Pass : The second value is a typed null value
PRINT '';
PRINT '';
PRINT 'PASS = COALESCE(null,<typed null parameter>)';  --1 non-typed null value, 1 typed null value

GO

DECLARE @MyField AS NVARCHAR(50);
DECLARE @MyTypedParameter AS NVARCHAR(50);
SET @MyField = COALESCE(null, @MyTypedParameter);
PRINT @MyField;

GO

-- Using the COALESCE with a typed parameter to create an optional "column" update.
PRINT '';
PRINT '---------------------------------------------------------- '
PRINT 'NO UPDATES';
EXEC spProduct_Update 'PART1', null, null;

PRINT '';
PRINT '---------------------------------------------------------- '
PRINT 'UPDATE DESCRIPTION ONLY';
EXEC spProduct_Update 'PART1', 'PART 1 *** UPDATE DESCRIPTION ***', null;

PRINT '';
PRINT '---------------------------------------------------------- '
PRINT 'UPDATE COMMENTS ONLY';
EXEC spProduct_Update 'PART1', null, 'PART 1 *** UPDATE COMMENT ***';

PRINT '';
PRINT '---------------------------------------------------------- '
PRINT 'UPDATE DESCRIPTION & COMMENTS';
EXEC spProduct_Update 'PART1', '*** UPDATE BOTH ***', '*** UPDATE BOTH ***';

-- DELETE DEMO PROCEDURE
DROP PROCEDURE [dbo].[spProduct_Update];

GO

-- DELETE DEMO TABLE
DROP TABLE [dbo].[Product];

GO

In a nutshell, we can only use COALESCE with typed nullable values… this means COALESCE(null,null,null) will fail because null is not a defined type, but COALESCE(@nullvalue, @nullvalue, @nullvalue) will work since we had to declare a type of @nullvalue [e.g. DECLARE @nullvalue AS nvarchar(20)]

INSERT SAMPLE DATA
---------------------------------------------------------- 
(1 row(s) affected)
 
FAIL = COALESCE(null,null)
Msg 4127, Level 16, State 1, Line 3
At least one of the arguments to COALESCE must be a typed NULL.
 
 
PASS = COALESCE(null,<typed null parameter>)
 
---------------------------------------------------------- 
NO UPDATES

(1 row(s) affected)
BEFORE: PART1  |  PART 1 Description    |  PART 1 Comment      
 AFTER: PART1  |  PART 1 Description    |  PART 1 Comment      
 
---------------------------------------------------------- 
UPDATE DESCRIPTION ONLY

(1 row(s) affected)
BEFORE: PART1  |  PART 1 Description    |  PART 1 Comment      
 AFTER: PART1  |  PART 1 *** UPDATE DE  |  PART 1 Comment      
 
---------------------------------------------------------- 
UPDATE COMMENTS ONLY

(1 row(s) affected)
BEFORE: PART1  |  PART 1 *** UPDATE DE  |  PART 1 Comment      
 AFTER: PART1  |  PART 1 *** UPDATE DE  |  PART 1 *** UPDATE CO
 
---------------------------------------------------------- 
UPDATE DESCRIPTION & COMMENTS

(1 row(s) affected)
BEFORE: PART1  |  PART 1 *** UPDATE DE  |  PART 1 *** UPDATE CO
 AFTER: PART1  |  *** UPDATE BOTH ***   |  *** UPDATE BOTH *** 

How to build a Date Dimension Table for OLAP

OLAP Date Dimension Table Script

This query will build a table called “OLAP_DATE_DIMENSION”, based upon a starting and ending date. As you can see all of the work is done by the “DATENAME” and “DATEPART” functions in MS SQL Server. If you want information on using these, Query Analyzer for MS SQL Server 2000 is great! I’m not sure what happened with MS, but post MS SQL Server the built in help documents suck and your better of using Google.

** This query has a little hard coded logic for my specific company’s fiscal year (April – March), adjust accordingly.

-- =============================================
-- Build Dimension Date Table
-- =============================================
DECLARE @StartDate as smalldatetime, @EndDate as smalldatetime

SET @StartDate = '04/01/2010'
SET @EndDate = '03/31/2011'

BEGIN
SELECT
DATEPART(dy, @StartDate) as DAY_OF_YEAR,
CASE
WHEN DATENAME(qq,@StartDate)-1=0 THEN
4
ELSE
DATENAME(qq,@StartDate)-1
END AS FISCAL_PERIOD,
DATENAME(m,@StartDate) AS MONTH_DESC,
DATEPART(m,@StartDate) AS MONTH_NUM,
DATEPART(qq,@StartDate) AS QUARTER_NUM,
CONVERT(smalldatetime, CONVERT(CHAR(10),@StartDate,110)) AS SALES_DATE,
REPLACE(CONVERT(CHAR(10),@StartDate,06),' ','-') AS SALES_DATE_SPL,
DATEPART(yy,@StartDate) AS YEAR_NUM,
DATEPART(d,@StartDate) AS DAY_OF_MONTH,
CASE
WHEN DATEPART(m,@StartDate)&lt; 4 THEN
DATENAME(yy,@StartDate)-1
ELSE
DATENAME(yy,@StartDate)
END AS FISCAL_YEAR,
CASE
WHEN DATEPART(m,@StartDate)&gt;3 THEN
DATEPART(m,@StartDate)-3
ELSE
12-(3-DATEPART(m,@StartDate))
END AS FISCAL_MONTH
INTO OLAP_DATE_DIMENSION

SELECT @StartDate = @StartDate + 1

END

WHILE (@StartDate &lt;= @EndDate)
    BEGIN
     BEGIN
     INSERT INTO OLAP_DATE_DIMENSION SELECT
     DATEPART(dy, @StartDate) as DAY_OF_YEAR,
     CASE
     WHEN DATENAME(qq,@StartDate)-1=0 THEN
     4
     ELSE
     DATENAME(qq,@StartDate)-1
     END AS FISCAL_PERIOD,
     DATENAME(m,@StartDate) AS MONTH_DESC,
     DATEPART(m,@StartDate) AS MONTH_NUM,
     DATEPART(qq,@StartDate) AS QUARTER_NUM,
         CONVERT(smalldatetime, CONVERT(CHAR(10),@StartDate,110)) AS SALES_DATE,
     REPLACE(CONVERT(CHAR(10),@StartDate,06),' ','-') AS SALES_DATE_SPL,
     DATEPART(yy,@StartDate) AS YEAR_NUM,
     DATEPART(d,@StartDate) AS DAY_OF_MONTH,
     CASE
     WHEN DATEPART(m,@StartDate)&lt; 4 THEN
     DATENAME(yy,@StartDate)-1
     ELSE
     DATENAME(yy,@StartDate)
     END AS FISCAL_YEAR,
     CASE
     WHEN DATEPART(m,@StartDate)&gt;3 THEN
     DATEPART(m,@StartDate)-3
     ELSE
     12-(3-DATEPART(m,@StartDate))
     END AS FISCAL_MONTH
     END
     SELECT @StartDate = @StartDate + 1
    END