SQL Server Performance

generate a dynamic query

Discussion in 'SQL Server 2005 General Developer Questions' started by bogdan, Sep 29, 2006.

  1. bogdan New Member

    hello,


    i have a query which selects some persons from a database. now i found out that i have to generate a dynamic where clause.
    the problem is that i need to generate a dynamic query which has more than 27.000 characters. I did such queries before but with no more than 2000 characters. can you please give me a hint of how this is best done?


    thanks,
    bogdan


    Able was I ere I saw Elba !
  2. Roji. P. Thomas New Member

  3. Madhivanan Moderator

    What is the need of using 27000 characters?
    Can you give us more informations?

    Madhivanan

    Failing to plan is Planning to fail
  4. bogdan New Member

    indeed it is odd roji but if it worked for smaller number of characters, it works for bigger one also [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] i did something like this<br /><br />declare @sql varchar(max) --remember that varchar has 8000 characters<br />declare @where varchar(max) --this is what i needed to be dynamic the simple query was OK but returning all the values from a table<br />set @sql = '' --just to be sure it is empty<br /><br />set @sql = @sql + ' /*first 8000 characters, but i think it would be wiser to put fewer*/ '<br />.<br />.<br />.<br />set @where = '/*something*/'<br />set @sql = @sql + @where <br /><br />exec (@sql)<br /><br /><br />it's nothing fancy or what you probably didn't know ... i thought that having so many characters there could be some optimizations to be done ... i also used some parameters passed to the stored procedure in the stored procedure ... alter all there was to it was having fun with strings [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><br />ps: i counted the characters with MS Word.<br /><br /><br />thanks for your replies! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Able was I ere I saw Elba !
  5. Adriaan New Member

    If you're compiling a WHERE clause based on multiple values, why not put those values into a (temp) table, and in the WHERE clause add a subquery to that table?
  6. bogdan New Member

    if i think at what you are saying you might be right but i cant see the solution ...

    i have a stored procedure that returns the where clause which looks something like "(age between 5 and 10) or age = 15 or age = 25 or (age between 30 and 45)"

    select *
    from my_table
    where @ouput_variable [?]

    i do not know if something like this can work ... it is also true that i never tried it ...

    Able was I ere I saw Elba !
  7. Madhivanan Moderator

Share This Page