May 7, 2013 at 6:52 am
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
May 7, 2013 at 7:13 am
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
May 7, 2013 at 8:33 am
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
May 7, 2013 at 8:54 am
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
May 8, 2013 at 3:43 am
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...
May 8, 2013 at 2:24 pm
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
May 8, 2013 at 7:40 pm
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.
😉
May 9, 2013 at 2:19 am
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.
May 9, 2013 at 6:39 am
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.
May 9, 2013 at 9:31 am
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?
May 9, 2013 at 10:35 am
Eugene Elutin (5/9/2013)
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?
Nope and where I work now I don't have to worry about writing PL/SQL either. I like T-SQL, it just seems right.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply