Read/Write Text field from Stored Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Read/Write Text field from Stored Proc

Author

Topic

coderdude
Starting Member

24 Posts

Posted - 2004-04-02 : 16:23:59

I usually use a stored proc to read/write data from/to a table. I've recently started working with Text fields (not varchar... greater than 8000 characters) and have found that you can't use a Text parameter for a stored procedure.

What is the best way to read/write Text fields? Am I back to writing Update statments in my code?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-02 : 20:09:27

Do you really need the Text fields? They are incredibly inefficient in SQL Server. If you can avoid them, you should go back to useing the VARCHAR.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-02 : 22:06:51

That didn't answer the question Derrick

You can use text parameters in stored procs, you just can't declare local text variables.

You can do :

Create Procedure Foo
@mytext text

AS

UPDATE Table Set myfield = @mytext

Damian

coderdude
Starting Member

24 Posts

Posted - 2004-04-03 : 11:26:25

The way you showed the update works, however, how do I read the data back out using a parameter?

When I try the following:

CREATE PROCEDURE Test

@TextField text OUTPUT

AS

SELECT @TextField = TextField FROM TestTable WHERE TestTableID = 1

I get this error...

Server: Msg 409, Level 16, State 1, Procedure Test, Line 7
The assignment operator operation cannot take a text data type as an argument.

What now?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-03 : 11:29:05

You don't always need an OUTPUT parameter to return data to the application. You can return the results of a SELECT statement into a recordset in the application. Simply:


CREATE PROCEDURE Test
AS
BEGIN

  SELECT TextField FROM TestTable WHERE MyID = 1

END

coderdude
Starting Member

24 Posts

Posted - 2004-04-03 : 13:03:00

This is true, but it requires the overhead of a recordset and manually reading the data out of each field (in my ASP code). Not that this is too difficult of a task, however, I was just wondering if there was a better way...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-03 : 13:34:27

I think that's the only way -- you can't assign text datatypes to variables/parameters in either T-SQL or ADO as far as I know ... the recordset is the way to go.

Write a generic, re-usable function if it seems like too much code to write each time.

- Jeff

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-03 : 13:40:47

You can't assign to variables. You will also run into difficulties when updating data. For more information about how text fields work in SQL, don't just look at text datatype. Look at WRITETEXT, READTEXT, and UPDATETEXT.

You also need to look at the differences in how text fields are handled depending on your database compatability level.

I wasn't trying to avoid answering the questions earlier, but there has to be a really good reason for me to allow text fields in my databases. It's too easy to just write a generic function in SQL that will parse the text into varchar fields using a temp table and text pointers.

If your not going to be updating or reading the text fields, don't worry about it. Just store it. Otherwise, you might want to at least consider the possibility.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

coderdude
Starting Member

24 Posts

Posted - 2004-04-03 : 14:54:18

Thanks for your replies guys.

Derrick, the reason I need to use text fields (as opposed to varchar) is because our application needs to store text in RTF format for each row. This text will easily exceed 8000 characters in most cases. I don't understand your suggestion about writing "a generic function in SQL that will parse the text into varchar fields using a temp table and text pointers." Can you elaborate on this?

Thanks!

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-18 : 14:40:31

I noticed this post has not been answered in quite a while, and I am having similar problems. Yes, I too hate text fields, but like you said, there has to be a really good reason for you to allow text fields, as there is for me, and for coderdude as well. Well so now we have a really good reason, and he as well as I am here to get some answers, if it can be done or not and how if it can. So please let us know what you mean about "a generic function in SQL that will parse the text into varchar fields using a temp table and text pointers."

Thanks.

- RoLY roLLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources