Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: 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 anything
    Last edited by reiqwan; 10-22-04 at 06:11.

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    thats 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)'''

  3. #3
    Join Date
    Aug 2004
    Posts
    9

    Query a variable

    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

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by reiqwan
    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

  5. #5
    Join Date
    Aug 2004
    Posts
    9

    Problems with query

    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

  6. #6
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    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

  7. #7
    Join Date
    Aug 2004
    Posts
    9

    past it into a new query window

    Hi Desmond;

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

    Thanks

  8. #8
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    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

  9. #9
    Join Date
    Aug 2004
    Posts
    9

    When I run it

    Where should I look for the information that it compiles ?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •