Prev: Using table valued parameters to delete and update a table
Next: SQL Server 2005 Stored Procedure Performance
From: tshad on 22 Mar 2010 17:28
I have rewritten a function that calls a function recusively that passes
back a table from a stored procedure.

It now seems that I have to call that procedure over and over from another
list that I get from a common table expression. Something like:


with MyCTE as
{
....
}
INSERT INTO #Temp SELECT Account FROM MyCTE

Read through #Temp and execute the stored procedure for each row.

Is there a way to do this without a cursor?

Thanks,

Tom


From: tshad on 22 Mar 2010 17:40

"tshad" <t(a)dslextreme.com> wrote in message
news:%23qfThagyKHA.244(a)TK2MSFTNGP06.phx.gbl...
>I have rewritten a function that calls a function recusively that passes
>back a table from a stored procedure.
>
> It now seems that I have to call that procedure over and over from another
> list that I get from a common table expression. Something like:
>
>
> with MyCTE as
> {
> ...
> }
> INSERT INTO #Temp SELECT Account FROM MyCTE
>
> Read through #Temp and execute the stored procedure for each row.
>
> Is there a way to do this without a cursor?
>

I was looking at something like:

with MyCTE as
{
....
}
INSERT INTO #Temp EXEC Proc( Each line from MyCTE)

> Thanks,
>
> Tom
>


From: Erland Sommarskog on 22 Mar 2010 18:35
tshad (t(a)dslextreme.com) writes:
> I have rewritten a function that calls a function recusively that passes
> back a table from a stored procedure.
>
> It now seems that I have to call that procedure over and over from another
> list that I get from a common table expression. Something like:
>
>
> with MyCTE as
> {
> ...
> }
> INSERT INTO #Temp SELECT Account FROM MyCTE
>
> Read through #Temp and execute the stored procedure for each row.
>
> Is there a way to do this without a cursor?

Without DECLARE CURSOR, yes. Without loop, no.

You could rewrite the procedure to operate on #Temp directly.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: tshad on 22 Mar 2010 19:21

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D43EFF4DD8FDYazorman(a)127.0.0.1...
> tshad (t(a)dslextreme.com) writes:
>> I have rewritten a function that calls a function recusively that passes
>> back a table from a stored procedure.
>>
>> It now seems that I have to call that procedure over and over from
>> another
>> list that I get from a common table expression. Something like:
>>
>>
>> with MyCTE as
>> {
>> ...
>> }
>> INSERT INTO #Temp SELECT Account FROM MyCTE
>>
>> Read through #Temp and execute the stored procedure for each row.
>>
>> Is there a way to do this without a cursor?
>
> Without DECLARE CURSOR, yes. Without loop, no.
>
> You could rewrite the procedure to operate on #Temp directly.

Are you saying to pass #Temp to the procedure and execute it from there?

I was thinking of changing the stored procedure to a function that passes
back a table (which is what the stored procedure was doing. I need to
return a set (table) that is a combination of all the tables returned from
the function would be called n times:

with MyCTE as
{
....
}
SELECT f1(value) from MyCTE

Is that how I would do it?

Thanks,

Tom
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: tshad on 22 Mar 2010 19:44
I tried doing:

WITH MyCte
AS
(
SELECT * from @theTable
)
SELECT dbo.tfs_GetGroupAncesters_Function(var1) from MyCTE

But I get the error:

Cannot find either column "dbo" or the user-defined function or aggregate
"dbo.tfs_GetGroupAncesters_Function", or the name is ambiguous.

But if I run:

SELECT * from dbo.tfs_GetGroupAncesters_Function(10)

It works fine.

Tom

"tshad" <t(a)dslextreme.com> wrote in message
news:e8hr1ZhyKHA.5036(a)TK2MSFTNGP02.phx.gbl...
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9D43EFF4DD8FDYazorman(a)127.0.0.1...
>> tshad (t(a)dslextreme.com) writes:
>>> I have rewritten a function that calls a function recusively that passes
>>> back a table from a stored procedure.
>>>
>>> It now seems that I have to call that procedure over and over from
>>> another
>>> list that I get from a common table expression. Something like:
>>>
>>>
>>> with MyCTE as
>>> {
>>> ...
>>> }
>>> INSERT INTO #Temp SELECT Account FROM MyCTE
>>>
>>> Read through #Temp and execute the stored procedure for each row.
>>>
>>> Is there a way to do this without a cursor?
>>
>> Without DECLARE CURSOR, yes. Without loop, no.
>>
>> You could rewrite the procedure to operate on #Temp directly.
>
> Are you saying to pass #Temp to the procedure and execute it from there?
>
> I was thinking of changing the stored procedure to a function that passes
> back a table (which is what the stored procedure was doing. I need to
> return a set (table) that is a combination of all the tables returned from
> the function would be called n times:
>
> with MyCTE as
> {
> ...
> }
> SELECT f1(value) from MyCTE
>
> Is that how I would do it?
>
> Thanks,
>
> Tom
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
>


 |  Next  |  Last
Pages: 1 2
Prev: Using table valued parameters to delete and update a table
Next: SQL Server 2005 Stored Procedure Performance