16

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!

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
user53885
  • 3,809
  • 11
  • 33
  • 43

1 Answers1

29

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Hello, I updated my question to show that I'm using SQL 2000. – user53885 Dec 05 '10 at 17:36
  • 3
    You'll have to use `varchar(8000)` or `nvarchar(4000)` then. Obviously it might be truncated but not at 1 character. – Martin Smith Dec 05 '10 at 17:37
  • 1
    Most of these text fields contain way more than 8000 - this is definitely of excellent help though. – user53885 Dec 05 '10 at 17:41
  • 1
    Hmm, Not sure if there is any way around this actually. – Martin Smith Dec 05 '10 at 17:44
  • 1
    In other areas within the existing stored procedures looks like TextPtr() and UPDATETEXT are being used so I may have to figure out those. – user53885 Dec 05 '10 at 17:48
  • 1
    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? – Martin Smith Dec 05 '10 at 17:52
  • 1
    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. – user53885 Dec 05 '10 at 17:57
  • 2
    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.... – marc_s Dec 05 '10 at 18:25