Posted by Erland Sommarskog on 10/01/93 11:18

Mahesh (gaware@gmail.com) writes:
> Yeah, Stu had mentioned it earlier about the performance of dynamic
> SQL. Can anyone of you explain why Dynamic SQL is poor in performance?

When the list is long, it takes the optimizer very long time to build
the execution plan.

> Erland, actually my query consists for two such IN statements with AND
> condition.
>
> i.e WHERE StateTypeID in ('1,2,3,4.....') AND UserTypeID IN
> ('2,3,4.....')
>
>
> StateTypeID and UserTypeID are of INT type. Could please let me know
> the best way to perform the above query in SP.
> Do I need to go for nested WHILE loop? what u feel is best way of doing
> it

Did you look at the link I posted? That should take you a long way.
http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-integers-


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: Max Filegroup Size set to 20 how do I change it?
• Prev in forum: Re: Two records of same Partner together
• Thread view: Re: How to Use Varchar with Int column

[Reply to this message]