By Kalman Toth, M.Phil., M.Phil., MCDBA

December 10, 2005

Software engineers don't think of Transact-SQL as a serious language. More in the lightweight category with good sql query capacity. While you cannot do object-oriented programming in T-SQL, you can now in SQL 2005 parse trees. You don't really want to do heavy-duty algorithmic programming in T-SQL anyhow, since it is an interpreted language. If you resort to Java-like programming, make sure the stored procedure or function is not used frequently. Below is a sample function to illustrate the tremendous flexibility of the T-SQL language. Bit-wise Boolean operators are used to produce a bit switch display of byte.

This is the code sample:

use AdventureWorks
go
create function dbo.fnBinaryPattern (@Byte tinyint)
returns char(8)
as
begin
declare @Pattern char(8)
set @Pattern = ''
select @Pattern= convert(varchar,+ (@Byte & 1)/1)
+ convert(varchar,(@Byte & 2)/2)
+ convert(varchar,(@Byte & 4)/4)
+ convert(varchar,(@Byte & 8)/8)
+ convert(varchar,(@Byte & 16)/16)
+ convert(varchar,(@Byte & 32)/32)
+ convert(varchar,(@Byte & 64)/64)
+ convert(varchar,(@Byte & 128)/128)
return reverse( @Pattern )
end
go

select dbo.fnBinaryPattern(0)
go

00000000

select dbo.fnBinaryPattern(1)
go

00000001

select dbo.fnBinaryPattern(128)
go

10000000

select dbo.fnBinaryPattern(ascii('B'))
go

01000010

select dbo.fnBinaryPattern(ascii('b'))
go

01100010