Text data type in Stored Procs

I am a bit confused about the text data type...what we want to do is have a stored procedure to be called from a .Net program that will accept an xml document. Since the xml could possibly be very long, i was thinking of using the text data type, but according to BOL, you cant use text as a local variable...

The thing is, when i did a very quick little proc that doesnt do anything, i was able to have TEXT as the datatype of an input parameter...so, whats the story?!?! can i use it as the input parameter? is an input parameter considered different to a local variable?!

Thanks,

Ben

'I reject your reality and substitute my own' - Adam Savage

sorry, just found some info in BOL stating that i can use text as the input parameter...so i guess i can do it! Does anyone see any problems with this?!

'I reject your reality and substitute my own' - Adam Savage

quote:Originally posted by benwilson

sorry, just found some info in BOL stating that i can use text as the input parameter...so i guess i can do it! Does anyone see any problems with this?!

'I reject your reality and substitute my own' - Adam Savage

If you look at CREATE PROCEDURE in BOL, it doesn't say that you can't use TEXT.

You should however be looking at how you are going to implement this ... at least when you intend to call the procedure within the T-SQL environment, I can see a big problem.

You cannot call this procedure within T-SQL with a local parameter, because you cannot declare a variable as having type TEXT. And because you cannot have a variable of the proper type, there are only two ways to call the procedure with a string longer than 8000 characters:

(1) You supply the string verbatim on the procedure call.

(2) You declare a number of VARCHAR(8000) variables, split the string over the variables, and then concatenate the variables on the procedure call. And you would have to test if that actually works.

... then again you could work around the local variable restriction by adding a dummy TEXT parameter to the procedure definition.

It does beg the question when they will allow the use of TEXT variables - does anyone know if that's in SQL 2005 already?

Frank,

Sure it works up until 8000 characters, but if you try to start working with bigger strings you just run into the 8000 character limitation of string functions.

Also, if you have a procedure parameter declared as @test TEXT, then you cannot use SET @test = 'blabla' or SELECT @test = 'blabla' or you will get the following error: "The assignment operator operation cannot take a text data type as an argument."

I've been looking at the TEXTPTR, READTEXT and UPDATETEXT functions, but they can only be used against table columns and so a possible work-around would need to involve creating a temporary table with a text column. This could quickly become very ugly.

Okay, the long-and-short of it is that we wait for SQL 2005 for this kind of processing ...

Well, how else would you ever manage to pass on a long string to a procedure from your client app? The only thing that you cannot do is to use it as an OUTPUT parameter for an edited string, since you cannot use SET or SELECT on a TEXT parameter.

-- This is the procedure that processes the TEXT data
CREATE PROCEDURE dbo.TestForDummy (@text TEXT OUTPUT, @varchardummy VARCHAR(8000) = '')
AS

-- Create an 800 character string to play with ...
SET @varchardummy = REPLICATE('a', 8000)

-- Create a table with a TEXT column so we can manipulate the string -
-- this could of course also refer to an existing column in an existing table ...
CREATE TABLE #T (col TEXT)
INSERT INTO #T VALUES (@varchardummy)

-- Use TEXTPTR to get a handle on the string for the UPDATETEXT function
DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(col)
FROM #T

-- Use UPDATETEXT to insert the 8000 character string at the start of the data in the column
UPDATETEXT #T.col @ptrval 0 0 @varchardummy

-- Show the length of the data in the column after the update
SELECT DATALENGTH(col) FROM #T

-- The next line would fail because you cannot do SET or SELECT a TEXT string
--SELECT @text = #T.col FROM #T

DROP TABLE #T

GO

-- This procedure calls the previous function with a dummy TEXT parameter.
CREATE PROCEDURE dbo.TestWithDummy (@dummy TEXT)
AS

EXEC dbo.TestForDummy @dummy OUTPUT

-- Just to show that you cannot get a return value ...
SELECT DATALENGTH(@dummy)

GO

EXEC dbo.Testwithdummy NULL

DROP PROCEDURE dbo.testforDummy
DROP PROCEDURE dbo.testwithdummy

I wasn't expecting to get this much interest in my post overnight! <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Ok, since I can't wait for SQL Server 2005, it seems i can have TEXT as an input parameter, so the .Net guys can pass the XML document to the procedure as TEXT...on looking at OPENXML, i see that i will need to use sp_xml_preparedocument which will also (according to BOL) accept TEXT as the input XML document...so with a bit of playing i will hopefully be able to get it all working! (although my previous experiences with XML havent bee too successful!)<br /><br />I still find it interesting/confusing the way the TEXT data type is supported in SQL 2000 in that you can use it some places but not others. I guess Microsoft want to keep us on our toes <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Thanks a lot everyone!<br /><br />'I reject your reality and substitute my own' - Adam Savage

ok, i have had a play with it today, and it works! In case anyone is interested, here is the procedure i came up with that successfully uses a TEXT variable as input (tested with up to 45000 characters) and reads the XML into a table variable [<img src='/community/emoticons/emotion-2.gif' alt='' />]:<br /><br /><pre id="code"><font face="courier" size="2" id="code">CREATE PROCEDURE Run_TrainingGetPrintList @XMLDoc TEXT<br />AS<br /><br />DECLARE @hdoc INT<br /><br />DECLARE @XMLAsTable TABLE<br />(BookId BIGINT,<br />ChapterId BIGINT,<br />ScreenId BIGINT,<br />TopicId BIGINT,<br />EventId BIGINT,<br />PassedOrder [bigint] IDENTITY (1, 1)NOT NULL)<br /><br />EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLDoc<br /><br />--insert the xml into a table variable<br />INSERT INTO @XMLAsTable (BookId, ChapterId, ScreenId, TopicId, EventId)<br />SELECT BookId, ChapterId, ScreenId, TopicId, EventId<br />FROM OPENXML (@hdoc, '/root/item',2)<br />WITH(BookId BIGINT,<br />ChapterId BIGINT,<br />ScreenId BIGINT, <br />TopicId BIGINT,<br />EventId BIGINT)<br /><br />--i then go on to join the table variable to other tables and select the data i need!<br /><br />exec sp_xml_removedocument @hdoc<br /><br />GO<br /></font id="code"></pre id="code"><br /><br />'I reject your reality and substitute my own' - Adam Savage

Here's the scenario:
One system currently sends their transactions/records to us by posting them in a table. Our system then processes these records via DTS job that runs a stored proc that reads the table and update our database.

Now this system that sends us the records are planning to include XML data in the records that they are sending and we have to process it. I'm trying to use the same DTS job that we currently have to process this info but I can't pass the text column (which has the XML data) to sp_xml_preparedocument.

Any idea to make this happen?

sampaloc