varchar output greater than 8000 chars

[colabus]

hi all, I'm after a way to produce a single-lined output of a char/varchar string replicated over 8000 times.

basically I've been given a task to create a stored procedure which can accept any integer to replicate a string X times.

From what I've read the replicate() fn will covert to varchar of max 8000 bytes.

I've thought about creating a UDF to accept the varchar and int and run a loop to keep outputting but 'print' will pass an endline to the buffer which is no good for my loop.

Any help would be great on this. Thanks.

[skhanal]

You can use

replicate(x,8000)+replicate(x,8000)+.....

But you can't store the result in any variable as max sixe for that is also 8000.

In SQL 2005,you can cast the 'x' value to large data type like varchar(max) and get larger than 8000 bytes result.