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