SQL Server Performance Forum – Threads Archive
generate a dynamic query
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 !
You can probably do away with EXEC(@[email protected][email protected]) But dyanmic SQL with 27000 characteres? Sound bit odd to me. Anyways make sure you read http://www.sommarskog.se/dynamic_sql.html Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
What is the need of using 27000 characters?
Can you give us more informations? Madhivanan Failing to plan is Planning to fail
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=’
‘ />] 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=’‘ />]<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 !
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?
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 !
Read this fully http://www.sommarskog.se/dynamic_sql.html
Madhivanan Failing to plan is Planning to fail
]]>