MS SQL stored procedures inside another stored procedure

Hi,

Do you know how to write stored procedures inside another stored procedure in MS SQL.

Create procedure spMyProc inputData varchar(50)

AS

-- some logical

procedure spMyProc inputInsideData varchar(10)

AS

-- some logical

-- go

---

What exactly are tou trying to do?

|||

Like Function, you can have one function inside another another function.

Function1 ()

{

Function2()

}

How about store procedure ?

spProc1

{

spProc2

}

how to write it with correct syntax?

|||

What do you mean by "having" a proc inside another proc? Execute a proc or create a proc? You can definetely call another proc but creating a proc from a proc is a very very bad idea.

|||

But, it is easy to migration. for example, currently, I have a base procedure and associated with several (5) satellite procedures, every time I have tell DBA, all of 6 proc.. I want to put into one.

|||

Hi,

First, just as ndinakar said, I also think it's not good to create a proc from a proc. You may create your procedure separately call your satellite procedures in your base procedure.

Second, if you really want to migratie all the procedures, then just put all the logic process code into one procedure, but it's also not a good way compared with the first method.

Thanks.

ms sql stored procedures and functions

Hi all

Trying to figure out what you use ms sql functions for. I understand stored procedures and how to create them. the question is what is the real purpose of a ms sql function considering everything i have read so far makes me think that there is no valid use for them. You can do almost everything that a function does but in a stored procedure.

If somebody can give me a good examplle of a sql function i would appreciate it very much.

thanks

I use functions as a way to seperate cetain logic, usually so I can use it in several stored procedures. To give you an example, I often pass a comma delimited list of values to stored procedures as a varchar sql parameter. I created a function that splits that string on the delimiter (comma) and puts the values into a table, which I then use as part of the criteria for my select statement. So it all works out like this:

SELECT * FROM myTable

WHERE ApplicationID IN (Select value from dbo.f_split(@.mylist))

|||

wstevens@.vodamail.co.za:

You can do almost everything that a function does but in a stored procedure.

True. But you have to call a stored procedure with parameters and an exec statement, and then you have to declare those parameters as output if you want a return value that is anything other than an int.

A function can return any datatype. It is also easier to call a simple function during a SELECT statement than it would be a stored procedure, i.e. ISNULL, CAST, or CONVERT. If you have specific functions that you need to do in your database, and they are required in numerous places (more than 1), then a function may be a way to go.

An easy one could be GetUserName, to which you pass a UserID. The function could then concatenate the first name and last name together with a space in between. If you want to get even more fancy, it could add a prefix (Mr.) and a suffix (, Jr.). The function returns the complete user name without you having to implement the logic for concatenating and adding spaces, which allows it to easily be used in the select query for a user.

Another nice thing about a function is that it helps encapsulate and centralize the logic, just as the previous example showed us. What if you decide later you don't want the prefix and suffix? You have just one place to make the change. That's better than trying to remember every place you used ([Prefix] + ' ' + [FirstName] + ' ' + [LastName] + ' ' + [Suffix]) AS [UserName].

|||

Hi

What u are saying with using the example of GetUserName is the function is called on its own and not via a Stored Procedure. So it retrieves a record and formats the output something like below;

strUsername = "Mr" & " " & [Name] & " " & [Surname]

output = Mr Name Surname

This is a basic formating of a 2 returned values. I understand that it makes it easier to change a suffix and a prefix from one place, but im sure there must be a more powerful use for functions. Can you give me an example of something which has to use a function as there is no other way. For example a stored procedure that requires a function to complete its execution.

|||

Hi

So what u are saying is you are passing an array of values from the function to the stored procedure. Can u possibly show me a full example of your function and the stored procedure. It would help if i can see the code.

thx

|||

There are a number of different functions, each of which would have a different use.

Scalar functions:

SELECT dbo.BuildName(First,Middle,Last,Prefix,Suffix) As FullName

FROM MyTableWithNamesInIt

WHERE Last LIKE 'A%'

Of course that function would take all the parameters building a nicely formated name field. Something like Prefix+" "+First+" "+CASE WHEN ISNULL(Middle,'')<>'' THEN LEFT(MIddle,1)+'.' ELSE '' END+' '+Last+' '+Suffix would be pretty close unless prefix was null (or empty) in which case you would have a space at the beginning. Or middle didn't exist, then you'd have two spaces in the middle of the name, etc.etc. How would you accomplish the same thing with a stored procedure if you had many places where you had to build a name from multiple parts in many different queries?

