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
 

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:

https://goo.gl/HQh67p Masker wajah alami untuk menghilangkan jerawat

https://goo.gl/4UJxUL Obat liver alami aman dan terbaik

https://goo.gl/j3PFHJ Obat herbal sinusitis kronis terbaik

https://goo.gl/W5W8F9 Obat bronkitis anak secara alami

https://goo.gl/2AsGLu Obat Kanker Payudara terbaru 2018

https://goo.gl/ssFQGs Cara meningkatkan nafsu makan secara alami

https://goo.gl/KSnnFb Obat herbal diabetes menurunkan gula darah

https://goo.gl/ngiymL Obat herbal kusta terbaik

https://goo.gl/ZsjaLf Obat benjolan di leher tanpa operasi

https://goo.gl/arrkfe Obat Herbal Kanker Otak tuntas hingga akar

https://goo.gl/VBFyi2 Obat TBC alami aman tanpa efek samping

https://goo.gl/ooXyhS Obat radang amandel sembuh tanpa operasi

https://goo.gl/94gZFX Obat asam urat alami tanpa efek sampinfg

https://goo.gl/Ld4VKF Obat tetes Sariawan secara alami

https://goo.gl/8v2oKL Cara mengobati mata bengkak dengan cepat

https://goo.gl/pnP3nA Obat tradisional kanker lambung terbaik

https://goo.gl/96UaW3 Obat kanker paru paru

https://goo.gl/bjzQzS Obat usus buntu alami tanpa operasi

https://goo.gl/PUDELN 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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Privacy Statement