Testing and documentation tip

[Steve Jorgensen]

Over the last few days, I've come up with and played with what's turned out to
be a really useful technique that helps to test and document stored
procedures.

Pretty much any time you write a stored procedure, you run some tests to make
sure it's working as it should, but why discard these tests when you're done
with them, or store them somewhere that only you know how to find them, and
even you could forget? Instead, why not just put the entire test script in a
comment block in the procedure definition itself? This way, to run the tests
again later, just highlight the test block in Query Analyzer, and go.
Furthermore, the embedded test code is good documentation for how the
procedure is intended to be used, and indicates how well tested it is.

The example is kind of a simple case because it doesn't interact with data,
but if it did, the test could start a transaction, prepare some data, call the
procedure, check the result, then roll back the transaction to keep the
effects from messing up data or interfering with a subsequent test.

Example:

CREATE PROCEDURE dbo.procSJPreGetLine
@Text NVARCHAR(4000),
@StartAt INTEGER OUTPUT,
@LineText NVARCHAR(4000) OUTPUT
AS

DECLARE @NextLineStart INTEGER

SELECT @StartAt=COALESCE(@StartAt,1)

IF @Text IS NULL OR @StartAt>LEN(@Text) BEGIN
SELECT @LineText=NULL, @StartAt=0
RETURN
END

SELECT @NextLineStart = CHARINDEX(dbo.fnCrLf(), @Text, @StartAt)

IF @NextLineStart>0 BEGIN
SELECT @LineText = SUBSTRING(@Text, @StartAt, @NextLineStart-@StartAt)
SELECT @StartAt = @NextLineStart + LEN(dbo.fnCrLf())
END ELSE BEGIN
SELECT @LineText = SUBSTRING(@Text, @StartAt, LEN(@Text)-@StartAt+1)
SELECT @StartAt = LEN(@Text)+1
END

/*
-- Test script:
DECLARE
@Text NVARCHAR(4000),
@StartAt INTEGER,
@LineText NVARCHAR(4000)
SELECT @Text =
'abc
def'

EXEC dbo.procSJPreGetLine @Text, @StartAt OUTPUT, @LineText OUTPUT
PRINT CASE WHEN @LineText = 'abc' THEN 'Line 1 - OK' ELSE 'Line 1 bad - ' +
@LineText END
EXEC dbo.procSJPreGetLine @Text, @StartAt OUTPUT, @LineText OUTPUT
PRINT CASE WHEN @LineText = 'def' THEN 'Line 2 - OK' ELSE 'Line 2 bad - ' +
@LineText END
*/