local variable - any limitations of max characters?

• vyu123

  SSC Veteran

  Points: 211

  More actions

  June 28, 2004 at 12:23 pm

  #108546

  Hi,

  I am trying the following:

  Declare @test-2 varchar(8000)

  Set @test-2='SELECT VIOXX_LastName + '' + VIOXX_FirstName + ''  + CONVERT(varchar(50), VIOXX_Number) AS PlaintiffsName, VIOXX_Number

  FROM tblPlaintiff WHERE     VIOXX_Number NOT IN(SELECT     VIOXX_Number FROM          tblCase_Plaintiff)  OR

  VIOXX_Number  IN (SELECT     tblCase_Plaintiff.VIOXX_Number FROM tblCase INNER JOIN tblCase_Plaintiff ON tblCase.Case_Number = tblCase_Plaintiff.Case_Number

        WHERE      (tblCase.Status = ''InActive'')) ORDER BY VIOXX_Number, VIOXX_LastName'

  When I exec the following:

  Select  @test-2

  I got the following result:

  SELECT VIOXX_LastName + ' + VIOXX_FirstName + '  + CONVERT(varchar(50), VIOXX_Number) AS PlaintiffsName, VIOXX_Number

  FROM tblPlaintiff WHERE     VIOXX_Number NOT IN(SELECT     VIOXX_Number FROM          tblCase_Plaintiff)  OR

  VIOXX_Number  IN (SELECT

  Any ideas? Any limitations on the number of characters for a local variable in TSQL?

   

  Thanks in advance.

• David.Poole

  SSC Guru

  Points: 76105

  More actions

  June 28, 2004 at 12:52 pm

  #512253

  That 8000 characters includes white space and some white space characters can break your SQL.

  The only problems I have had are when unicode text (which takes twice as much space) gets truncated.

   

  LinkedIn Profile

• vyu123

  SSC Veteran

  Points: 211

  More actions

  June 28, 2004 at 1:01 pm

  #512255

  The problem is not with the white space. I did a 'Select len(@Test)' and I got 434, which is not even 10 percent of the 8000 characters.

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  June 28, 2004 at 2:37 pm

  #512284

  Query Analyzer --> Tools --> Options --> Results --> Maximum Characters per Column.

   

  It's probably correct, it's just truncating the 1 colimn result at 256 Characters !!!!!!

  
  KlK

• rcavill

  Default port

  Points: 1482

  More actions

  June 29, 2004 at 3:40 am

  #512369

  use

  print @test-2

  rather than

  select @test-2

  so that you do not have to concern yourself with the maximum column limit set within the Query Analyzer Results option.