Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2009
    Posts
    51

    Unanswered: need to avoid dynamic SQL

    Hi

    Please help on this.
    I build a sql variable as follows this variable needs to be included in the SQL query witten below it is not working.

    DECLARE @PARAM1 AS NVARCHAR(1)
    DECLARE @PARAM2 AS NVARCHAR(1)
    DECLARE @PARAM3 AS NVARCHAR(1)

    SET @PARAM1 = 'N'
    SET @PARAM2 = 'N'
    SET @PARAM3 = 'N'

    DECLARE @TYPES AS NVARCHAR(50)
    SET @TYPES = ''
    IF @PARAM1 = 'N' SET @TYPES = '''A'''
    IF @PARAM2 = 'N' AND @TYPES <> ''
    SET @TYPES = @TYPES + ',''B'''
    ELSE IF @PARAM2 = 'N'
    SET @TYPES = @TYPES + '''B'''
    IF @PARAM3 = 'N' AND @TYPES <> ''
    SET @TYPES = @TYPES + ',''C'''
    ELSE IF @PARAM3 = 'N'
    SET @TYPES = @TYPES + '''C'''

    SELECT @TYPES

    SELECT * FROM TABLE1 WHERE TYPE IN (SELECT @TYPES)
    /*
    PLEASE NOTE Dynamic SQL works but needs to be avoided
    any other approach than the above would be ok as far as no Dynamic SQL
    */
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  2. #2
    Join Date
    Feb 2009
    Posts
    51

    addressed the issue

    DECLARE @PARAM1 AS NVARCHAR(1)
    DECLARE @PARAM2 AS NVARCHAR(1)
    DECLARE @PARAM3 AS NVARCHAR(1)

    SET @PARAM1 = 'N'
    SET @PARAM2 = 'N'
    SET @PARAM3 = 'N'

    DECLARE @TYPE_A AS NVARCHAR(1)
    DECLARE @TYPE_B AS NVARCHAR(1)
    DECLARE @TYPE_C AS NVARCHAR(1)

    IF @PARAM1 = 'N' SET @TYPE_A = 'A'
    IF @PARAM2 = 'N' SET @TYPE_B = 'B'
    IF @PARAM3 = 'N' SET @TYPE_C = 'C'

    SELECT * FROM TABLE1 WHERE (TYPE IN (@TYPE_A) OR TYPE IN (@TYPE_B) OR TYPE IN (@TYPE_C) AND TYPE IS NOT NULL)

    Any issues with the above approach?
    Thx
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    try by using patindex or like operator?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbfHelp
    SELECT * FROM TABLE1 WHERE (TYPE IN (@TYPE_A) OR TYPE IN (@TYPE_B) OR TYPE IN (@TYPE_C) AND TYPE IS NOT NULL)
    that can be simplified as follows:

    SELECT * FROM TABLE1 WHERE TYPE IN (@TYPE_A,@TYPE_B,@TYPE_C)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    create table #types (_type char(1) null)
    insert #types select t from (
    select t = case @param1 when 'N' then 'A' end union
    select case @param2 when 'N' then 'B' end union
    select case @param3 when 'N' then 'C' end) x where t is not null
    select * from table1 t1 where exists (select * from #types t2 where t1.type = t2._type)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2009
    Posts
    51
    Thanks for all the replies.
    Sorry I forgot to mention no unions allowed as a standard the application
    got SQL procedures without any union statements.
    Sounds strange but cant complain.
    r937 good stuff !!!!
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would not work for anyone who played by such silly rules.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2009
    Posts
    51

    No worries.

    Quote Originally Posted by Thrasymachus
    I would not work for anyone who played by such silly rules.
    G'day Thrasy Buddy!!
    Take it easy mate!!!
    It's individuals choice when it comes to that.
    I am very happy to work for this company for so many good reasons.
    Honestly and truly... all righty
    No worries.
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If the rule is NO to UNIONs, but YES to UNION ALLs, you can simply use :
    Code:
    create table #types (_type char(1) null)
    insert #types select t from (
    select t = case @param1 when 'N' then 'A' end union ALL
    select case @param2 when 'N' then 'B' end union ALL
    select case @param3 when 'N' then 'C' end) x where t is not null
    select * from table1 t1 where exists (select * from #types t2 where t1.type = t2._type)
    If the silly rule is NO to UNIONs and NO to UNION ALLs, you can rewrite it like:
    Code:
    create table #types (_type char(1) null)
    if @param1 = 'N' then 
       INSERT INTO #types(_type) VALUES('A')
    end if
    if @param2 = 'N' then 
       INSERT INTO #types(_type) VALUES('B')
    end if
    if @param3 = 'N' then 
       INSERT INTO #types(_type) VALUES('C')
    end if
    
    select * 
    from table1 t1 
    where exists (select 1 
                  from #types t2 
                  where t1.type = t2._type
                 )
    Ask why UNIONs (and UNION ALLs ?) are taboo. Perhaps it is because in dBase II version 1.0 it made queries slow. See if the reason still holds for the SQL Server version you are using. If not, but UNIONs (ALLs) stay taboo, start humming "The times they are a changing" on each and every occasion you meet this guy.

    I try to avoid UNIONs too, replacing them by UNION ALL whenever applicable. But sometimes you need a DISTINCT. As a rule of thumb: if I need to put a DISTINCT after my SELECT, I reread my SQL script again until I have found the error in the code that made it necessary or until I have found why I really really need that DISTINCT.

    Good news you like your company, but don't let it turn your curiosity and intelligence off when you hear such rules.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Feb 2009
    Posts
    51
    All righty all the replies well regarded.
    Thanks again for all the great stuff.
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  11. #11
    Join Date
    Feb 2009
    Posts
    51
    Quote Originally Posted by rdjabarov View Post
    create table #types (_type char(1) null)
    insert #types select t from (
    select t = case @param1 when 'N' then 'A' end union
    select case @param2 when 'N' then 'B' end union
    select case @param3 when 'N' then 'C' end) x where t is not null
    select * from table1 t1 where exists (select * from #types t2 where t1.type = t2._type)
    Hi rdjabarov

    beautiful thanks heaps for the reply.
    it rocks!!!!
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  12. #12
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by dbfHelp View Post
    Thanks for all the replies.
    Sorry I forgot to mention no unions allowed as a standard the application
    got SQL procedures without any union statements.
    Sounds strange but cant complain.
    r937 good stuff !!!!
    Maybe that refers to you and your fellow IT workers joining/forming a labor union

  13. #13
    Join Date
    Mar 2010
    Posts
    10
    You can use Dynamic SQL.

    Now hold on before you all start berating me ;-)

    It's not recommended when user inputted values are being run in your query, but you can protected them by executing your dyanamic SQL as a stored procedure with parameters

    The command is sp_executesql

    sp_executesql [@SQLStatement],[@ParameterDefinitionList],
    [@ParameterValueList]


    You can find more info here:
    Building Dynamic SQL In a Stored Procedure - CodeProject

    BUT, its not recommended, if you can avoid using dynamic SQL with user inputted data do so. Like everything though, there is occassionally a requirement, so protect your data from SQL injection by using the sp_executesql

    At least, that is what I know... I may be wrong... if so I reckon a kind soul on here will correct me.

Posting Permissions

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