THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know: LOB Parameters


So still no juicy bits and bytes post, only this short one to tide me over.

Another question from my Budapest class wasn’t really a question at all. It was pointing out that something was wrong in my slides! And this makes me think back about a blog post from a while back about features that change that can be easily overlooked.

In the section in my class on using LOB datatypes (text, ntext, image) vs the MAX types (varchar(MAX), etc), I mention that you can’t pass LOB types as a parameter. It was pointed out to me that this ‘fact’ is not true. I was told they use text parameters all the time in their applications. However, when I sat down to test this out, I realized I needed to put the text data into something before passing it as a parameter, and I definitely can’t declare a variable of type text.  So I need to use the varchar(max) type to hold the data to pass in, even though the procedures is created to accept a text type parameter. Here it is:


USE test;  -- use the name of any testing database you have
IF object_id('bigdata', 'P') IS NOT NULL DROP PROC bigdata;
CREATE PROC bigdata (@a text)
  SELECT 'The input parameter is ', datalength(@a), ' bytes long'

DECLARE @message varchar(max);
SELECT @message = replicate (convert(varchar(max),'Halleluja '), 100000);
EXEC bigdata @message;


So now you (and I) both know!


Published Wednesday, May 6, 2015 2:47 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



a.m. said:

You can pass it as a literal...

create proc #whatever (@i text)



select datalength(@i)



declare @s varchar(max) =

convert(varchar(max), '') +

'exec #whatever ''' + replicate(convert(varchar(max), 'a'), 10000) + ''''


May 6, 2015 8:32 PM

Kalen Delaney said:

Thanks Adam. But part of my point was that you couldn't do it without a varchar(max) in the mix somewhere!


May 6, 2015 9:02 PM

a.m. said:

There's no VARCHAR(MAX) actually in the mix anywhere in the example above. I just used it to concatenate the string so I wouldn't have to post a 10,000 character reply in your blog.

Try it yourself. EXEC #whatever '[put 10,000 characters here]'

IIRC, TEXT parameters have been allowed since at least as early as SQL Server 2000 -- which was of course prior to VARCHAR(MAX) getting added. You can see in Erland's famous "Arrays and Lists in SQL Server 2000 and Earlier" article that his iter_intlist_to_table function uses an NTEXT parameter:


May 6, 2015 9:32 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement