T-SQL can be a powerful tool for developers if exploited. It allows you to programatically manipulate your data on the “database-level”. This post will show a simple T-SQL script on how to create a recursive user-defined function [UDF] that can be used application-wide via a simple query. To demonstrate, we will use one of the most common mathematical problems in which a recursive algorithm can be applied - solving for factorial.

Create a UDF

Create Function [dbo].[Factorial]
(
@n int
)
Returns int

AS
Begin

declare @factorial int

if @n = 0
set @factorial = 1
else
set @factorial = @n * dbo.Factorial(@n - 1) –call it recursively

return @factorial

You can then simply call it in any of your queries like:

select dbo.Factorial(6) –returns 720

• 0 Comments

Tags: mssql sample script • t-sql • user-defined function

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

Sometimes, we wanted to return our query result as datatable for easy handling of data [probably making it as a datasource of a control].

Here’s a simple function:

Public Function GetDataTable(ByVal conStr As String, ByVal qryStr As String) As DataTable

Dim dt As DataTable = New DataTable()
Dim con As SqlConnection = New SqlConnection(conStr)

Dim adapter As SqlDataAdapter = New SqlDataAdapter(qryStr, con)

Try
‘fill datatable
adapter.Fill(dt)
Catch ex As SqlException
System.Web.HttpContext.Current.Response.Write(”SQL Error!!!”)
Finally
con.Close()
End Try

‘return the data table.
Return dt

End Function

Then, you can use it like this:

myDataGrid.DataSource = GetDataTable(myDbConnection, myQuery)
myDataGrid.DataBind()

• 0 Comments

Tags: .Net asp .net tutorial • mssql sample script • vb .net sample code

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