Whenever possible, I use set-based solutions. But it has occurred in the past where I simply couldn't think one.

For example, this is my answer to a question on this site (no-one was able to produce a set-based alternative) : -

CREATE TABLE #SPWithVarchar

(SPName VARCHAR(MAX), --Display the stored procedure name

LinePos INT, --Display the line number to where the varchar was found.

TextPart VARCHAR(MAX)) --Display the code to where the varchar was found

--First temporary table, required to get the names of the stored-procedures

CREATE TABLE #TestTemp (Code VARCHAR(MAX), sp_name VARCHAR(MAX))

INSERT INTO #TestTemp(Code, sp_name)

--sp_helptext "hides" an ugly while loop, so a set-based version would be

--preferred. I can't think of a way of getting the line numbers without it

--unfortunately.

SELECT 'EXEC sp_helptext ''' + o.name + '''', '''' + o.name + ''''

FROM sysobjects o

WHERE o.xtype = 'P' AND o.category = 0

GROUP BY o.name

DECLARE @code VARCHAR(MAX), @sp_name VARCHAR(MAX)

DECLARE crap CURSOR FOR

SELECT code, sp_name FROM #TestTemp

OPEN crap

FETCH NEXT FROM crap INTO @code, @sp_name

WHILE @@FETCH_STATUS = 0

BEGIN

--Second temporary table, holds the results of "sp_helptext" for each individual

--stored-procedure. Uses an identity column to determine the line number of

--the stored-procedure.

--We then search this table before inserting into your #SPWithVarchar table.

CREATE TABLE #TestTemp2 (Number INT IDENTITY,Line VARCHAR(MAX), sp_name VARCHAR(MAX))

SET @code = 'INSERT INTO #TestTemp2(Line) ' + @code

--Insert results of "sp_help" into second temp table

EXECUTE ('' + @code + '')

--Updates second temporary table with the stored-procedure name.

UPDATE #TestTemp2 SET sp_name = @sp_name

--Inserts the lines from #TestTemp2 where "varchar" appears into #SPWithVarchar.

INSERT INTO #SPWithVarchar(spname, linepos, TextPart)

SELECT sp_name, number, line FROM #TestTemp2 WHERE line LIKE '%varchar%'

--Drop #TestTemp2 so it's ready to be used for the next stored-procedure.

DROP TABLE #TestTemp2

FETCH NEXT FROM crap INTO @code, @sp_name

END

--Clean up

CLOSE crap

DEALLOCATE crap

DROP TABLE #TestTemp

--Display the results

SELECT * FROM #SPWithVarchar

--DROP TABLE #SPWithVarchar