|
|
SQL Server T-SQL User-Defined Function of the Week
Volume 1 Number 32 June 24, 2003
Testing UDFs with Stored Procedures
Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm
Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
This newsletter continues with last week's testing theme. The
tests that I showed last week were embedded in the function
header comment. The header comment is a particularly useful
place to keep the tests because they go everywhere the function
goes. That is they're hard to lose.
Although there were 8 tests in last week's UDF, sometimes you
can't do everything you want in the header. Not only that, but
the only way to run the tests in the header is manually. They're
set up to be selected and run from SQL Query Analyzer.
More elaborate test for the UDF should be created in
a stored procedure. The testing SP can do many things that
a UDF can't such as dynamic SQL and PRINT statements.
You may have received the message about the bug that was found
in udf_Txt_CharIndexRev last week. As I revised the UDF I
created two stored procedures to test the UDF. The first SP
runs a single test on udf_Txt_CharIndexRev. The second one
has all the tests that I want to run.
All of my testing UDFs begin with TEST_ followed by the UDF name.
The SP that runs an individual test is a subroutine of that SP
and I just append _SUB_OneTest to the name so that it's easy to
find in Enterprise Manager or Query Analyzer.
Here's the CREATE PROC script for the subroutine, which should
be created first:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked BIT OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs BIT = 0
, @TestNo int -- The Test Number
, @Worked BIT OUTPUT -- 1 when this test worked
, @TestSource varchar(8000) -- The string being searched
, @TestTarget varchar(8000) -- String we're searching for
, @Answer int -- Correct answer
AS
/*
* Subroutine for test driver for TEST_udf_Txt_CharIndexRev.
* Performs one test
****************************************************************/
DECLARE @FuncAnswer int -- Answer the function returned
SET @FuncAnswer = dbo.udf_Txt_CharIndexRev (@TestTarget
, @TestSource)
SET @Worked = CASE WHEN @Answer = @FuncAnswer THEN 1 ELSE 0 END
SET @AllWorked = CASE WHEN @AllWorked=1 AND @Worked=1
THEN 1 ELSE 0 END
IF @Worked=1 AND @PrintSuccessMsgs=1
PRINT 'Test ' + CONVERT(varchar(10), @TestNo) + ' Worked'
ELSE BEGIN
PRINT '/----------------------------------------------------\'
PRINT 'Test ' + CONVERT(varchar(10), @TestNo) + ' Failed'
PRINT ' Searching for ->' + @TestTarget + '<- in '
PRINT ' 123456789012345678901234567890123456789012345678'
PRINT ' ->' + @TestSource + '<-'
PRINT ' Correct = ' + CONVERT(varchar(10), @Answer)
PRINT ' Returned = ' + CONVERT(varchar(10), @FuncAnswer)
PRINT '\----------------------------------------------------/'
END
GO
\-------Stop copying above this line ---------------------------/
There's no GRANT statement at the end of the script because
there's no need to grant permissions to use this routine to any
one other than dbo.
Next the main TEST_ sp is created. My top level tests have
a output parameter @AllWorked, which summarizes the result
of all the tests. The @PrintSuccessMsgs parameter can be used
to suppress messages that show that a test succeeded, if you only
want to see messages about failures.
Here's the CREATE PROC for the main TEST_ SP:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROC TEST_udf_Txt_CharIndexRev
@AllWorked BIT OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs BIT = 0
AS
/*
* Test driver for udf_Txt_CharIndexRev. Conducts a variety of tests
* that try and find any flaws in the routine.
*
* Test:
DECLARE @AllWorked BIT, @RC int
EXEC @RC = TEST_udf_Txt_CharIndexRev @AllWorked OUTPUT, 1
PRINT 'Test TEST_udf_Txt_CharIndexRev @RC = ' + CONVERT(char(10), @RC)
+ ' @AllWorked = ' + CONVERT(VARCHAR(1), @ALLWORKED)
****************************************************************/
DECLARE @TestNo int -- Test Number
, @Worked BIT -- Did the test worked.
, @FuncAnswer int -- Answer the function returned
SELECT @AllWorked = 1 -- Assume the best
-- basic test, worked in the original version
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 0 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, 'f123 asdasfdfdfddfjas ' -- The string being searched
, 'fdf' -- String we're searching for
, 13 -- Correct answer
-- basic test, had been a problem with earlier version
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 1 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, 'f123 asdasfdfdfddfjas ' -- The string being searched
, 'sf' -- String we're searching for
, 10 -- Correct answer
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 2 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, 'f123 asdasfdfdfddfjas ' -- The string being searched
, 'fj' -- String we're searching for
, 18 -- Correct answer
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 3 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, 'f123 asdasfdfdfddfjas ' -- The string being searched
, 's' -- String we're searching for
, 21 -- Correct answer
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 4 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, 'f123 asdasfdfdfddfjas ' -- The string being searched
, 's ' -- String we're searching for
, 21 -- Correct answer
-- from start of string
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 5 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, 'C:\temp\ab.txt' -- The string being searched
, 'C' -- String we're searching for
, 1 -- Correct answer
-- single char that is both at the start and the middle
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 6 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, 'f123 asdasfdfdfddfjas ' -- The string being searched
, 'f' -- String we're searching for
, 18 -- Correct answer
-- At end of the string
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 7 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, '123456789X' -- The string being searched
, 'X' -- String we're searching for
, 10 -- Correct answer
-- Target not in the string
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 8 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, '12347' -- The string being searched
, 'AB' -- String we're searching for
, 0 -- Correct answer
-- Full string
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 9 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, '12347' -- The string being searched
, '12347' -- String we're searching for
, 1 -- Correct answer
-- Target bigger than source
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 10 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, '12347' -- The string being searched
, '123478' -- String we're searching for
, 0 -- Correct answer
-- Target one shy of source
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 11 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, '12347' -- The string being searched
, '1234' -- String we're searching for
, 1 -- Correct answer
-- Target starts at 2nd char and goes to end
EXEC TEST_udf_Txt_CharIndexRev_SUB_OneTest
@AllWorked OUTPUT -- 1 when all tests worked.
, @PrintSuccessMsgs
, 12 -- The Test Number
, @Worked OUTPUT -- 1 when this test worked
-- 12345678901234567890123
, '12347' -- The string being searched
, '2347' -- String we're searching for
, 2 -- Correct answer
GO
\-------Stop copying above this line ---------------------------/
The header comment of the main test SP has a short script that
runs the test. I've copied it and I'm running it against the
original version of udf_Txt_CharIndexRev
/------- Start copying below this line -------------------------\
DECLARE @AllWorked BIT, @RC int
EXEC @RC = TEST_udf_Txt_CharIndexRev @AllWorked OUTPUT, 1
PRINT 'Test TEST_udf_Txt_CharIndexRev @RC = ' + CONVERT(char(10), @RC)
+ ' @AllWorked = ' + CONVERT(VARCHAR(1), @ALLWORKED)
\-------Stop copying above this line ---------------------------/
(Results)
Test 0 Worked
/----------------------------------------------------\
Test 1 Failed
Searching for ->sf<- in
123456789012345678901234567890123456789012345678
->f123 asdasfdfdfddfjas <-
Correct = 10
Returned = 9
\----------------------------------------------------/
/----------------------------------------------------\
Test 2 Failed
Searching for ->fj<- in
123456789012345678901234567890123456789012345678
->f123 asdasfdfdfddfjas <-
Correct = 18
Returned = 17
\----------------------------------------------------/
/----------------------------------------------------\
Test 3 Failed
Searching for ->s<- in
123456789012345678901234567890123456789012345678
->f123 asdasfdfdfddfjas <-
Correct = 21
Returned = 19
\----------------------------------------------------/
/----------------------------------------------------\
Test 4 Failed
Searching for ->s <- in
123456789012345678901234567890123456789012345678
->f123 asdasfdfdfddfjas <-
Correct = 21
Returned = 20
\----------------------------------------------------/
Test 5 Worked
/----------------------------------------------------\
Test 6 Failed
Searching for ->f<- in
123456789012345678901234567890123456789012345678
->f123 asdasfdfdfddfjas <-
Correct = 18
Returned = 16
\----------------------------------------------------/
Test 7 Worked
Test 8 Worked
/----------------------------------------------------\
Test 9 Failed
Searching for ->12347<- in
123456789012345678901234567890123456789012345678
->12347<-
Correct = 1
Returned = 5
\----------------------------------------------------/
Test 10 Worked
/----------------------------------------------------\
Test 11 Failed
Searching for ->1234<- in
123456789012345678901234567890123456789012345678
->12347<-
Correct = 1
Returned = 4
\----------------------------------------------------/
/----------------------------------------------------\
Test 12 Failed
Searching for ->2347<- in
123456789012345678901234567890123456789012345678
->12347<-
Correct = 2
Returned = 5
\----------------------------------------------------/
Test TEST_udf_Txt_CharIndexRev @RC = 0 @AllWorked = 0
(End of Results)
OUCH! Major problems. The test is stacked against the original
version of the UDF and includes every test that I found was a
problem as I re-examined it. It actually worked in plenty of
situations. However, I don't want to leave it around.
Here's an ALTER script to change the UDF. If you've never
added it in the first place then:
a) Consider yourself lucky.
b) Change the command ALTER to CREATE before you run the script.
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER FUNCTION dbo.udf_Txt_CharIndexRev (
@SearchFor varchar(255) -- Sequence to be found
, @SearchIn varchar(8000) -- The string to be searched
) RETURNS int -- Position from the back of the string where
-- @SearchFor is found in @SearchIn
WITH SCHEMABINDING
/*
* Searches for a string in another string working from the back.
* It reports the position (relative to the front) of the first
* such expression it finds. If the expression is not found, it
* returns zero.
*
* Equivalent Template:
CASE
WHEN CHARINDEX(
, ) > 0
THEN LEN()
- CHARINDEX(REVERSE()
, REVERSE ()) + 1
ELSE 0 END
* Example:
select dbo.udf_Txt_CharIndexRev('\', 'C:\temp\abcd.txt')
*
* Test:
PRINT 'Test 1 ' + CASE WHEN 8=
dbo.udf_Txt_CharIndexRev ('\', 'C:\temp\abcd.txt')
THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 2 ' + CASE WHEN 0=
dbo.udf_Txt_CharIndexRev ('*', 'C:\tmp\d.txt')
THEN 'Worked' ELSE 'ERROR' END
-- 12345678901234567890
PRINT 'Test 3 ' + CASE WHEN 18=
dbo.udf_Txt_CharIndexRev('fj', 'f123 asdasfdfdfddfjas ')
THEN 'Worked' ELSE 'ERROR' END
*
* Test Script: TEST_udf_Txt_CharIndexRev
*
* History
* WHEN WHO WHAT
* ---------- ---------- -----------------------------------------
* 2002-12-10 ASN Initial Coding
* 2003-06-16 ASN Fixed problem with Test 3 by using
DATALENGTH instead of LEN
*
* © Copyright 2002-3 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as in the T-SQL UDF of the week Vol 1 #10 and #32
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
DECLARE @Result int
, @StringLen int
, @ReverseIn varchar(8000)
, @ReverseFor varchar(255)
SELECT @ReverseIn = REVERSE (@SearchIn)
, @ReverseFor = REVERSE(@SearchFor)
, @StringLen = DATALENGTH(@SearchIn)
SELECT @Result = CHARINDEX(@ReverseFor, @ReverseIn)
-- return the position from the front of the string
IF @Result > 0
SET @Result = @StringLen - (@Result
+ DATALENGTH(@SearchFor)
- 2
)
-- ENDIF
RETURN @Result
END
GO
\-------Stop copying above this line ---------------------------/
Now that the corrected function is in place, let's rerun the
tests. The script is the same as before:
/------- Start copying below this line -------------------------\
DECLARE @AllWorked BIT, @RC int
EXEC @RC = TEST_udf_Txt_CharIndexRev @AllWorked OUTPUT, 1
PRINT 'Test TEST_udf_Txt_CharIndexRev @RC = ' + CONVERT(char(10), @RC)
+ ' @AllWorked = ' + CONVERT(VARCHAR(1), @ALLWORKED)
\-------Stop copying above this line ---------------------------/
(Results)
Test 0 Worked
Test 1 Worked
Test 2 Worked
Test 3 Worked
Test 4 Worked
Test 5 Worked
Test 6 Worked
Test 7 Worked
Test 8 Worked
Test 9 Worked
Test 10 Worked
Test 11 Worked
Test 12 Worked
Test TEST_udf_Txt_CharIndexRev @RC = 0 @AllWorked = 1
(End of Results)
AHHHH! Much better.
The test script is the same but the result is better this
time. All the tests work. Twelve tests may not cover every
possible situation. If you think of another, drop me an e-mail
at udf@novicksoftware.com. TIA
Please share this newsletter with anyone interested in
SQL Server.
+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
|
|
