SQL Server Programming - Merging Stored Proc Result Set

Author

Merging Stored Proc Result Set

Craig Foster

2005-01-28, 8:59 am

Hi Guys,

How do I make a stored proc return all the fetches from a cursor into a
single result set?

For example, how can I make the output of this msdn example code exactly the
same as if I ran the query that the cursor is declared for?

DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees

OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END

CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

I will need to have the flexibilty to do row-by-row computations.

Jacco Schalkwijk

2005-01-28, 8:59 am

You hardly ever need cursors. If you can provide us with the row-by-row
computations you want to do, someone can probably rewrite it so that you
don't need a cursor.

In the mean time, you can insert the rows from your cursor into a temporary
table and at the end of your stored procedure select from this temporary
table. Don't be surprised if this takes a long time to run with large
amounts of data though.

--
Jacco Schalkwijk
SQL Server MVP

"Craig Foster" <Craig Foster@discussions.microsoft.com> wrote in message
news:BDC52BD1-7F31-4D38-A77E-1C4E8B0534D9@microsoft.com...
> Hi Guys,
>
> How do I make a stored proc return all the fetches from a cursor into a
> single result set?
>
> For example, how can I make the output of this msdn example code exactly
> the
> same as if I ran the query that the cursor is declared for?
>
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
>
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @@FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
>
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
>
> I will need to have the flexibilty to do row-by-row computations.

Francesco Anti

2005-01-28, 8:59 am

if you need a cursor for row by row computation....you can use a temporary
table
(DECLARE @TableName TABLE .... in your SP ), do an insert for each row and
then
return a select * from @TableName.
It's not a smart solution, use it only if it's strictly necessary, that is
only if you can't "translate" your computations into a query or into one or
more user-defined function.

Francesco Anti

"Craig Foster" <Craig Foster@discussions.microsoft.com> wrote in message
news:BDC52BD1-7F31-4D38-A77E-1C4E8B0534D9@microsoft.com...
> Hi Guys,
>
> How do I make a stored proc return all the fetches from a cursor into a
> single result set?
>
> For example, how can I make the output of this msdn example code exactly
the
> same as if I ran the query that the cursor is declared for?
>
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
>
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @@FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
>
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
>
> I will need to have the flexibilty to do row-by-row computations.

David Portas

2005-01-28, 8:59 am

What "row-by-row" computations do you have that you think will require
a cursor? Don't assume a cursor is required. 99.99% of the time cursors
are unnecessary and inappropriate.

Anyway, if you must, use cursors for data-modification statements. Use
SELECT statements to return data. You can make use of temp tables or
table variables modified by the cursor if necessary but as per usual,
it's far preferable to avoid using the cursor at all.
--
David Portas
SQL Server MVP
--

Craig Foster

2005-01-28, 9:01 pm

Thanks for the quick responses guys!

I need to do some string manipulations on one column (which can be put into
a function) and I need to create a new column which will be a unique integer
identifier (I was going to achieve this by using a counter variable). If this
can be done without the use of a cursor that'd be great. How would I do it?

Cheers,
Craig

"David Portas" wrote:

> What "row-by-row" computations do you have that you think will require
> a cursor? Don't assume a cursor is required. 99.99% of the time cursors
> are unnecessary and inappropriate.
>
> Anyway, if you must, use cursors for data-modification statements. Use
> SELECT statements to return data. You can make use of temp tables or
> table variables modified by the cursor if necessary but as per usual,
> it's far preferable to avoid using the cursor at all.
> --
> David Portas
> SQL Server MVP
> --
>
>

Jacco Schalkwijk

2005-01-31, 9:05 am

If you need a unique integer identifier, you can use an identity column, for
example:
ALTER TABLE your_table ADD id_column INT IDENTITY(1,1) NOT NULL,
CONSTRAINT UQ_your_table__id_column UNIQUE (id_column)

There are a number of functions for string manipulation in T-SQL, but it's
impossible to tell you which to use unless you give an example of what you
need.

--
Jacco Schalkwijk
SQL Server MVP

"Craig Foster" <CraigFoster@discussions.microsoft.com> wrote in message
news:67C1422B-89B0-4E2E-8FF1-8B4A77B60906@microsoft.com...[color=darkred]
> Thanks for the quick responses guys!
>
> I need to do some string manipulations on one column (which can be put
> into
> a function) and I need to create a new column which will be a unique
> integer
> identifier (I was going to achieve this by using a counter variable). If
> this
> can be done without the use of a cursor that'd be great. How would I do
> it?
>
> Cheers,
> Craig
>
> "David Portas" wrote:
>

Sponsored Links