If you realize that functions like LEFT(), RIGHT(), TRIM(), SUBSTRING(), ISNULL(), etc are scalar values functions, then you quickly understand how useful they can be.

Another example is table-valued functions where you can something like this:

SELECT *

FROM Orders o

JOIN dbo.Split(@.ListOfOrders) t1 ON o.ID=t1.ID

Of course @.ListOfOrders is a comma-delimited string of orders id's, like '1,2,3'. Yes, you can make a stored proc to retrieve a list of order id's as a comma-delimited list. But what if you need to do that for orders, receipts, customers, stores, and employees?

Table-valued functions can be very similiar to views with parameters as well. So you can do something like:

SELECT *

FROM dbo.CompletedOrdersThatWereNotReturnedBy('1/1/2007')

Of course that's not a great example, but they do come in very handy when you want to encapsulate some complex logic for reuse by others.

|||

A good example of a useful table-valued function could be... GenerateNumbers(Seed,Limit,Increment). So then you could grab every 3rd something like:

SELECT *

FROM Somethings s

JOIN dbo.GenerateNumbers(1,1000,3) gn on s.ID=gn.ID

In that example, GenerateNumbers would return a table of numbers (1,4,7,10,etc).

Another use would be for insuring that there always exists atleast one row for each month by generating rows 1-12 and using that as part of an outer join.

|||

Hi

Would i be correct if i tried the following.

Create a function that can handle paging for a datalist. Call the function from a custom class. using a objectdatasources. If there is no page value posted back then it assumes that this is the frist call to the function. I then have a next and previous button. if I select next it calls the function and passed the value next to the function which intern checks the last page number and moves to the next page returning those values. If the value passed is of a null nature it then automatically returns to the first page .

MS SQL Srv 2000 and extended stored procedures

hi,
I have created my own dll file that I would like to use in sql 2000 as an
extended stored procedure. I have checked the dll is written correctly (I ca
n
use it in other application). The dll file is created in C# (Visual Studio
2005).
Unfortunatelly I cannot use it in sql as the extended procedure. I am
getting error: Cannot find the function <<f_name>> in the library
<<path\library.dll>>. Reason: 127(error not found).Extended procedures need to be written in C or C++ (or Delphi, so I've heard
). A language that can
create classic DLL files, not any of the modern fancy COM dlls. Also, SQL Se
rver 2000 doesn't
support hosting any type of CLR code, whether through xp or sp_OACreate, or
whether with or without
any wrappers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
> hi,
> I have created my own dll file that I would like to use in sql 2000 as an
> extended stored procedure. I have checked the dll is written correctly (I
can
> use it in other application). The dll file is created in C# (Visual Studio
> 2005).
> Unfortunatelly I cannot use it in sql as the extended procedure. I am
> getting error: Cannot find the function <<f_name>> in the library
> <<path\library.dll>>. Reason: 127(error not found).|||Hi
This may be of interest:
"Using extended stored procedures or SP_OA stored procedures to load CLR in
SQL Server is not supported"
http://support.microsoft.com/default.aspx?scid=322884
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBcPMTWwGHA.3264@.TK2MSFTNGP03.phx.gbl...
> Extended procedures need to be written in C or C++ (or Delphi, so I've
> heard). A language that can create classic DLL files, not any of the
> modern fancy COM dlls. Also, SQL Server 2000 doesn't support hosting any
> type of CLR code, whether through xp or sp_OACreate, or whether with or
> without any wrappers.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
>|||so is it possible to create an extended stored procedure in Visual Studio
2005 (C++)?
"Michael Epprecht [MSFT]" wrote:

> Hi
> This may be of interest:
> "Using extended stored procedures or SP_OA stored procedures to load CLR i
n
> SQL Server is not supported"
> http://support.microsoft.com/default.aspx?scid=322884
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uBcPMTWwGHA.3264@.TK2MSFTNGP03.phx.gbl...
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:8F880F51-DD0E-4FC9-8887-88A805920AC9@.microsoft.com...
> so is it possible to create an extended stored procedure in Visual Studio
> 2005 (C++)?
>
Yes, but it's much easier and safer to use SQL 2005 where you can use your
C# code inside the database.
David|||Can you show me how to do that? plz
"David Browne" wrote:

> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:8F880F51-DD0E-4FC9-8887-88A805920AC9@.microsoft.com...
> Yes, but it's much easier and safer to use SQL 2005 where you can use your
> C# code inside the database.
> David
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:884D5F80-4D8F-4252-824A-2B2438330B76@.microsoft.com...
> Can you show me how to do that? plz
>
Programming SQL Server 2005 Using the .NET Framework
http://msdn.microsoft.com/sql/learn...lr/default.aspx
David

MS SQL Srv 2000 and extended stored procedures

hi,
I have created my own dll file that I would like to use in sql 2000 as an
extended stored procedure. I have checked the dll is written correctly (I can
use it in other application). The dll file is created in C# (Visual Studio
2005).
Unfortunatelly I cannot use it in sql as the extended procedure. I am
getting error: Cannot find the function <<f_name>> in the library
<<path\library.dll>>. Reason: 127(error not found).Extended procedures need to be written in C or C++ (or Delphi, so I've heard). A language that can
create classic DLL files, not any of the modern fancy COM dlls. Also, SQL Server 2000 doesn't
support hosting any type of CLR code, whether through xp or sp_OACreate, or whether with or without
any wrappers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
> hi,
> I have created my own dll file that I would like to use in sql 2000 as an
> extended stored procedure. I have checked the dll is written correctly (I can
> use it in other application). The dll file is created in C# (Visual Studio
> 2005).
> Unfortunatelly I cannot use it in sql as the extended procedure. I am
> getting error: Cannot find the function <<f_name>> in the library
> <<path\library.dll>>. Reason: 127(error not found).|||Hi
This may be of interest:
"Using extended stored procedures or SP_OA stored procedures to load CLR in
SQL Server is not supported"
http://support.microsoft.com/default.aspx?scid=322884
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBcPMTWwGHA.3264@.TK2MSFTNGP03.phx.gbl...
> Extended procedures need to be written in C or C++ (or Delphi, so I've
> heard). A language that can create classic DLL files, not any of the
> modern fancy COM dlls. Also, SQL Server 2000 doesn't support hosting any
> type of CLR code, whether through xp or sp_OACreate, or whether with or
> without any wrappers.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
>> hi,
>> I have created my own dll file that I would like to use in sql 2000 as an
>> extended stored procedure. I have checked the dll is written correctly (I
>> can
>> use it in other application). The dll file is created in C# (Visual
>> Studio
>> 2005).
>> Unfortunatelly I cannot use it in sql as the extended procedure. I am
>> getting error: Cannot find the function <<f_name>> in the library
>> <<path\library.dll>>. Reason: 127(error not found).
>|||so is it possible to create an extended stored procedure in Visual Studio
2005 (C++)?
"Michael Epprecht [MSFT]" wrote:
> Hi
> This may be of interest:
> "Using extended stored procedures or SP_OA stored procedures to load CLR in
> SQL Server is not supported"
> http://support.microsoft.com/default.aspx?scid=322884
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uBcPMTWwGHA.3264@.TK2MSFTNGP03.phx.gbl...
> > Extended procedures need to be written in C or C++ (or Delphi, so I've
> > heard). A language that can create classic DLL files, not any of the
> > modern fancy COM dlls. Also, SQL Server 2000 doesn't support hosting any
> > type of CLR code, whether through xp or sp_OACreate, or whether with or
> > without any wrappers.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Chris" <Chris@.discussions.microsoft.com> wrote in message
> > news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
> >> hi,
> >> I have created my own dll file that I would like to use in sql 2000 as an
> >> extended stored procedure. I have checked the dll is written correctly (I
> >> can
> >> use it in other application). The dll file is created in C# (Visual
> >> Studio
> >> 2005).
> >> Unfortunatelly I cannot use it in sql as the extended procedure. I am
> >> getting error: Cannot find the function <<f_name>> in the library
> >> <<path\library.dll>>. Reason: 127(error not found).
> >
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:8F880F51-DD0E-4FC9-8887-88A805920AC9@.microsoft.com...
> so is it possible to create an extended stored procedure in Visual Studio
> 2005 (C++)?
>
Yes, but it's much easier and safer to use SQL 2005 where you can use your
C# code inside the database.
David|||Can you show me how to do that? plz
"David Browne" wrote:
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:8F880F51-DD0E-4FC9-8887-88A805920AC9@.microsoft.com...
> > so is it possible to create an extended stored procedure in Visual Studio
> > 2005 (C++)?
> >
> Yes, but it's much easier and safer to use SQL 2005 where you can use your
> C# code inside the database.
> David
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:884D5F80-4D8F-4252-824A-2B2438330B76@.microsoft.com...
> Can you show me how to do that? plz
>
Programming SQL Server 2005 Using the .NET Framework
http://msdn.microsoft.com/sql/learning/prog/clr/default.aspx
David

MS SQL Server Management Studio - permissions and stored procedures

Hi

My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables.

I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table!

Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables? :(

many thanksYour best bet is to avoid dynamic code within your stored procedure. Failing that, you need to avoid actually executing any submitted parameters within you stored procedure. Failing that, you need to thoroughly verify parameter strings before including them in any executed sql.|||hi blindman

I am not using any dynamic code, i am just passing in variables to my stored proc.

I'm not sure what you mean by:

Failing that, you need to avoid actually executing any submitted parameters within you stored procedure.

I'm using SELECT statements only in my stored proc, for example:

SELECT t3.sub_id, t2.SIC_id, t1.business_name, t1.venue_id, t1.address1, t1.address2, t1.address3, t1.address4, t1.county, t1.town, t1.postcode, t1.tel, t1.img_thumb
FROM VENUE AS t1 INNER JOIN SIC AS t2 ON t1.venue_id = t2.venue_id INNER JOIN SUBSCRIPTION AS t3 ON t1.venue_id = t3.venue_id INNER JOIN SIC_TYPE AS t4 ON t2.SIC_id = t4.SIC_id
WHERE (t3.sub_id = 1) AND (t2.SIC_id = 8 OR t2.SIC_id = 9) AND (t1.town = @.city OR @.city = '0') AND (postcode LIKE @.postcode + '%' OR @.postcode = '0') AND (county = @.county OR @.county = '0')

Can you see anything wrong with that with regards to injection attacks?

thanks|||The code you posted is not susceptible to SQL injection attacks.

MS SQL C# Extended Stored Procedures

Hello, I have wiriten several components in c# that do some simple HTML manupilation. I was wondering if it is possible to make these c# components into extended stored procedures? From my understanding an extended stored procedure must support several methods so that MS SQL can interact with it ... is there a template in c# for making extended stored procedures ... is it even possible. Thanks in advance.I think if you made a [URL=http://www.dnzone.com/ShowDetail.asp?NewsId=126] com callable wrapper [/url] it would work.

MS SQL 2005 generate separate table scripts

How do you generate separate scripts for all the objects such as
Tables or Store Procedures? This was an option in SQL 2000Are you referring to Right-click the database, Tasks, "Generate Scripts"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CompSci" <ruchir.jaipuriyar@.gmail.com> wrote in message
news:1170260018.511018.128670@.j27g2000cwj.googlegroups.com...
> How do you generate separate scripts for all the objects such as
> Tables or Store Procedures? This was an option in SQL 2000
>|||This is supported in SP2
(http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/WhatsNewSQL2005SP2.htm#BKMK_ManagementStudio).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||On Jan 31, 10:44 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> This is supported in SP2
> (http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-...).
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
there is no other way to do this untill sp2 comes out?|||Some options at: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CompSci" <ruchir.jaipuriyar@.gmail.com> wrote in message
news:1170334953.424668.248760@.a75g2000cwd.googlegroups.com...
> On Jan 31, 10:44 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> This is supported in SP2
>> (http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-...).
>> Cheers,
>> Paul Ibison SQL Server MVP,www.replicationanswers.com.
> there is no other way to do this untill sp2 comes out?
>

MS SQL 2005 generate separate table scripts

How do you generate separate scripts for all the objects such as
Tables or Store Procedures? This was an option in SQL 2000Are you referring to Right-click the database, Tasks, "Generate Scripts"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CompSci" <ruchir.jaipuriyar@.gmail.com> wrote in message
news:1170260018.511018.128670@.j27g2000cwj.googlegroups.com...
> How do you generate separate scripts for all the objects such as
> Tables or Store Procedures? This was an option in SQL 2000
>|||This is supported in SP2
(http://download.microsoft.com/downl...anagementStudio).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||On Jan 31, 10:44 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> This is supported in SP2
> (http://download.microsoft.com/downl...7-423d-bc8f-...)
.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
there is no other way to do this untill sp2 comes out?|||Some options at: http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CompSci" <ruchir.jaipuriyar@.gmail.com> wrote in message
news:1170334953.424668.248760@.a75g2000cwd.googlegroups.com...
> On Jan 31, 10:44 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> there is no other way to do this untill sp2 comes out?
>

MS SQL 2000 SP Query Plan

I have a couple of complex stored procedures that work well and quickly
once they have compiled. The problem I am running into is that every
once in a while they want to refresh thier execution plans, and when
that happens it takes about 1 minute and 30 seconds for them to
rebuild, well of course my application is set up to time out commands
after 30 seconds so basicly the stored procedure never completes and
hangs up all of my subsequent stored procdures.

I have tried to use

OPTION KEEP FIXEDPLAN

on all of my select statments but I was wondering what else could be
done to stop a stored procedure from it's need to rebuild.

-AdamAdam --

I honestly don't think that what you think is happening is actually
happening. I think what might be more realistic is that one of your
stored procedures has started scanning a table, or acquiring a
long-lived lock, causing the others to slow down. Or perhaps something
else is acquiring a lock, slowing up your procedures. 1:30 to
recompile a query plan is an absolutely enormous amount of time. Keep
in mind that the time that it takes to run your query may vary by the
inputs that are passed to it. Have you run SQL Server Profiler and run
a trace? Look for large amounts of reads and writes associated with
the long duration of your stored procedures.

-Dave|||Adam Rogas (adam.rogas@.gmail.com) writes:
> I have a couple of complex stored procedures that work well and quickly
> once they have compiled. The problem I am running into is that every
> once in a while they want to refresh thier execution plans, and when
> that happens it takes about 1 minute and 30 seconds for them to
> rebuild, well of course my application is set up to time out commands
> after 30 seconds so basicly the stored procedure never completes and
> hangs up all of my subsequent stored procdures.
> I have tried to use
> OPTION KEEP FIXEDPLAN
> on all of my select statments but I was wondering what else could be
> done to stop a stored procedure from it's need to rebuild.

As Dave says, 1 minute for a recompilation is a very long time. There
is all reason to reinvestigate whether the diagnosis is correct. There
could be several other reasons for such stalls.

One way to test this is to run a copy of a procedure with a different
name from Query Analyzer, in this fashion:

CREATE PROCEDURE alternate_name AS ...
go
DECLARE @.d datetime
SELECT @.d = getdate()
EXEC alternate_name ...
PRINT 'First run took ' + ltrim(str(datediff(ms, @.d, getdate())
go
DECLARE @.d datetime
SELECT @.d = getdate()
EXEC alternate_name ...
PRINT 'Second run took ' + ltrim(str(datediff(ms, @.d, getdate())
go
EXEC sp_recompile alternate_name ...
go
DECLARE @.d datetime
SELECT @.d = getdate()
EXEC alternate_name ...
PRINT 'Third run took ' + ltrim(str(datediff(ms, @.d, getdate())

In the first run, there is no plan in csche, so the procedure will
be compiled at least once. Data may or may not be in cache. In the
second run, plan and data is in cache. In the third run, data is still
in cache, but the procedure will be compiled again. Thus, you should
compare the second and third runs.

The biggest procedure in our system is 3000 lines of code. It takes
about 8 seconds to compile. I've seen that queries with very long
IN lists (SELECT ... FROM tbl WHERE col IN (...)) with over 15000
elements can take up to 15 seconds to compile. That is still a far
cry from 90 seconds.

If you indeed have recompilation problems, you need to analyse what the
causes are. The SP:Recompile event populates the EventSubClass column,
values are documented here:
http://support.microsoft.com/defaul...b;EN-US;q308737.

The most likely reason is changed statistics. This white paper may give
guidance in such case:
http://www.microsoft.com/technet/pr...5/qrystats.mspx.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx