Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    8

    Unanswered: Building SQL using variables

    Hello all!

    I am sure there is a technical name for this, but I am trying to build a sql statement using variables... where the variables would be entire clauses within the statement, not just values. This will ultimately be used in a stored procedure.

    ie. Focus on the @AndClause variable

    -----------------------------------
    declare @AndClause varchar(128)
    select @AndClause = ' AND lastname like ''jharris%'''

    SELECT *
    FROM my_customer_table
    WHERE 1=1
    @AndClause
    -----------------------------------

    I have seen this done before but can not find it in an of my references. Thank you for your help

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Code:
    declare @AndClause varchar(128) 
    set @AndClause = ' AND lastname like ''jharris%'''
    declare @vSQL varchar(200)
    set @vSQL =  'SELECT * 
    FROM my_customer_table
    WHERE 1=1' + @AndClause
    
    exec(@vSQL)
    Johan

  3. #3
    Join Date
    Aug 2003
    Posts
    8
    I missed the plus sign... Thanks alot Jora! Do you know if there is technical name for this?

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    euh ... building dynamic sql statements? Don't think there is one term for it. Also, you can use the stored procedure sp_executesql for executing dynamic queries. See Books Online for more info on the differences between the two methods.
    Johan

Posting Permissions

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