Enclosing multiple statements in a variable

Hi all;

How do I enclose multiple filters in a variable, for instance how would I put the following filter into a variable and also is it actually possible or do I have to do something else before performing this type of operation:

tel1 like '072%' or tel1 like '+27 72%' or tel1 like '072-%'
or tel1 like '+2772%' or tel1 like '72%'
and len(tel1) > 7
or tel2 like '072%' or tel2 like '+27 72%' or tel2 like '072-%'
or tel2 like '+2772%' or tel2 like '72%'
and len(tel2) > 7
or tel3 like '072%' or tel3 like '+27 72%' or tel3 like '072-%'
or tel3 like '+2772%' or tel3 like '72%'
and len(tel3) > 7
or tel4 like '072%' or tel4 like '+27 72%' or tel4 like '072-%'
or tel4 like '+2772%' or tel4 like '72%'
and len(tel4) > 7
or tel5 like '072%' or tel5 like '+27 72%' or tel5 like '072-%'
or tel5 like '+2772%' or tel5 like '72%'
and len(tel5) > 7
or tel_other like '072%' or tel_other like '+27 72%' or tel_other like '072-%'
or tel_other like '+2772%' or tel_other like '72%'
and len(tel_other) > 7

the problem is that it's got a couple of apostrophes which when declaring variables pulls it out of that mode, I have looked on the internet but can't seem to find anythingthats no problem - just use an extra quote for each quote

set @.statement = N'master.dbo.xp_smtp_sendmail @.subject= N''Purchase Failure (Dont Reply To This Mail)'''|||Hi all;
thanks for the assistance how would I use the variable in a where section in other words it would follow as this

declare @.var varchar(10)
set @.var = 'multiple line like and or filters'
select * from table1
where @.var

thanks all, reaally appreciate the help

cheers|||Hi all;
thanks for the assistance how would I use the variable in a where section in other words it would follow as this

declare @.var varchar(10)
set @.var = 'multiple line like and or filters'
select * from table1
where @.var

thanks all, reaally appreciate the help

cheers

for this u need to use dynamic sql something like:

declare @.var varchar(10),@.Sql nvarchar(1000)
set @.var = 'userid>1000'
set @.sql='select * from users
where '+@.var
exec sp_executesql @.sql|||Hi all;

I am trying the following query:

declare @.072 varchar(5000),@.073 varchar(5000),@.Sql nvarchar(1000)
set @.072 = 'tel1 like ''072%'' or tel1 like ''+27 72%'' or tel1 like ''072-%''
or tel1 like ''+2772%'' or tel1 like ''72%''
and len(tel1) > 7
or tel2 like ''072%'' or tel2 like ''+27 72%'' or tel2 like ''072-%''
or tel2 like ''+2772%'' or tel2 like ''72%''
and len(tel2) > 7
or tel3 like ''072%'' or tel3 like ''+27 72%'' or tel3 like ''072-%''
or tel3 like ''+2772%'' or tel3 like ''72%''
and len(tel3) > 7
or tel4 like ''072%'' or tel4 like ''+27 72%'' or tel4 like ''072-%''
or tel4 like ''+2772%'' or tel4 like ''72%''
and len(tel4) > 7
or tel5 like ''072%'' or tel5 like ''+27 72%'' or tel5 like ''072-%''
or tel5 like ''+2772%'' or tel5 like ''72%''
and len(tel5) > 7
or tel_other like ''072%'' or tel_other like ''+27 72%'' or tel_other like ''072-%''
or tel_other like ''+2772%'' or tel_other like ''72%''
and len(tel_other) > 7'

set @.073 = 'or tel1 like ''073%'' or tel1 like ''+27 73%'' or tel1 like ''073-%''
or tel1 like ''+2773%'' or tel1 like ''73%''
and len(tel1) > 7
or tel2 like ''073%'' or tel2 like ''+27 73%'' or tel2 like ''073-%''
or tel2 like ''+2773%'' or tel2 like ''73%''
and len(tel2) > 7
or tel3 like ''073%'' or tel3 like ''+27 73%'' or tel3 like ''073-%''
or tel3 like ''+2773%'' or tel3 like ''73%''
and len(tel3) > 7
or tel4 like ''073%'' or tel4 like ''+27 73%'' or tel4 like ''073-%''
or tel4 like ''+2773%'' or tel4 like ''73%''
and len(tel4) > 7
or tel5 like ''073%'' or tel5 like ''+27 73%'' or tel5 like ''073-%''
or tel5 like ''+2773%'' or tel5 like ''73%''
and len(tel5) > 7
or tel_other like ''073%'' or tel_other like ''+27 73%'' or tel_other like ''073-%''
or tel_other like ''+2773%'' or tel_other like ''73%''
and len(tel_other) > 7'

set @.sql='select * from fl1_pcode
where '+@.072 +@.073
exec sp_executesql @.sql

but then i get the following error:

Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near 'tel'.

Firstly is this the right method of doing it and how does this error occur, I have tried only using the 072 variable and it pulls out a result fine, but the minute I add the second variable or insert the info from the second variable into the first (make the first variable longer) I get that error.

THanks 4 all the help so far ;)|||hi
check out you concatenation and unicode - put a space between two vars
and specify unicode..(N)

set @.sql= N'select * from fl1_pcode
where '+@.072 +' '+@.073

exec sp_executesql @.sql

if it still gives trouble syntax wise, instead of executing @.sql, select it,
and past it into a new query window - then youll be able to fine the syntax error location and make amends,
goodluck,
Des|||Hi Desmond;

Sorry about my ignorance but how would i pass it into a new query window ?

Thanks|||spelling was meant to be "paste" on my side -
in query analyzer, just open another window, and paste the results from
your select @.sql query, then see if it runs - amend if necessary
des|||Where should I look for the information that it compiles ?

Thanks