There are cases where using Cursor would do you more harm than good. It uses considerably large amount of resources in your server and not to mention that it can cause leaks if not used correctly (Eg. Open without corresponding Close). That’s why most T-SQL developer often suggest to avoid it whenever possible. Here is a sample code on how to travese a result set and get values of each row using a While Loop instead of a Cursor.

–identity INT (1,1) - surrogate PRIMARY KEY
declare @tempTable table(id int identity(1,1),employeeName varchar(25),employeePosition varchar(25))

insert into @tempTable
select ‘John’,'Manager’

insert into @tempTable
select ‘Joe’,'Vice President’

insert into @tempTable
select ‘Mary’,'Secretary’

declare @rowCount int
select @rowCount = count(*) from @tempTable

declare @counter int
select @counter = 1

while @counter <= @rowCount
begin

select * from @tempTable where id = @counter

select @counter = @counter + 1
end

• 0 Comments

Tags: database guide • mssql sample script

When working with MSSQL, there are cases where your simple stored procedure (SP) just won’t work when deployed to your live server even though it works perfectly on your local PC. I have encountered this problem a couple of days ago where my SP won’t work on Windows Azure SQL. I did some research and found out the as of now it does not support calling of OpenXML. And unfortunate, this function is needed in my SP. So instead of working on xml, I changed the parameter to string [delimited by a comma], split it and return as table. Here’s the code for the “split” function and how to use it in queries.
Run the code below to create the Split function:

Create FUNCTION [dbo].[Split]
(
@InputArray NVARCHAR(max),
@delimiter CHAR(1)
)
RETURNS @ReturnTable TABLE (ID INT,RValue NVARCHAR(150))
AS

BEGIN
DECLARE @TempArrayStr NVARCHAR(max)
SET @TempArrayStr = @InputArray

DECLARE @i INT
DECLARE @RValue NVARCHAR(max)
DECLARE @counter INT = 1

SET @TempArrayStr = REPLACE (@TempArrayStr, ‘ ‘, ”)
SET @i = CHARINDEX(@delimiter, @TempArrayStr)

WHILE (LEN(@TempArrayStr) > 0)
BEGIN
IF @i = 0
SET @RValue = @TempArrayStr
ELSE
SET @RValue = LEFT(@TempArrayStr, @i - 1)
INSERT INTO @ReturnTable(ID,RValue) VALUES(@counter,@RValue)
IF @i = 0
SET @TempArrayStr = ”
ELSE
SET @TempArrayStr = RIGHT(@TempArrayStr, LEN(@TempArrayStr) - @i)
SET @i = CHARINDEX(@delimiter, @TempArrayStr)
SET @counter = @counter + 1
END
RETURN
END

Sample usage:

declare @StringOfValues varchar(max)

set @StringOfValues = ‘value1,value2,value3,value4′

SELECT * FROM split(@StringOfValues, ‘,’)

Returns:

ID  RValue

1    value1
2    value2
3    value3
4    value4

SELECT * FROM split(@StringOfValues, ‘,’) where ID = 2

Returns:

ID  RValue

2    value2

• 0 Comments

Tags: database guide • mssql sample script

A holiday can either be fix or floating. Fix Holidays are those that occur on the same day each year, like Christmas. Floating Holidays on the other hand are those which may occur on different days in different years. An example would be Martin Luther King, Jr. Day which is celebrated in US every “3rd Monday of January”.

Now, what I need is a function that determines whether a given date is a holiday or not. I have a table called tbHolidays with the following columns:

HolidayIDX (int)
HolidayDate (datetime)
HolidayDayMonth (varchar(10)) - just to ease myself from having to parse day and month out of date everytime I query, I added this column and always made sure that it should be of form mm/dd
HolidayType (varchar(10)) - Fix or Floating

This table stores all the holidays defined by the user. Determining fix holidays are pretty straight-forward, where you just have to compare the dates. But floating holidays need a little trick. To make it clean and organized, I created two “scalar-valued” db functions. These are:

fn_DayOfOccurence - Returns the week number of the given date. Used in fn_IsHoliday.
fn_IsHoliday - Returns holidayID if a given date is a holiday, if not it returns Null.

fn_IsHoliday

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE Function [dbo].[fn_IsHoliday] (@DATE datetime)

RETURNS int
BEGIN
DECLARE @holiday int;

– make a query first on Fix types. Negate the year part.

Select top(1) @holiday = HolidayIDX from tbHolidays where HolidayDayMonth = SUBSTRING(convert(varchar(30),@DATE,101),1,5) and HolidayType = ‘Fix’

If @holiday > 0
Return(@holiday)
Else
Begin

– if given date is not found on Fix types, query on Floating
– all we have to do is compare day-of-occurence (e.g 3), day-of-week (e.g Monday) and month-name (e.g January)
– Basically, we’re just comparing the ‘important’ parts of a date in floating-holiday-format (e.g 3rd Monday of January)

select top(1) @holiday = HolidayIDX from tbHolidays where datename(mm, HolidayDate) =  datename(mm, @DATE) and datename(dw, HolidayDate) = datename(dw, @DATE) and dbo.fn_DayOfOccurence(HolidayDate) = dbo.fn_DayOfOccurence(@DATE) and HolidayType = ‘Floating’

If @holiday > 0
Return(@holiday)
End
Return(null)
END

fn_DayOfOccurence

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE Function [dbo].[fn_DayOfOccurence] (@DATE datetime)

RETURNS int
BEGIN
declare @dayNum as int
declare @dow as int

set @dow = 1
Select @dayNum = DATEPART(dd, @DATE)
while(@dayNum > 7)
begin
set @dow = @dow + 1
set @dayNum = @dayNum - 7
end

Return(@dow);
END

How to Use:

select fn_IsHoliday(@givenDate)

– If not Null then it’s holiday, otherwise it’s not

These functions may not be perfect, but I hope they could at least give an idea to someone out there who might need them.

• 0 Comments

Tags: mssql sample script