Chronicals of… a developer in Johannesburg

This is for those who use string concatenation to join character variables and hard coded strings to form some sort of new output.

I found this when trying to throw and error and using the variable to describe the nature of the error. It will affect your PRINT, SELECT, and SET statements too.

EG.
DECLARE @Output varchar(100), @SomeVar varchar(10)
SET @SomeVar = NULL

SET @Output = ‘This will be ‘ + @SomeVar
RAISERROR (@Output, 12, -1) — or what ever you want here

The problem with the above is that trying to concatenate a null with a string will make the entire string null. So throw in an ISNULL and then we’re all set.

SET @Output = ‘This will be ‘ + ISNULL(@SomeVar,’NULL’)
RAISERROR (@Output, 12, -1) — or what ever you want here

I felt the need to search for a text string in SQL 2005 within a stored procedure.
EASY! yes… but then you get the stored procedure name, and if you want … the text. The text isn’t very useful when it is one giant string now, is it?

So here is a little script I made. It will return the stored procedure name, the line of text where the string was found – and the line number. handy you say……?

DECLARE
@TextString varchar(100)

SET
@TextString = ” ——- YOUR STRING HERE

DECLARE
@ProcName varchar(100)
DECLARE @Results TABLE(ProcName varchar(100), ProcLineNo int, ProcLine varchar(1000))

DECLARE blah CURSOR FOR
SELECT DISTINCT
[Name]
FROM
SysObjects AS O (NOLOCK)
JOIN SysComments AS C (NOLOCK) ON O.Id = C.Id WHERE [Text] LIKE ‘%’+ @TextString +’%’
ORDER BY
[Name]

OPEN blah
FETCH NEXT FROM blah INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN

CREATE TABLE #ProcLines ([lineNo] int IDENTITY(1,1), line varchar(1000))

INSERT INTO
#ProcLines
EXEC sp_helptext @ProcName

INSERT INTO
@Results
SELECT
@ProcName,
[lineNo],
line
FROM
#ProcLines
WHERE
line LIKE ‘%’ + @TextString +’%’

DROP TABLE #ProcLines

FETCH NEXT FROM blah INTO @ProcName
END

CLOSE blah
DEALLOCATE blah

SELECT
*
FROM
@Results