If your example is representative of what the SP is doing, then you
can do that using plain old SQL:
-- using joins
select t2.*
from
MySchema.MyTable2 t2
inner join MySchema.MyTable1 t1 on t2.ID = t1.ID
where
t1.MyColumn > 1
-- using sub-query = slower
select *
from MySchema.MyTable2 t2
where
t2.ID in (
select t1.ID
from MySchema.MyTable1 t1
where
t1.MyColumn > 1)
If it is not possible to use only SQL, then I will let someone else
handle the answer as my knowledge of .NET hosted on SQL Server is
minimal.
Sébastien
On 8/6/07, Mike Andrews <[EMAIL PROTECTED]> wrote:
> Guys,
>
> I've got a good candidate for a CLR stored procedure and I need some advice.
> The original stored procedure used several while loops and a cursor to
> accomplish the task.
> I'm trying to convert it to a CLR stored procedure but I'm running into
> difficulties.
>
> The procedure used several table variables to store intermediate results and
> used those results as sub-selects in other queries using the IN clause.
>
> Example:
>
> DECLARE @table TABLE (
> ID uniqueidentifier
> )
>
> INSERT INTO @table ( ID )
> SELECT ID
> FROM MySchema.MyTable
> WHERE
> MyColumn > SomeValue
>
> SELECT *
> FROM Myschema.MyTable2
> WHERE
> ID IN (SELECT ID FROM @table)
>
> This occurs several times.
> What I need to know is this: is it possible to write a CLR stored procedure
> to do something similar, i.e., store the results in a "temporary" place and
> use those results to generate another query?
>
> Thanks,
> Mike
>
> ===================================
> This list is hosted by DevelopMentor(r) http://www.develop.com
>
> View archives and manage your subscription(s) at http://discuss.develop.com
>
--
Sébastien
www.sebastienlorion.com
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
