TEXT output parameter in SP

Question

TEXT output parameter in SP

jbrabant

SSC Enthusiast

Points: 153
More actions
September 3, 2004 at 2:41 pm

#102186

I read in BOL that a SP can have TEXT OUTPUT parameter.
"...Text, ntext, and image parameters can be used as OUTPUT parameters."
That's nice.
I declare @var TEXT OUTPUT in a SP, but how the hell do I set the @var to be the full content of what I read with READTEXT ? I do not want only the first 8000 characters.
If TEXT is accepted then I should have a SP like this
CREATE PROCEDURE [dbo].[test]
{
@var text OUTPUT
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(myField) FROM myTable WHERE myField2=1
READTEXT myTable.myField @ptrval 0 0
Where and How do I set @var after that ??????????????????
}

Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Unfortunately you cannot do anything to @var in the Stored Procedure as text, ntext and image cannot be altered within it. Never really found an example of why MS implemented the ability to be an output variable anywhere. Basically you can only get out what you put in.

The only work around was builing a temp table with a text field and manipulating that field then doing a select on the temp table and retreive from your recordset returned.

Sorry.

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

ergh! Text! That aside I noticed in your example code that your output field is not correctly defined. It should be something like:

CREATE PROCEDURE [dbo].[test]

@var text OUTPUT

AS

-- Some SQL code

GO -- end sproc

Then, SQL BOL leads me to believe this would be your answer:

CREATE PROCEDURE [dbo].[test]

@var text OUTPUT

AS

-- set the maximum length of text we want to return

SET TEXTSIZE 64512 -- 64k

SELECT @var = myTextField FROM myTable WHERE myField2=1

GO

Julian Kuiters
juliankuiters.id.au

jbrabant SSC Enthusiast Points: 153 More actions · Answer 3

Hi Julian,

The proc was well defined, just a cut and paste error here.

I tried the SET TEXTSIZE but SQL Server gives the following error :

"The assignment operator operation cannot take a text data type as an argument."

meaning that I cannot use select @var= ... if @var is declared as TEXT

Never mind, I'll try something else.

By the way, I know that using TEXT is ugly here but how can I work nicely with XML beside saving them as a file of my server? Any ideas ?

Thanks

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

Hmm. Well if you are using well structured XML you might be able to use SQL XML to import it into native tables and use FOR XML to export it back out in xml. You also then get the benifits of being able to manipulate the data if you wish.

SQL XML is still a bit ugly in SQL 2000, but I think better support is coming in SQL 2005, and it's probably a better shot in the long run.

As a general rule I try to keep away from TEXT and IMAGE datatypes, because they don't behave like normal data types (like the select @var= problem).

Julian Kuiters
juliankuiters.id.au