Dynamic Evaluation

[PaulBarbeau]

I want to build a string that i then want to use in a select statement (look at example below) and i can not workout how to do this. Can anyone help me or tell me it is imposible in t-sql


declare @sqlline varchar(100)
set @sqlline = 'op_id = 16'
select * from thetable where @sqlline

 

[Jamfool]

declare @table_name as varchar(50)
declare @sqlline varchar(100)
declare @command varchar(255)

select @table_name = 'table_name'
select @sqlline = 'where op_id = 16'
--select @sqlline = 'where description like ''%sometext%'''


set @Command = 'select * from '+@table_name+' '+@sqlline

PRINT (@Command)
EXECUTE (@Command)

 

[PaulBarbeau]

Thanks alot... works like charm

 

[PaulBarbeau]

What happend is my @command is larger then 8000 charaters

 

[swampBoogie]

You can execute a string > 8000

e.g.

declare @s,@w,@o varchar(8000)
set @s = 'select ...'
set @w = ' where ....'
set @o = 'order by ...'
exec (@s + @w + @o )

 

[Jamfool]

Yeah split the query up more in to its compsite parts. You could also use aliases to reduce the number of chars

select column_name
from table_name as a
where a.column_name = 1

 

[JamesLean]

swampBoogie's theory is correct. Just you have to declare variables seperately:

DECLARE @s varchar(8000),
  @w varchar(8000),
  @o varchar(8000)

--James