Posted by JayCallas on 04/13/06 21:46

I am having a problem with "SET fmtonly ON" and a function I
implemented in my database. (The function is actually Erland's
delimited string to tmp table function for purposes of passing in
multiple values to a stored procedure.)

The stored procedure which uses the function is used to return a
resultset. But sometimes the stored procedure is called with bogus
values (preceded by "SET fmtonly ON") to get the column names. And this
is where the problem happens.

If fmtonly is ON and the function is called with the list containing a
single value (with no delimiters) (as in 'item1' versus 'item1,item2')
then SQL throws a "Invalid length parameter passed to the substring
function" error.

After much testing it seems that certain parts of code in the function
gets IGNORED. (In this case it is the WHILE loop condition check.)
(Happens with IF statements also.)

I created some sample code to prove this.

--SET fmtonly ON

DECLARE @flag INT
SET @flag = 0

IF 0 > 0 -- always false
SELECT @flag = 1 -- should never execute

SET fmtonly OFF

PRINT 'DEBUG | ' + LTRIM(STR(@flag))

If executed as is the code executes correctly and the value printed is
"0". But if fmtonly is ON then for whatever reason 0 > 0 evaluates to
true and the @flag variable gets set to "1".

While this seems to be by design (I tested it on SQL 2000 sp3, sp4, and
SQL 2005 with the same results) this just seems silly to me.

[Back to original message]