text variable in TRIGGER

text variable in TRIGGER

• awwardak

  Thank you,Louis Davidson - SQL Server MVP
  I just want to use the text variable as a container of messages,

  but Iusing varchar(1000) is more expensive to the system, as the text variable is only 16 bits.

  maybe I should just adopt the varchar variable.

  
  
  

• brianmrush

  Actually this is never allowed, even outside of a trigger.

  declare @content text

  Msg 2739, Level 16, State 1, Line 1
  The text, ntext, and image data types are invalid for local variables.
  

  Text has special tools to work with it: READTEXT WRITETEXT, but not in a trigger, but even outside of a trigger they are unpleasant to work with.

  In 2005 you can use varchar(max) in triggers, and it all works like you probably were hoping text would be.

  You can't even refer to a column that is of text type in a trigger unless you use an instead of trigger. Text is generally speaking, a real pain.

  Post what you are trying to do and perhaps one of us can help you figure a way to do it easy enough.

  
  
  

• Fuzzb

  Hi Umachandar,

  I am dynamically creating a sql query and then executing it using exec command. my problem is my dynamic sql exceeds the limit of 8000 chars. so I am having couple of variables and executing like

  exec @var1 + @var2

  To improve performance of this SP I tried to use sp_executesql but it accepts only ntext or nvarchar as input.

  kindly let me know in case of any work arounds

  many thanks in advance.

  Bala

  
  

• Pedro Alves Ferreira

  maybe your first advice is better!

  thank you, Umachandar Jayachandran - MS.

  
  
  

• Eric Bickle - BlueRealm

  The text column in a table uses 16 bytes in-row or as many bytes as specified in the 'text in row' option. The text variable which can only be used as parameter to a SP or UDF for example consumes as much memory as the data. So it is not going to be efficient. So there is restriction on where you can declare and use text variables. You can do one of the following:

  1. Create a temporary table with text column and use that to accumulate the messages

  2. Use varchar(8000) or nvarchar(4000) in SQL Server 2000

  3. Use varchar(max) or nvarchar(max) in SQL Server 2005.

  
  
  
• text variable in TRIGGER