problem with Dynamic Query

Question

problem with Dynamic Query

Ankit Bhurat

SSC Journeyman

Points: 89
More actions
July 24, 2007 at 7:06 am

#196994

Hello every1
In my SP i am making a dynamic query to make WHERE calue and getting some ID from DB in IN clause.
Now since we can have a max of 8000 size for varchar,
My quesition is that after some time, when the data becomes large this is bound to over flow thus causing errors.
Is there any permanent solution for this?
Any hint to the solution will also do.
Thanks in advance
Ankit

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Isn't there a varchar(max) in 2005?? I am really asking because I don't know if there's another limit for the dynamic sql length.

Anyhow a couple solutions already exists for this one :

From the code side create a temp table, insert all the ids in the temp table, then in the procedure, do an inner join on the temp table to apply the filter.

Option b which I like a lot less is to put all those ids into XML document and parse the XML in the proc to get the list of ids. I like this one less because there's more data to send on the network that way, and more data to process...

Ankit Bhurat SSC Journeyman Points: 89 More actions · Answer 2

Hello Ninja..

Thanks for ur kind reply....

Yes there is MAX .. in 2005 and I just used that to its now working .

1. My query is dynamic because i dont know the where caluse that will be coming. It's a kind of advanced search. So i am making it dynamic. As of your suggestion of temp table sound good.

Thanks for ur time

Ankit