THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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
GO
IF object_id('bigdata', 'P') IS NOT NULL DROP PROC bigdata;
GO
CREATE PROC bigdata (@a text)
AS
  SELECT 'The input parameter is ', datalength(@a), ' bytes long'
RETURN;

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

 

So now you (and I) both know!

~Kalen

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

Comments

 

a.m. said:

You can pass it as a literal...

create proc #whatever (@i text)

as

begin

select datalength(@i)

end

go

declare @s varchar(max) =

convert(varchar(max), '') +

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

exec(@s)

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!

~Kalen

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:

http://www.sommarskog.se/arrays-in-sql-2000.html

--Adam

May 6, 2015 9:32 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Privacy Statement