Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58

    Unanswered: generating sql string for execution

    I've a need to generate a condition clause (if statement) from 3 variables: a value, an operator & another value.

    To generate this: 'abcd' like 'a%'
    & verify if the condition is satisfied I've done the following:

    CREATE TABLE #Temp(Result varchar(10))
    declare @cond1 as varchar(50), @op as varchar(4), @cond2 as varchar(50),
    @expr as varchar(50),@result as varchar(10)
    set @cond1 = '''abcd'''
    set @op = 'like'
    set @cond2 = '''a%'''
    set @expr = @cond1 + ' ' + @op + ' ' + @cond2
    insert into #temp exec ('select case when ' + @expr + ' then ''true'' else ''false'' end')
    if exists (select result from #temp where result = 'true')
    select 'it is true'
    else
    select 'it is false'
    drop table #temp


    It works, but a bit clumsy. Is there a more elegant way to do this ?
    catkins

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about something mildly perverse like:
    Code:
    DECLARE @cond1 as varchar(50), @op as varchar(4), @cond2 as varchar(50)
    
    SET @cond1 = '''abcd'''
    SET @op = 'like'
    SET @cond2 = '''a%'''
    
    EXECUTE ('SELECT ''it is '' + CASE WHEN (' + @cond1 + ' ' + @op 
    + ' ' + @cond2 + ') THEN ''true'' ELSE ''false'' END')
    The extra parentheses are just digital "seat belts" in case anything goes wrong cooking up your expression.

    -PatP

  3. #3
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58
    Thanks Pat, very creative.

    An extension on that: how do I get the result of the execute into a variable ?

    with a select one can do this:

    declare @temp as varchar(10)
    select @temp = (select 'abc')
    print @temp

    but that doesn't work with an exec

    Thanks
    Colin
    catkins

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Now we need to get "creative" to make that happen!
    Code:
    DECLARE
       @cond1	AS VARCHAR(50)
    ,  @cond2	AS VARCHAR(50)
    ,  @cmd		AS NVARCHAR(200)
    ,  @op		AS VARCHAR(4)
    ,  @result	AS NVARCHAR(50)
    
    SET @cond1 = '''abcd'''
    SET @op = 'like'
    SET @cond2 = '''a%'''
    
    SET @cmd = 'SELECT @i = ''it is '' + CASE WHEN (' + @cond1 + ' ' + @op 
    + ' ' + @cond2 + ') THEN ''true'' ELSE ''false'' END'
    
    EXECUTE sp_executesql @cmd, N'@i NVARCHAR(50) OUTPUT ', @result OUTPUT
    
    SELECT @result  --  Just to show it worked
    -PatP

Posting Permissions

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