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



Anonymous 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

Anonymous 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

obat diabetes said:

August 1, 2018 11:15 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:48 PM

obat sinusitis said:

August 10, 2018 7:21 PM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:57 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:56 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:19 AM

obat benjolan di tubuh said:

August 30, 2018 7:39 PM

obat wasir said:

September 4, 2018 8:26 PM

obat katarak said:

September 16, 2018 5:45 PM

obat limfoma said:


September 23, 2018 6:50 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement