Need to convert Text field to Varchar temporarily so that I can pass to a stored procedure

Question

I am using a SQL 2000 database.

I am working with a database in which I cannot change the types on the tables, or the stored procedures. One of the stored procedures I need to call expects a parameter of 'text'. I can get to the text field, but I am unable to figure out who to store that in a variable or any other way to pass it into the stored procedure?

If I try and create a text variable, SQL won't let me - if I convert it to varchar I only get the first character from the text field.

Any tricks to get around this much appreciated! Thank you!

Solution

Declare the variable of type varchar(8000)

declare @v varchar(8000)
SET @v = (SELECT CAST(textcol as varchar(8000)) FROM yourtable WHERE ....)

Obviously it might still be truncated but not at 1 character.

Comments

[user53885]

Hello, I updated my question to show that I'm using SQL 2000.

[Martin Smith]

You'll have to use varchar(8000) or nvarchar(4000) then. Obviously it might be truncated but not at 1 character.

[user53885]

Most of these text fields contain way more than 8000 - this is definitely of excellent help though.

[Martin Smith]

Hmm, Not sure if there is any way around this actually.

[user53885]

In other areas within the existing stored procedures looks like TextPtr() and UPDATETEXT are being used so I may have to figure out those.

[Martin Smith]

Can you change the stored procedure so that it is responsible for retrieving the text data rather than passing it in? What does it do with it anyway? Insert it into another row?

[user53885]

That's a good suggestion, will try that. The procedure just updates the text value with what is passed in, but it normally gets a text value passed in - i think the front end application is doing the conversion.

[marc_s]

If you really want a solution - support for more than 8000 characters and being able to pass to stored proc - you'll have to upgrade to SQL Server 2005 or newer which supports VARCHAR(MAX) which can hold up to 2 GByte of textual data. SQL Server 2000 doesn't offer any useable solution for this, sorry....