Multiple variable concatenation

• dgodden

  SSC Veteran

  Points: 213

  More actions

  October 1, 2003 at 4:20 am

  #84454

  We all know that we can't have variables of text, ntext etc in stored procedures. So the greatest number of characters we can store is 8000 in non-unicode...I know that I can concatenate multiple local variables, but this is not perfect because I may not know the max length of the string and therefore how many variables I may need. Is there anyway to dynamically declare variables and then concatenate them for use anywhere within the procedure? I know I can insert into a text or ntext field of temp table or table variable but I still wouldn't be able to use the field value in the same manner as a variable (I want to use EXEC(@textvariable)). Anyone got any suggestions?

• Richard Binnington

  SSChasing Mays

  Points: 656

  More actions

  October 1, 2003 at 12:19 pm

  #476622

  Since it is a SQL statement, how about breaking it up into several variables: select, from, where, groupby etc.?

  That would give you 8k a piece. Would that be enough?

  Edited by - rbinnington on 10/01/2003 12:20:28 PM

• philcart

  SSC-Forever

  Points: 47795

  More actions

  October 1, 2003 at 6:08 pm

  #476675

  If you declare multiple variables you can use then in the EXEC statment like this,

  EXEC (@sql1 + @sql2 + etc...)

  Just make sure that the concatenated string makes a valid sql statement. I always forget to make sure there is a space between each string of text

  Hope this helps

  Phill Carter

  --------------------

  Colt 45 - the original point and click interface

  --------------------
  Colt 45 - the original point and click interface

• bdohmen

  SSCrazy

  Points: 2996

  More actions

  October 2, 2003 at 10:17 am

  #476832

  Would this work for you?

  It builds and executes the following statement:

  select col1,col2,col3.....,col487,col488,col499..... from table1 t1, table2 t2.... where t1.col1=t2.col1 and t1.col2=t2.col2.... and t1.col487=t2.col488 and t1.col488=t2.col488.....

  create table #temp (a int identity(1,1), string varchar(8000))

  /*

  You will have to figure out how to populate the temp table

  */

  insert into #temp

  select 'select col1,col2,col3.....'

  insert into #temp

  select ',col487,col488,col499.....'

  insert into #temp

  select ' from table1 t1, table2 t2....'

  insert into #temp

  select ' where t1.col1=t2.col1 and t1.col2=t2.col2....'

  insert into #temp

  select ' and t1.col487=t2.col488 and t1.col488=t2.col488.....'

  declare @sql varchar(8000), @cnt int

  set @sql='declare '

  --declare the needed variables

  select @sql=@sql+'@sql'+convert(varchar,a)+' varchar(8000),' from #temp order by a

  set @sql=left(@sql,len(@sql)-1)

  --set the value to each variable

  select @sql=@sql+' select @sql'+convert(varchar,a)+'=string from #temp where a='+convert(varchar,a) from #temp order by a

  --now to build the execute statement

  select @sql=@sql+' exec('

  select @sql=@sql+'@sql'+convert(varchar,a)+'+' from #temp order by a

  --get rid of the last "+" and add the close paren

  select @sql=left(@sql,len(@sql)-1)+')'

  exec (@sql)

  drop table #temp