When you want to compare two strings that may have case differences on a case-insensitive server, this script will quickly and easily do the compare for you. It returns 0 if strings are equal, 1 if not. Feel free to modify/reuse in your code.
/*
©Copyright 2003 Mike McManus (mmcmanus@cierant.com) All Rights Reserved
*/
/* --------------------- For case-sensitive compare of string values on case-insensitive SQL Server -------------------------- *//* --------------------------------- Will Return 0 if parms are equal, 1 if not ---------------------------------------------- */
Create Procedure CaseCompare @String1 Varchar(100),@String2 Varchar(100)
As
Declare
@Pos int,
@String1_Ascii int,
@String2_Ascii int,
@ReturnVal int,
@String1_len int,
@String2_len int
/* -------------------------------------------------- Initialize Vars ----------------------------------------------------------- */Set NOCOUNT On
Set @String1_Ascii = 0
Set @String2_Ascii = 0
Set @Pos = 1
Set @ReturnVal = 0
/* ------------------------------------------------------------------------------------------------------------------------------ */
If ((@String1 Is Null) Or (@String2 Is Null))
Begin
Select @ReturnVal = 1
End
Else
Begin
Select @String1_len = len(@String1), @String2_len = len(@String2)
If (@String1_len != @String2_len)
Begin
Select @ReturnVal = 1
End
Else
Begin
/* ------- Compare Strings one character at a time until finished, or until they don't match --------------------- */
While ((@Pos <= Datalength(@String1)) And (@ReturnVal = 0))
Begin
Select @String1_Ascii = ASCII(SubString(@String1, @Pos, 1)), @String2_Ascii = ASCII(SubString(@String2, @Pos, 1))
Select @ReturnVal = @ReturnVal + (@String1_Ascii - @String2_Ascii)
Set @Pos = @Pos + 1
End
End
End
/* --- To make sure we get a non-negative return value --- */
If (@ReturnVal != 0)
Begin
Set @ReturnVal = 1
End
/* ------------------------------------------------------- */
-- If testing, or running stand-alone, do Select. To execute from other stored proc or function, do Return.
-- Select @ReturnVal
Return @ReturnVal
go 
A short but interesting article, the author has figured out a way to create a PDF from a stored procedure without using a third party library.
2019-09-20 (first published: 2003-08-26)
72,044 reads
Robert is back this week with a look at the text data type. Not the easiest thing to work with, but sometimes a varchar just doesn't give you the range you need.
2003-05-07
26,972 reads
Fast Track to MDX gives readers all the necessary background information needed to write useful, powerful MDX expressions and introduces the most frequently used MDX functions and constructs. No prior knowledge is assumed and examples are used throughout the book to rapidly develop MDX skills to the point where a reader can solve real business problems. A CD containing examples from within the book, and a time-limited version of ProClarity, is included
2003-05-01
2,698 reads
Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing!
2007-10-02 (first published: 2003-04-25)
50,211 reads
In this article Andy looks at one way you can separate your presentation code from your data when you generate HTML tables for simple reports. Lots of code examples so you can see how it works.
2002-08-05
5,904 reads