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 06, 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

 

Adam Machanic 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
 

Adam Machanic 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