Friday, September 15, 2006

Do Not Concatenate VARCHAR and VARCHAR(MAX) Variables

Do Not Concatenate VARCHAR and VARCHAR(MAX) Variables, what happens is that the whole string will be implicitly converted to varchar(8000)

Run these examples to see what I mean

declare @v varchar(max)
select @v = (cast('a' as varchar)) + replicate('a', 9000)

select len(@v)
--8000
GO

declare @v varchar(max)
select @v = (cast('a' as varchar(1))) + replicate('a', 9000)

select len(@v)
--8000
GO

declare @v varchar(max)
select @v = (cast('a' as varchar)) +replicate (cast('a' as varchar(max)), 9000)

select len(@v)
--9001
GO

declare @v varchar(max)
select @v = (cast('a' as varchar(1))) + replicate(cast('a' as varchar(max)), 9000)

select len(@v)
--9001
GO


Or how about this? If you don't convert to varchar(max) while doing the LEN function it returns 8000

declare @v varchar(max)
select @v = replicate('a', 9000)
select len(@v)


declare @v varchar(max)
select @v = replicate(cast('a' as varchar(max)), 9000)
select len(@v)

4 comments:

Anonymous said...

The problem is the replicate function. Because 'a' is not varchar(max), the result is not varchar(max).


On this blog (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1374.entry) I posted a little query using sql_variants to store results so you can see the types:

declare @var sql_variant
set @var = replicate(cast('a' as varchar(max)), 9)

select cast(sql_variant_property(@var,'BaseType') as varchar(20)) + '(' +
cast(cast(sql_variant_property(@var,'MaxLength') as int) / --for unicode types, divide by 2
case when cast(sql_variant_property(@var,'BaseType') as varchar(20)) like 'N%'
then 2 else 1 end as varchar(10)) + ')'

--------------------------------
varchar(8000)

Try it like this:

declare @var sql_variant
set @var = replicate(cast('a' as varchar(max)), 9)

And you get this:

Msg 206, Level 16, State 2, Line 2
Operand type clash: varchar(max) is incompatible with sql_variant

The same result comes from:

declare @var sql_variant
set @var = cast('a' as varchar(max)) + 'a'

So it is not the number of values, but rather the type of what is being replicated.... Of course, replicate should probably warn you if you try to replicate too many characters, huh?

Denis said...

That's interesting when I do this

declare @var sql_variant
set @var = replicate(1, 9000)

select cast(sql_variant_property(@var,'BaseType') as varchar(20)) + '(' +
cast(cast(sql_variant_property(@var,'MaxLength') as int) / --for unicode types, divide by 2
case when cast(sql_variant_property(@var,'BaseType') as varchar(20)) like 'N%'
then 2 else 1 end as varchar(10)) + ')'

the datatype is not int but varchar

Denis said...

Also run these 2 example

declare

@a int,
@b varchar(500),
@c int,
@d datetime,
@e datetime,
@f varchar(500)

DECLARE @SQL varchar(max)
select @a =1,@b ='xxx',@c =7,@d='20060918',@e ='20060918',@f ='zzz'

SELECT @SQL= '
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(50)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(50)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(50)) + ''' AND ''' + cast(@e as
varchar(50)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


'
SELECT LEN(@sql)

GO
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************
--*****************************************************************************************************

declare

@a int,
@b varchar(500),
@c int,
@d datetime,
@e datetime,
@f varchar(500)

DECLARE @SQL varchar(max)
select @a =1,@b ='xxx',@c =7,@d='20060918',@e ='20060918',@f ='zzz'

SELECT @SQL= '
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
AND a = '+ cast(@a as varchar(max)) +'
AND b IN ('+ @b +')
AND c = '+ cast(@c as varchar(max)) +'
' + @f + '
AND datetime BETWEEN ''' + cast(@d as varchar(max)) + ''' AND ''' + cast(@e as
varchar(max)) + '''
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
) a
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa


'
SELECT LEN(@sql)

GO

Anonymous said...

This is amusing...