Capture data rows from output cursor into table variable

Question

Post reply

Capture data rows from output cursor into table variable

kingdonshel

SSCommitted

Points: 1704
More actions
May 13, 2012 at 4:54 am

#257881

I have been trying to insert rows into table variable from the reults of an output cursor without no success.
Does anyone know how to do this, is it possible, or is my syntax just wrong.
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'StaffySummry'
)
DROP PROCEDURE dbo.StaffySummry
GO
CREATE PROCEDURE dbo.StaffySummry
@Staffsum CURSOR VARYING OUTPUT
AS
SET @Staffsum = CURSOR FOR
SELECT
HRE.EmployeeID,FirstName,MiddleName,LastName,MaritalStatus,
AddressLine1+ ' ' + AddressLine2+ ' ' + City+ ' ' + PostalCode AS Adress
FROM
HumanResources.Employee HRE INNER JOIN HumanResources.EmployeeAddress EA
on HRE.EmployeeID = EA.EmployeeID JOIN
Person.Address A on A.AddressID = EA.AddressID JOIN
Person.Contact PC on PC.ContactID = HRE.ContactID JOIN
HumanResources.EmployeeDepartmentHistory EDH on EDH.EmployeeID = HRE.EmployeeID JOIN
HumanResources.Department HRD on HRD.DepartmentID = EDH.DepartmentID
ORDER BY HRE.EmployeeID
OPEN @Staffsum
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @EmployeSummary TABLE (Counting int Primary key identity(1,1)
,EmployeeID int
,Name varchar (50)
,FirstName varchar (50)
,MiddleName varchar (50) ,LastName varchar (50) ,MaritalStatus varchar (50) , AddressL varchar (50))
FETCH NEXT FROM @Staffsum into @EmployeSummary
PRINT @EmployeSummary
SELECT * FROM @EmployeSummary
END
GO
---execution
DECLARE @staff CURSOR
DECLARE @EmployeSummary NOT SURE WHAT DATATYPE TO PUT HERE
EXEC dbo.StaffySummry @Staffsum = @staff OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @staff into @mployeSummary
PRINT @mployeSummary
FETCH NEXT FROM @staff into @EmployeSummary
select* from @EmployeSummary = @info --'put user
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First of all, I really can't make much sense out of what you are doing. I can tell you you syntax is wrong.

Second, what are you trying to accomplish with this procedure? Why are you trying to rerun a cursor as output?

Reminds me of Oracle and that the only way to return a result set from a procedure was with a reference cursor.

kingdonshel SSCommitted Points: 1704 More actions · Answer 2

Its just for practice I am not sure how to use a procedure with Cursor output,

I was trying to populate a tale variable with the results form the cursor output.

if you have an example of how this can be done or coded properly I would really appreciate, to help with my understanding of cursors with output.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Check out this series of articles, here[/url]. The notoriously opinionated author never finished the series, but I think that you'll find the answers to your questions in the first two installments.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Lynn Pettis (5/13/2012)
First of all, I really can't make much sense out of what you are doing. I can tell you you syntax is wrong.

I can't make sense of it either and I can't tell whether the syntax is wrong because I don't know the syntax for CURSORs. 😛

I figure I can look it up in Books On Line if I ever really need it...

Did you know that the abbreviation for CURSOR is curse?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

kingdonshel (5/13/2012)
I have been trying to insert rows into table variable from the reults of an output cursor ...

What is it that you're trying to accomplish here? If you can describe this as a single-sentence "job spec", such as "Copy all data from the Sales table for 2009 to an archive table called SalesArchives, then remove all data from the Sales table for 2009", then it's highly likely that the good folks here will determine a realistic approach - and help you with coding it up, to boot.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden