Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can a CURSOR be populated by firing a stored procedure? Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 6:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
The subject says it all. Can I do this?

DECLARE Test AS CURSOR LOCAL FOR

EXECUTE uspMyStoredProcedure

OPEN Test
FETCH Next FROM Test INTO
... (field list)

WHILE (@@FETCH)_STATUS = 0)

... and so on.


I've tried a simple case and it didn't work although I got no errors. Is this even possible?


TIA friends,
Is


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1450109
Posted Tuesday, May 7, 2013 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
not directly like that, no.
If you have a stored procedure that returns data, you can insert it into a temp table, and have the cursor go through THAT.

no, adding a bit of peer review, In general, if a cursor was going to do something to data, it can and should be replaced with a set based operation instead.

There's a very good chance that whatever your cursor was going to do, can be replaced with a single command that does the same work at least an order of magnitude faster; if you'd like help with that, post more details.



as far as a specific code example, here's an example creating a temp table with the results of sp_who2 for SQL2005; from there, you could create a cursor selecting from the temp table.


CREATE TABLE #Results (
[ResultsID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL,
[REQUESTID] INT NULL
)
--table exists, insert some data
INSERT INTO #Results(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT,REQUESTID)
EXEC sp_who2

SELECT * FROM #Results



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450122
Posted Tuesday, May 7, 2013 8:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
Lowell, I would love to learn more about set-based coding but this seems to require a cursor.

I'm in a legal case management system. Each case is a Matter, matters can have several Processes, processes can have several Tasks. Each of these tables is very tightly coupled by use of integer keys, and the subsidiary tables have primary keys and also the foreign keys for all their hierarchical 'parents.'

Matter table has a MatterID
MatterProcess table has all the processes under a given Matter
MatterTask table has all the tasks under a given Matter and Process.

SO--any MatterTask row (for example) will have the following columns at minimum:

MatterID - FK
MatterProcessID - FK
MatterTaskID - PK
TaskID - FK

...and more data columns

But you can see that if I have a MatterTaskID, I can quickly find its parent Processes and parent Matter and can JOIN to get any other data points I need.

The problem is that the vendor wrote us a custom add-on subsystem to track the Parties (defendants) on each case using a new table, MatterContact. And every time we add a new party on a Matter, they coded it to create a new "Service" task on that matter/process with the party's name on it.

But to their eternal shame, they didn't add the necessary foreign key relationships, so it's not as tightly coupled as the rest of the application: they just used a MatterID (FK) and the MatterContactID (PK). So I can easily pull out ALL the parties on a particular matter, but the only way I can match a particular MatterContact record is by using a string match on the name in the MatterContact table and the name of the party's Service task, like this:

...WHERE MatterTask.Label = MatterContact

Still with me? This means that whenever I have multiple liens from, say, the "State of New York" on my matter, I will get multiple hits when I query with the MatterID and "State of New York." This makes it very difficult to query out just the distinct rows I want.

Kludgy, but right now, I feel like I need the curson in order to evaluate each entry and weed out the dupes.

Honestly? I'd be happy with a quick overview explanation of how you might handle this with a set-based solution, but it you need more information let me know.

Thanks much,





Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1450188
Posted Tuesday, May 7, 2013 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
sure hope we can help, it's honestly very satisfying to help someone eliminate a cursor when possible.

ok, you've got a cursor, and it's doing something...SELECTING and matching with your WHERE criteria:

...WHERE MatterTask.Label = MatterContact


in that situation, say you found three matching rows, you have logic that decides which row is the "right" row, or are you using this as an investigation tool?

if you have some sort of specific logic in place, then you can probably change it to a set based operation.

post your cursor if you can, and let us take a peek at it.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450200
Posted Wednesday, May 8, 2013 3:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
Actually you can, if you really want or need to!
You should use cursor datatype in output parameter of your procedure. Here you will find a sample:

http://msdn.microsoft.com/en-GB/library/ms175498(v=sql.105).aspx

Should, also mention that this technique is not used very often...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1450475
Posted Wednesday, May 8, 2013 2:24 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
Thanks, guys.

I was able to solve my own problem of weeding out the dupes by rewriting a subquery.

I had used a table alias in a subquery that was already in use in the main query and the subquery couldn't do its job properly. Once I renamed the alias in the subquery I now get exactly what I expect to see.

Thanks again for being willing to pitch in!


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1450791
Posted Wednesday, May 8, 2013 7:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
Eugene Elutin (5/8/2013)
Actually you can, if you really want or need to!
You should use cursor datatype in output parameter of your procedure. Here you will find a sample:

http://msdn.microsoft.com/en-GB/library/ms175498(v=sql.105).aspx

Should, also mention that this technique is not used very often...


Unless you are working with Oracle, then reference cursors are the only way to return data from a stored procedure.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1450862
Posted Thursday, May 9, 2013 2:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
Lynn Pettis (5/8/2013)
Eugene Elutin (5/8/2013)
Actually you can, if you really want or need to!
You should use cursor datatype in output parameter of your procedure. Here you will find a sample:

http://msdn.microsoft.com/en-GB/library/ms175498(v=sql.105).aspx

Should, also mention that this technique is not used very often...


Unless you are working with Oracle, then reference cursors are the only way to return data from a stored procedure.


Sorry, what do you mean by that?
I know at least four ways of returning result(s) (data) from stored procedure in T-SQL.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1450930
Posted Thursday, May 9, 2013 6:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
Eugene Elutin (5/9/2013)
Lynn Pettis (5/8/2013)
Eugene Elutin (5/8/2013)
Actually you can, if you really want or need to!
You should use cursor datatype in output parameter of your procedure. Here you will find a sample:

http://msdn.microsoft.com/en-GB/library/ms175498(v=sql.105).aspx

Should, also mention that this technique is not used very often...


Unless you are working with Oracle, then reference cursors are the only way to return data from a stored procedure.


Sorry, what do you mean by that?
I know at least four ways of returning result(s) (data) from stored procedure in T-SQL.


Yes, T-SQL is nice in that way. I had the opportunity to work with Oracle for a year, and the methods we can use in T-SQL (MS SQL Server) don't work in Oracle. You use reference cursors to return data, which you can also use in T-SQL by returning a cursor as an outout parameter.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451078
Posted Thursday, May 9, 2013 9:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
Lynn Pettis (5/9/2013)
Eugene Elutin (5/9/2013)
Lynn Pettis (5/8/2013)
Eugene Elutin (5/8/2013)
Actually you can, if you really want or need to!
You should use cursor datatype in output parameter of your procedure. Here you will find a sample:

http://msdn.microsoft.com/en-GB/library/ms175498(v=sql.105).aspx

Should, also mention that this technique is not used very often...


Unless you are working with Oracle, then reference cursors are the only way to return data from a stored procedure.


Sorry, what do you mean by that?
I know at least four ways of returning result(s) (data) from stored procedure in T-SQL.


Yes, T-SQL is nice in that way. I had the opportunity to work with Oracle for a year, and the methods we can use in T-SQL (MS SQL Server) don't work in Oracle. You use reference cursors to return data, which you can also use in T-SQL by returning a cursor as an outout parameter.



I'm not an Oracle expert, but I remember that it has few ways of getting back data too. Have you used pipelined functions?



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451196
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse