Cursor versus Temporary Table

Hi All,
I am writing a stored procedure in which I need to read some records from a table which satisfy given condition, fetch the last read record and check its value.

e.g.
SELECT *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo.

I want to read the last record of each TestReqNo and check some values from that row. For this I was thiking of reading the above Select using a cursor and then using FETCH LAST to read the last row into some variable.

But this seems to be a round about way and also i have been reading that cursor will slow the execution. IS there any other better way. Should I use temp table instead, will that be more efficient ?

Please let me know your suggestions.
Thanks,
SnigdhaYou can reverse the order and just select the first record:

SELECT TOP 1 *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo DESC|||Hi,
Thanks a lot for this solution, but there is still a little problem. The above query returns the last record of the last TestRequestNO. But I wanted to the last record of each TestRequestNO. For e.g.:

TestRequestNo TestRequestStateId
TR123 11
TR123 3
TR123 5
TR123 12

TR155 11
TR155 3
TR155 5

TR007 11
TR007 12
TR007 3

Now, out of this orderd set of TestRequestNos I want the last record of each of the TestRequestNo: TR123, TR155, TR007.

Thanks,
Snigdha

|||

So, something along these lines?

USE Northwind

SELECT t1.* FROM [order details] t1
WHERE t1.Quantity=
(SELECT MAX(Quantity) FROM [order details] t2
WHERE t1.orderid=t2.orderid)
ORDER BY t1.orderid

SELECT t1.* FROM [order details] t1 INNER JOIN
(SELECT orderid, MAX(Quantity) AS maxdate FROM [order details] GROUP BY orderid) t2
ON t1.orderid = t2.orderid
AND t1.Quantity = t2.maxdate
ORDER BY t1.orderid
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

|||Hey,
Thanks a loooot...
the first query worked perfectly the way I wanted it !!!! :)

There are minor things remaining which I think I should be able to handle.
Thanks a lot once again,
Snigdha

cursor usage

hi guys

i have a table that contains a tremendous amount of row. i have written a stored procedure that takes a summary of that information and updates it's master table as well as another table. the problem is it takes very long to do. is what i am doing correct or is there a better way. here is the source

CREATE PROCEDURE update_cvrbatches
AS

declare @.code varchar(25),@.type varchar(3),@.batchno varchar(10),@.batchqty Float,@.issued float,@.returned float,@.transfered float,@.itemcount float,@.totestcost float,@.totactcost float,@.reserved float,@.warehouse varchar(3)

Update cvrwarehouse set BoughtQty =0, IssuedQty = 0, ReservedQty =0 ,Returned = 0 ,Transfered = 0

DECLARE getbatches CURSOR
for
select Code,Type,BatchNo,sum(BoughtQty) as BoughtQty,sum(IssuedQty) as IssuedQty,sum(ReservedQty) as ReservedQty,sum(ReturnedQty) as ReturnedQty,sum(TransferQty) as TransferQty,count(Barcode) as ItemCount,
sum(EstCost) as EstCost,sum(ActCost) as ActCost,Warehouse
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
OPEN getbatches

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost ,@.warehouse
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doen iets hier

update cvrbatchctrl set BatchQty = @.batchqty, Issued = @.issued, Reserved = @.reserved ,Returned = @.returned ,Transfered = @.transfered, ItemCount = @.itemcount,TotalEstCost = @.totestcost,TotalActCost = @.totactcost
where Code = @.code and Type = @.type and Warehouse = @.warehouse and BatchNo = @.batchno
update cvrwarehouse set BoughtQty =BoughtQty + @.batchqty, IssuedQty = IssuedQty + @.issued, ReservedQty = ReservedQty + @.reserved ,Returned = Returned + @.returned ,Transfered = Transfered + @.transfered
where Code = @.code and Type = @.type and Warehouse = @.warehouse

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost,@.warehouse

END
CLOSE getbatches
DEALLOCATE getbatches

is there a better way?

Hi,

better use setbased solutions rather than cursors, one example would be (*untested*) the below one: (put in a more human readble format)

update cvrbatchctrl
set BatchQty = SUbQuery.BoughtQty,
Issued = SUbQuery.IssuedQty,
Reserved = SUbQuery.ReservedQty,
Returned = SUbQuery.ReservedQty,
Transfered = SUbQuery.TransferQty,
ItemCount = SUbQuery.ItemCount,
TotalEstCost = SUbQuery.EstCost,
TotalActCost = SUbQuery.ActCost
FROM cvrbatchctrl cvr
INNER JOIN
(
select Code,
Type,
BatchNo,
Warehouse,
sum(BoughtQty) as BoughtQty,
sum(IssuedQty) as IssuedQty,
sum(ReservedQty) as ReservedQty,
sum(ReturnedQty) as ReturnedQty,
sum(TransferQty) as TransferQty,
count(Barcode) as ItemCount,
sum(EstCost) as EstCost,
sum(ActCost) as ActCost
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
) SUbQuery
ON
cvr.Code = SUbQuery.Code and
cvr.Type = SUbQuery.Type and
cvr.Warehouse = SUbQuery.Warehouse and
cvr.BatchNo = SUbQuery.batchno

--Second one, taking the value from the first update (make surethat your condition is complete in the below script)

update cvrwarehouse
set BoughtQty = cvrhouse.BoughtQty + @.batchqty,
IssuedQty = cvrhouse.IssuedQty + @.issued,
ReservedQty = cvrhouse.ReservedQty + @.reserved ,
Returned = cvrhouse.Returned + @.returned ,
Transfered = cvrhouse.Transfered + @.transfered
FROM cvrwarehouse cvrhouse
INNER JOIN cvrbatchctrl cvrbatch
ON
cvrhouse.Code = cvrbatchctrl.Code AND
cvrhouse.Type = cvrbatchctrl.Type
cvrhouse.Warehouse = cvrbatchctrl.Warehouse

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you.

haven't tried it yet but it sure makes for an interesting solution.

let you know as soon as i do.

again thanks

|||

i just tested it

works really well and the speed is a lot better

thanks again

cursor to alter table, add columns

Friends,
I am trying to use a cursor inside a stored procedure to add columns to a
temp table. Can anyone tell me how to fix the following code so the cursor
will feed new column names to the alter table statement?
The code fails when I try to use a variable as a column name in the alter
table statement.
Thanks for your help ...
DECLARE @.strVar varchar(7)
If object_id('tempdb..#temptbl') is not null
begin
drop table #temptbl
end
Create Table #temptbl
(sku varchar(15) null)
DECLARE mycursor CURSOR
FOR
SELECT colA
FROM PermTable
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.strVar
alter table #temptbl
add
@.strVAr varchar(20) null
WHILE @.@.fetch_status = 0
BEGIN
FETCH NEXT
FROM mycursor
INTO @.strVar
alter table #temptbl
add
@.strVar varchar(20) null
END
CLOSE mycursor
DEALLOCATE mycursorWhy in the world would you ever want to do this'''?
nevermind.......
CREATE TABLE #thisisstupid(colname VARCHAR(256))
GO
INSERT #thisisstupid(colname)
SELECT 'col1' UNION ALL
SELECT 'col2' UNION ALL
SELECT 'col3' UNION ALL
SELECT 'col4'
DECLARE @.thisisstupider VARCHAR(4000)
SELECT @.thisisstupider = ''
SELECT @.thisisstupider = @.thisisstupider + colname + ' VARCHAR(20), '
FROM #thisisstupid
SELECT @.thisisstupider = 'ALTER TABLE #thisisstupid ADD ' + @.thisisstupider
SELECT @.thisisstupider = LEFT(@.thisisstupider,LEN(@.thisisstupider
)-1)
EXEC(@.thisisstupider)
SELECT * FROM #thisisstupid
DROP TABLE #thisisstupid
GO
"bill_morgan_3333" <billmorgan3333@.discussions.microsoft.com> wrote in
message news:50997181-3CC0-45EE-97FE-7C389EDB6909@.microsoft.com...
> Friends,
> I am trying to use a cursor inside a stored procedure to add columns to a
> temp table. Can anyone tell me how to fix the following code so the cursor
> will feed new column names to the alter table statement?
> The code fails when I try to use a variable as a column name in the alter
> table statement.
> Thanks for your help ...
>
> DECLARE @.strVar varchar(7)
> If object_id('tempdb..#temptbl') is not null
> begin
> drop table #temptbl
> end
> Create Table #temptbl
> (sku varchar(15) null)
> DECLARE mycursor CURSOR
> FOR
> SELECT colA
> FROM PermTable
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.strVar
> alter table #temptbl
> add
> @.strVAr varchar(20) null
> WHILE @.@.fetch_status = 0
> BEGIN
> FETCH NEXT
> FROM mycursor
> INTO @.strVar
> alter table #temptbl
> add
> @.strVar varchar(20) null
> END
> CLOSE mycursor
> DEALLOCATE mycursor
>|||If you are so disgusted, why even reply? Keep your "stupid" remarks to
yourself.|||You may need to do this when you want the procedure to return a "pivot table
"
- i.e., the values in one column need to become column headers in the
returned records -in the meantime I was able to find a guy at a local compan
y
who is familiar with this technique, using a cursor. It does involve storin
g
the entire ALTER TABLE statement ( + a variable for the new column value)
inside a variable, as you've done below.
Inside each loop the new column value is updated and the sp_executesql is
executed to run the ALTER TABLE string.
Thanks for your reply ...
bill morgan
"Derrick Leggett" wrote:

> Why in the world would you ever want to do this'''?
> nevermind.......
>
> CREATE TABLE #thisisstupid(colname VARCHAR(256))
> GO
> INSERT #thisisstupid(colname)
> SELECT 'col1' UNION ALL
> SELECT 'col2' UNION ALL
> SELECT 'col3' UNION ALL
> SELECT 'col4'
> DECLARE @.thisisstupider VARCHAR(4000)
> SELECT @.thisisstupider = ''
> SELECT @.thisisstupider = @.thisisstupider + colname + ' VARCHAR(20), '
> FROM #thisisstupid
> SELECT @.thisisstupider = 'ALTER TABLE #thisisstupid ADD ' + @.thisisstupide
r
> SELECT @.thisisstupider = LEFT(@.thisisstupider,LEN(@.thisisstupider
)-1)
> EXEC(@.thisisstupider)
> SELECT * FROM #thisisstupid
> DROP TABLE #thisisstupid
> GO
> "bill_morgan_3333" <billmorgan3333@.discussions.microsoft.com> wrote in
> message news:50997181-3CC0-45EE-97FE-7C389EDB6909@.microsoft.com...
>
>|||Because normally people do this for a stupid reason. :) He actually has an
interesting reason for doing it. Calm down guy. It's not the end of the
world. He took it a little better than you. And, it is stupid that you
would have to do this for a PIVOT table in SQL Server. It's also
unfortunate in 2005 that they haven't fixed this. You still need to
hardcode the values for the columns, which IS STUPID!!!!! And, I won't keep
my stupid remarks to myself. People need to think about what they are
doing.
The MS reason for the pivot table in 2005 being written in that format is
because "it would cause problems with the optimizer otherwise". That really
doesn't cut it. The pivot is a great idea. The way they implemented it
though forces the everyday user to resort to dynamic SQL for it to be truly
useful. That's a shame. They should have a warning in Books Online about
the optimizer and plan generator having some issues with a dynamic comic
list, not just exclude the functionality completely.
"bd" <bryce_dooley123@.yahoo.com> wrote in message
news:1110478752.721077.32400@.o13g2000cwo.googlegroups.com...
> If you are so disgusted, why even reply? Keep your "stupid" remarks to
> yourself.
>sql

CURSOR question

I have written my first stored proc using a cursor and am wondering if I did
it correctly. It does work, but I wanted to make sure I am using it
correctly and that this code is optimized or if there was a simpler way to
do it (maybe without cursors). Basically what I needed to do was loop
through a recordset and execute an SP against each record found. Any advice
is appreciated, here is my SP...
--create stored procedure
create proc udpTest
@.cdid integer --CDID
AS
DECLARE @.eid integer --EventID
--create the cursor
DECLARE CursEvent CURSOR FOR
SELECT EventID FROM tblEvent
WHERE EventCDID = @.cdid
OPEN CursEvent
-- Perform the first fetch
FETCH NEXT FROM CursEvent INTO @.eid
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Run SP
EXECUTE udpTwo @.eid
FETCH NEXT FROM CursEvent INTO @.eid
END
--close the cursor
CLOSE CursEvent
DEALLOCATE CursEvent
GOOn Wed, 10 Aug 2005 02:21:05 -0400, Mark Hoffy wrote:

>I have written my first stored proc using a cursor and am wondering if I di
d
>it correctly.
Hi Mark,
Probably not. In 99% of all cases, the choice to use a cursor is
incorrect.

>It does work, but I wanted to make sure I am using it
>correctly and that this code is optimized or if there was a simpler way to
>do it (maybe without cursors).
Exactly. Use the strength of SQL to process the whole set at once and
let the optimizer figure out the most efficient way to do it.

>Basically what I needed to do was loop
>through a recordset and execute an SP against each record found.
No - what you needed to do was write one single query that will do
whatever the SP does, but for all rows at once.

>Any advice
>is appreciated, here is my SP...
If you really want some good advice, then post the stored proc that you
are calling for each row in the cursor as well. Add to that the
structure of all tables involved (as CREATE TABLE statements - omit
columns that are irrelevant in this case, but do include all constraints
and properties), some illustrative sample data (as INSERT statements)
and a short description of the business requirements you are trying to
satisfy.
See www.aspfaq.com/5006 for some more comments on the best way to get
help from the groups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No - what I needed to do was loop through a recordset and execute an SP
against each record found. I can ask my own questions - thanks.
The stored proc that is being called is called from several different places
in different forms. Even if my solution was to create an SP for each
instance, it would mean writing several versions of nearly the same proc and
having to maintain the logic in multiple places. Also, the proc called is
very complex, incorporating a number of lookups while updating several
tables. If it were a simple single-line-of-logic sp, I would agree and
would never have approached a cursor solution.
If anyone has any comments on my cursor code or other ways to implement
this, I would be happy to read them. Thanks.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p8cnf15huc04sohldc31qimc8qufl09o6i@.
4ax.com...
> On Wed, 10 Aug 2005 02:21:05 -0400, Mark Hoffy wrote:
>
did
> Hi Mark,
> Probably not. In 99% of all cases, the choice to use a cursor is
> incorrect.
>
to
> Exactly. Use the strength of SQL to process the whole set at once and
> let the optimizer figure out the most efficient way to do it.
>
> No - what you needed to do was write one single query that will do
> whatever the SP does, but for all rows at once.
>
> If you really want some good advice, then post the stored proc that you
> are calling for each row in the cursor as well. Add to that the
> structure of all tables involved (as CREATE TABLE statements - omit
> columns that are irrelevant in this case, but do include all constraints
> and properties), some illustrative sample data (as INSERT statements)
> and a short description of the business requirements you are trying to
> satisfy.
> See www.aspfaq.com/5006 for some more comments on the best way to get
> help from the groups.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 11 Aug 2005 17:51:40 -0400, Mark Hoffy wrote:

>No - what I needed to do was loop through a recordset and execute an SP
>against each record found. I can ask my own questions - thanks.
Well, you did write "any advice is appreciated".
Sorry for bothering you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> No - what I needed to do was loop through a recordset and execute an SP agains
t each record,[sic] found. I can ask my own questions - thanks. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
I agree with Hugo.
In all the DECADES -- not years-- I have been involved with SQL, I have
written five cursors. If the CASE expression had been available, I
would have avoid three of the five. Is this an NP-Complete problem?
So what? If the procedures are logically different, then they nee dtpo
be in separate modules. This is basic software engineering and is far,
far more fundamental than SQL practices.
Because you do not know a record is not a row, and describe a procedure
with poor cohesion, I think you are a procedural programmer that cannot
think of a relational solution. You also put "tbl-" prefixes on
singular names, and have other signs of OO and 1960's BASIC thinking.
Again, we cannot fix code that we cannot see. Post the procedure and a
clear spec.|||I'll refrain from the "do not use cursors" bit (you've seen that in the
other replies ;o) )
Other than that and assuming that in this case you do need a cursor to call
the second sp, you might consider adding
LOCAL FAST_FORWARD
to your cursor call. Other than that I think it's basically right...
Greets, Lee-Z
"Mark Hoffy" <mark@.here.com> wrote in message news:OmKKe.6$F_7.1@.fe06.lga...
>I have written my first stored proc using a cursor and am wondering if I
>did
> it correctly. It does work, but I wanted to make sure I am using it
> correctly and that this code is optimized or if there was a simpler way to
> do it (maybe without cursors). Basically what I needed to do was loop
> through a recordset and execute an SP against each record found. Any
> advice
> is appreciated, here is my SP...
> --create stored procedure
> create proc udpTest
> @.cdid integer --CDID
> AS
> DECLARE @.eid integer --EventID
> --create the cursor
> DECLARE CursEvent CURSOR FOR
> SELECT EventID FROM tblEvent
> WHERE EventCDID = @.cdid
> OPEN CursEvent
> -- Perform the first fetch
> FETCH NEXT FROM CursEvent INTO @.eid
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --Run SP
> EXECUTE udpTwo @.eid
> FETCH NEXT FROM CursEvent INTO @.eid
> END
> --close the cursor
> CLOSE CursEvent
> DEALLOCATE CursEvent
> GO
>|||None of what you've said here rules out a set-based solution. If you
have a legacy proc designed to work only with single rows then you have
to decide whether it's worth a re-write or if iyou can live with the
cursor solution. The "better" solution is to avoid designing-in these
limitations in the first place.
David Portas
SQL Server MVP
--|||You should use a local cursor. A local cursor is closed and deallocated
when it goes out of scope, whereas a global cursor stays allocated and open
for as long as the connection is open, unless explicitly closed and
deallocated. (DECLARE @.CursEvent CURSOR SET @.CursEvent = CURSOR LOCAL
FAST_FORWARD FOR SELECT ...). If an error occurs that terminates the batch,
a local cursor will be closed and deallocated, whereas a global cursor
remains open. Note: you should still explicitly close and deallocate a
local cursor, because you should free resources as soon as you're finished
with them.
You should add error handling. If the SP returns a value (in a RETURN
statement) then you should check that value (EXEC @.RC = udpTwo @.eid). You
should also check @.@.ERROR after the EXEC statement (IF @.RC != 0 OR @.@.ERROR
!= 0 GOTO ERROR). In addition, you should check @.@.CURSOR_ROWS before
executing the first fetch. There's no point in entering the fetch loop if
there's nothing to do. (Note that @.@.CURSOR_ROWS may return a negative
number if the cursor is asynchronously populated, so you should use
@.@.CURSOR_ROWS != 0 instead of @.@.CURSOR_ROWS > 0 to determine whether you
should proceed into the fetch loop.)
"Mark Hoffy" <mark@.here.com> wrote in message news:OmKKe.6$F_7.1@.fe06.lga...
> I have written my first stored proc using a cursor and am wondering if I
did
> it correctly. It does work, but I wanted to make sure I am using it
> correctly and that this code is optimized or if there was a simpler way to
> do it (maybe without cursors). Basically what I needed to do was loop
> through a recordset and execute an SP against each record found. Any
advice
> is appreciated, here is my SP...
> --create stored procedure
> create proc udpTest
> @.cdid integer --CDID
> AS
> DECLARE @.eid integer --EventID
> --create the cursor
> DECLARE CursEvent CURSOR FOR
> SELECT EventID FROM tblEvent
> WHERE EventCDID = @.cdid
> OPEN CursEvent
> -- Perform the first fetch
> FETCH NEXT FROM CursEvent INTO @.eid
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --Run SP
> EXECUTE udpTwo @.eid
> FETCH NEXT FROM CursEvent INTO @.eid
> END
> --close the cursor
> CLOSE CursEvent
> DEALLOCATE CursEvent
> GO
>

cursor problem

hi,
I have a stored procedure in which i have a cursor cur1.
when there is an error in the SP the cursor is not closed on exit of the SP.
i am currently looping syscursors to find the currently open cursor n then
deallocating it as shown below.. but i realize the user needs perm to access
the syscursors which i dont want to give. what other way can i deallocate
these cursors?
IF EXISTS
(SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
DEALLOCATE UpdateCursor
thanks
IChorCursors are evil. do not use them!
OK. Seriously 99% of the time, there will be a set based alternative.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
> SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
> access the syscursors which i dont want to give. what other way can i
> deallocate these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||ichor
You can try on your risk dbcc activecursors to find an active cursor/s. I'd
not recommend you using this command in the production.
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
> SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
> access the syscursors which i dont want to give. what other way can i
> deallocate these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||Hi
Posting DDL for the procedure may help to answer this question, you may want
to read http://www.aspfaq.com/etiquette.asp?id=5006 and
http://www.aspfaq.com/show.asp?id=2081
If the error can be handled in T-SQL then you should know which cursors are
open and close them!
For error handling check out:
http://www.sommarskog.se/error-handling-II.html
and
http://www.sommarskog.se/error-handling-I.html
A set based solution is usually more efficient than a cursor based one
therefore you may want to see about changing the design to use less cursors.
John
"ichor" wrote:

> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the S
P.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to acce
ss
> the syscursors which i dont want to give. what other way can i deallocate
> these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>
>|||1. Rewrite your error handling so that the SP terminates more easily.
2. Get rid of the cursors altogether.
David Portas
SQL Server MVP
--|||hi where can i learn more about set based solutions?
i would like to avoid cursors completely.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyIeW$LmFHA.2156@.TK2MSFTNGP14.phx.gbl...
> ichor
> You can try on your risk dbcc activecursors to find an active cursor/s.
> I'd not recommend you using this command in the production.
>
> "ichor" <ichor@.hotmail.com> wrote in message
> news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
>|||Hi
I am not sure if there us any one place where you can do this!
A starter may be to look at itzik Ben-Gan's articles in SQL Server magazine:
http://www.windowsitpro.com/Authors...ID/638/638.html
And the book that was co-authored with Tom Moreau
"Advanced Transact-SQL for SQL Server 2000" ISBN: 1893115828
http://search.barnesandnoble.com/bo...3115828

John
"ichor" wrote:

> hi where can i learn more about set based solutions?
> i would like to avoid cursors completely.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyIeW$LmFHA.2156@.TK2MSFTNGP14.phx.gbl...
>
>|||Hi
Donot use Cursor . They hurt performance badly.
But if you need to use cursor , you may use
CURSOR_STATUS
(
{ 'local' , 'cursor_name' }
| { 'global' , 'cursor_name' }
| { 'variable' , 'cursor_variable' }
)
to find the status of the cursor
and then close it and deallocate it
May this help you solve the problem
With warm regards
Jatinder Singh|||Use a local cursor instead of a global cursor. Local cursors are implicitly
deallocated when the batch that created it terminates. If they are declared
in a stored procedure, then they are deallocated when the stored procedure
exits, unless returned as an output paramenter from the procedure.
DECLARE @.X CURSOR
SET @.X = CURSOR LOCAL [other options] FOR ...
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
access
> the syscursors which i dont want to give. what other way can i deallocate
> these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||SQL PROGRAMMING STYLE, chapters 8, 9 and 10 for some help.

Cursor or Table For Stored Procedure ?

Hello,
I have the following probleman :
I have a stored procedure that retrieve results of a select statement,
and i would like to store this results in a cursor or temporary table
or something like that.
Could u help me ? Tks...
Code that i tried :
create procedure ListOfProducts
@.categoryID int
as begin
select * from Product
end
declare c int
declare c cursor for ListOfProducts 1
open c
fetch next from c into @.idProduct
print @.idProduct
close c
deallocate cHi,
If you want to store a stored procedure result into a temporary table, use
INSERT/EXEC statement. Create a temporary table first using CREATE TABLE.
Tomasz B.
"Carlao" wrote:

> Hello,
> I have the following probleman :
> I have a stored procedure that retrieve results of a select statement,
> and i would like to store this results in a cursor or temporary table
> or something like that.
> Could u help me ? Tks...
> Code that i tried :
> create procedure ListOfProducts
> @.categoryID int
> as begin
> select * from Product
> end
> declare c int
> declare c cursor for ListOfProducts 1
> open c
> fetch next from c into @.idProduct
> print @.idProduct
> close c
> deallocate c
>|||If you want the instances of sql server, then you can use SQL-DMO API,
specifically the ListAvailableSQLServers Method of the Application object.
http://www.microsoft.com/resources/... />
c3561.mspx
AMB
"Carlao" wrote:

> Hello,
> I have the following probleman :
> I have a stored procedure that retrieve results of a select statement,
> and i would like to store this results in a cursor or temporary table
> or something like that.
> Could u help me ? Tks...
> Code that i tried :
> create procedure ListOfProducts
> @.categoryID int
> as begin
> select * from Product
> end
> declare c int
> declare c cursor for ListOfProducts 1
> open c
> fetch next from c into @.idProduct
> print @.idProduct
> close c
> deallocate c
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> If you want the instances of sql server, then you can use SQL-DMO API,
> specifically the ListAvailableSQLServers Method of the Application object.
> http://www.microsoft.com/resources/...>
0/c3561.mspx
>
> AMB
> "Carlao" wrote:
>|||You can create a temporary table (local or global) or a permanent one to gra
b
the result of the sp. You can also return a cursor variable from your sp.
Example:
use northwind
go
create procedure proc1
@.sd datetime,
@.ed datetime
as
set nocount on
declare @.rv int
set @.rv = 1
execute @.rv = dbo.[Sales by Year] @.sd, @.ed
return coalesce(nullif(@.rv, 0), @.@.error)
go
create procedure proc2
@.sd datetime,
@.ed datetime
as
set nocount on
create table #t (
ShippedDate datetime,
OrderID int,
Subtotal money,
col_year int
)
insert into #t
exec proc1 @.sd, @.ed
select * from #t
where Subtotal between 2000.00 and 3000.00
order by ShippedDate
drop table #t
return
go
execute proc2 '19960101', '19981231'
go
drop procedure proc2, proc1
go
AMB
"Carlao" wrote:

> Hello,
> I have the following probleman :
> I have a stored procedure that retrieve results of a select statement,
> and i would like to store this results in a cursor or temporary table
> or something like that.
> Could u help me ? Tks...
> Code that i tried :
> create procedure ListOfProducts
> @.categoryID int
> as begin
> select * from Product
> end
> declare c int
> declare c cursor for ListOfProducts 1
> open c
> fetch next from c into @.idProduct
> print @.idProduct
> close c
> deallocate c
>

Cursor or Several Stored Procs

Would I be better served (ie faster execution & general database sexiness)
using the dreaded, hated, and loathsome cursor or to attempt to write a
series of stored procedures (6-12) to work with result sets of matching
criteria?
I am importing a large number of csv's to update existing information &
execute stored proc's aimed at updating / calculating an individual's data
based with multiple conditions.Hi
It depends on what you are doing! Usually it is better (and quicker) to use
a set based solution. If your code has a significant amount of branching
calling separate procedures may be an advantage.
HTH
John
"Clamps" wrote:

> Would I be better served (ie faster execution & general database sexiness)
> using the dreaded, hated, and loathsome cursor or to attempt to write a
> series of stored procedures (6-12) to work with result sets of matching
> criteria?
> I am importing a large number of csv's to update existing information &
> execute stored proc's aimed at updating / calculating an individual's data
> based with multiple conditions.
>
>sql

cursor on results from stored procedure?

I am working on a stored procedure that needs to pass through the results of another procedure, using a cursor. Any idea why this isn't working? The exec statement works fine when I try it on it's own.

DECLARE myCursor CURSOR
FOR (exec sp_splitstring @.array = @.searchstr, @.separator = ' ')
OPEN myCursorWhy not create a temp table instead of using a cursor.

create table #a
insert #a exec sp_...

This looks like you are processing csv strings - I usually do this by

create table #csvint (id int identity, myid int, value int)
exec spProcCsvint myid = 1, csv =@.csv

In this way you can hold a lot of array values in the same table identified by the myid value.
You can loop through the values using id - but usually you would just join to it.

Cursor not completing when stored procedure runs within it

I am having an interesting problem I haven't seen.
First, here's the code that sets up the cursor, with a select statement
where the exec should be, and the results:
DECLARE @.order_id int,
@.row_id int,
@.qty_rtn int,
@.invoice_id int,
@.date_shipped datetime
DECLARE order_return CURSOR FOR
select r.order_id_display, r.row_id -1, r.quantity, s.line_id,
getdate() from batch..temp_response r, shipment s,
receipt_item i
where isnull(r.status, 0) >= 0 and new_status in ('R', 'U')
and i.i_order_id_display = r.order_id_display
and i.order_id = s.order_id
and i.row_id = r.row_id - 1
and i.upc=r.upc and amount = 1
and i.order_id in ('0FD94RQXB4JL9J8V4R3G5B8CC5') --for
testing purposes I selected one order only
OPEN order_return
FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
@.invoice_id, @.date_shipped
WHILE @.@.FETCH_STATUS = 0
BEGIN
select 'exec process_line_item_shipping', @.order_id, @.row_id, 0,
@.qty_rtn, @.date_shipped, @.invoice_id
-- exec process_line_item_shipping @.order_id, @.row_id, 0, @.qty_rtn,
@.date_shipped, @.invoice_id
FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
@.invoice_id, @.date_shipped
END
CLOSE order_return
DEALLOCATE order_return
This returns
exec process_line_item_shipping 491232 0 0 1
2006-06-16 12:46:19.330 534386
exec process_line_item_shipping 491232 1 0 1
2006-06-16 12:46:19.330 534386
Which is exactly what I'd expect.
HOWEVER... when I remove the comment tag off the actual SP exec
command, then I ONLY get
exec process_line_item_shipping 491232 0 0 1
2006-06-16 12:46:19.330 534386
and only the first exec statement runs.
I've done a select @.@.fetch_status before and after the exec statement,
and it's 0 each time.
The stored procedure run has no cursors within it, just several
calculations, inserts and update statements.
Can someone figure this out for me?DOINK!
Never mind, I think I figured it out. When I changed it to an
INSENSITIVE cursor, all rows were executed -- basically the updates
were invalidating the remaining row's work, and so it wouldn't fetch
anymore rows.
At least I think that's what happened.
dwcscreenwriterextremesupr...@.gmail.com wrote:
> I am having an interesting problem I haven't seen.
> First, here's the code that sets up the cursor, with a select statement
> where the exec should be, and the results:
> DECLARE @.order_id int,
> @.row_id int,
> @.qty_rtn int,
> @.invoice_id int,
> @.date_shipped datetime
> DECLARE order_return CURSOR FOR
> select r.order_id_display, r.row_id -1, r.quantity, s.line_id,
> getdate() from batch..temp_response r, shipment s,
> receipt_item i
> where isnull(r.status, 0) >= 0 and new_status in ('R', 'U')
> and i.i_order_id_display = r.order_id_display
> and i.order_id = s.order_id
> and i.row_id = r.row_id - 1
> and i.upc=r.upc and amount = 1
> and i.order_id in ('0FD94RQXB4JL9J8V4R3G5B8CC5') --for
> testing purposes I selected one order only
> OPEN order_return
> FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
> @.invoice_id, @.date_shipped
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select 'exec process_line_item_shipping', @.order_id, @.row_id, 0,
> @.qty_rtn, @.date_shipped, @.invoice_id
> -- exec process_line_item_shipping @.order_id, @.row_id, 0, @.qty_rtn,
> @.date_shipped, @.invoice_id
> FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
> @.invoice_id, @.date_shipped
> END
> CLOSE order_return
> DEALLOCATE order_return
> This returns
> exec process_line_item_shipping 491232 0 0 1
> 2006-06-16 12:46:19.330 534386
> exec process_line_item_shipping 491232 1 0 1
> 2006-06-16 12:46:19.330 534386
> Which is exactly what I'd expect.
> HOWEVER... when I remove the comment tag off the actual SP exec
> command, then I ONLY get
> exec process_line_item_shipping 491232 0 0 1
> 2006-06-16 12:46:19.330 534386
> and only the first exec statement runs.
> I've done a select @.@.fetch_status before and after the exec statement,
> and it's 0 each time.
>
> The stored procedure run has no cursors within it, just several
> calculations, inserts and update statements.
> Can someone figure this out for me?|||Nope... That's not it... because now the inserts and updates aren't
happening. Argh! Help!
dwcscreenwriterextremesupr...@.gmail.com wrote:
> DOINK!
> Never mind, I think I figured it out. When I changed it to an
> INSENSITIVE cursor, all rows were executed -- basically the updates
> were invalidating the remaining row's work, and so it wouldn't fetch
> anymore rows.
> At least I think that's what happened.
>
> dwcscreenwriterextremesupr...@.gmail.com wrote:|||>> Nope... That's not it... <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
What you did post was awful. You are using SQL cursors, which are the
worst way to use SQL -- orders of magnitude poorer performance, lack of
portability, etc. Read some of the postings here and *any* other SQL
Newsgroup. My rule of thumb is that you should not write more than
five of them in 25 years in IT.
Looking at what you did post, it looks like you missed most of the
basic ideas of RDBMS and building a procedural routine that mimics a
file system. .
1) Why would anyone put the display order into a table? All display
work is done in the front end and not the database.
2) Ignoring design flaw #1, why did you use two different names for the
same data element (I.i_order_id_display = R.order_id_display)? Surely
nobody would put the data type or table on a data element.
3) What is a row_id? If it refers to the physical rows in a table,
then it is wrong. If it refers to the position on the input screen or
original paper form, then it is wrong. You woudl be mimicing a paper
form instead of building a relational model.
4) You use vague data element names Amount of what? It does not seem
to be money. Quantity of what? Ordered or returned or on-hand, or what?
That is like an adjective without a noun.
5) Why don't you follow ISO-11179 naming rules or at least be
consistent? Look at @.date_shipped is "<adj><noun>" while @.invoice_id
is "<noun><adj>" instead.
6) When I see procedure named "Process_Line_Item_Shipping' I worry
that you are going thru each item in an order, one at a time. SQL is a
set-oriented language and you should be working with a sub-set of
items. No loops. No Cursors.
My guess, based on no DDL, is that you need a table for the Orders, for
the Order Details, Shipments and working table of returns. The
returns will be used to update the Order Details with return
quantities and shipping info (perhaps the Orders will need changes).
I have done this in one UPDATE statement for some fairly simple
business rules. The trick was a detail table keyed on (order_nbr, sku,
ship_status, ship_date). Reports are done off of VIEWs (what
percentage of Lawn Gnomes are returned? in how many days? ) and you
needed to watch constraints (you cannot return more than you bought).

Cursor loop is broken

In a stored procedure (SP1) I am looping through a cursor with records
from Table1. Each record in the cursor is inserted into Table2.
Insert trigger on Table2 is inserting the record into Table3 (in
another DB).
In the insert trigger on Table3, a series on checks are done on the
inserted record and in case of an error, an email is sent and the
trigger returns.
This break the cursorloop in SP1 and the rest of the records in the
cursor is not treated.
How do I make sure that all records are treated?
This is the flow:
-- SP1 --
DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1
OPEN csrListe
-- The first record is treated here...
:
-- Treat the rest
WHILE @.@.FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM csrListe INTO @.feltet
IF @.@.FETCH_STATUS = 0 BEGIN
blah-blah-blah
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES
(@.Ordrenr, @.Status, @.Dato, @.Result)
END
END
CLOSE csrListe
DEALLOCATE csrListe
-- Table2_ITrig --
INSERT INTO db2.dbo.Table3 SELECT * FROM inserted
-- Table3_ITrig --
SET NOCOUNT ON
DECLARE @.STATUS int
DECLARE @.DATOTID smalldatetime
DECLARE @.RESULT int
SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
SELECT @.STATUS = (SELECT STATUS FROM INSERTED)
SELECT @.DATOTID = (SELECT DATO FROM INSERTED)
SELECT @.RESULT = (SELECT RESULT FROM INSERTED)
SET XACT_ABORT ON
IF NOT @.STATUS IN (1,2,3,4,5,6,9,10) BEGIN
SELECT @.ERR = 'ERROR - unknown status = ' + CAST(@.ORDRENR as char(4))
UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@.ORDRENR
EXEC @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'me@.here.dk',
@.TO = N'you@.here.dk',
@.priority = N'HIGH',
@.subject = N'Status error',
@.message = N'Status error',
@.type = N'text/plain',
@.server = 'smtp.here.dk'
RETURN
END
The mail is send so it must be the final RETURN that is causing the
trouble.sblar wrote:
> In a stored procedure (SP1) I am looping through a cursor with records
> from Table1. Each record in the cursor is inserted into Table2.
> Insert trigger on Table2 is inserting the record into Table3 (in
> another DB).
> In the insert trigger on Table3, a series on checks are done on the
> inserted record and in case of an error, an email is sent and the
> trigger returns.
> This break the cursorloop in SP1 and the rest of the records in the
> cursor is not treated.
> How do I make sure that all records are treated?
> This is the flow:
> -- SP1 --
> DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1
> OPEN csrListe
> -- The first record is treated here...
> :
> -- Treat the rest
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> FETCH NEXT FROM csrListe INTO @.feltet
> IF @.@.FETCH_STATUS = 0 BEGIN
> blah-blah-blah
> INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES
> (@.Ordrenr, @.Status, @.Dato, @.Result)
> END
> END
> CLOSE csrListe
> DEALLOCATE csrListe
> -- Table2_ITrig --
> INSERT INTO db2.dbo.Table3 SELECT * FROM inserted
> -- Table3_ITrig --
> SET NOCOUNT ON
> DECLARE @.STATUS int
> DECLARE @.DATOTID smalldatetime
> DECLARE @.RESULT int
>
> SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
> SELECT @.STATUS = (SELECT STATUS FROM INSERTED)
> SELECT @.DATOTID = (SELECT DATO FROM INSERTED)
> SELECT @.RESULT = (SELECT RESULT FROM INSERTED)
> SET XACT_ABORT ON
>
> IF NOT @.STATUS IN (1,2,3,4,5,6,9,10) BEGIN
> SELECT @.ERR = 'ERROR - unknown status = ' + CAST(@.ORDRENR as char(4))
> UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@.ORDRENR
> EXEC @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'me@.here.dk',
> @.TO = N'you@.here.dk',
> @.priority = N'HIGH',
> @.subject = N'Status error',
> @.message = N'Status error',
> @.type = N'text/plain',
> @.server = 'smtp.here.dk'
> RETURN
> END
> The mail is send so it must be the final RETURN that is causing the
> trouble.
Do not use cursors in triggers. Doubly important, do not send email
from a trigger.
See:
http://groups.google.co.uk/group/mi...b273c91159441f2
David Portas
SQL Server MVP
--|||Thanks David, I understand your point about email and will consider
another path, but the problem here doesn't seem to be the email which
is sent ok. The cursor is in the sp not the trigger.
/S=F8ren|||"Sren Larsen" <sblar1@.surfpost.dk> wrote in message
news:1136633487.767264.30440@.g49g2000cwa.googlegroups.com...
Thanks David, I understand your point about email and will consider
another path, but the problem here doesn't seem to be the email which
is sent ok. The cursor is in the sp not the trigger.
/Sren
First, I suggest you get rid of the cursor. Use an INSERT ... SELECT
statement instead:
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat)
SELECT Ordrenr, Status, Dato, Resultat
FROM ... ?
I expect there was more processing that you left out of your post but I can
only suggest a solution for what you posted.
Secondly, you need to modify your trigger to handle multiple rows properly.
Example:
/* This will FAIL if more than one row is inserted/updated */
SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
If you don't send emails from the trigger then you won't need to assign the
column values to variables.
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> First, I suggest you get rid of the cursor. Use an INSERT ... SELECT
> statement instead:
> INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat)
> SELECT Ordrenr, Status, Dato, Resultat
> FROM ... ?
> I expect there was more processing that you left out of your post but I
> can only suggest a solution for what you posted.
> Secondly, you need to modify your trigger to handle multiple rows
> properly.
> Example:
> /* This will FAIL if more than one row is inserted/updated */
> SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
> If you don't send emails from the trigger then you won't need to assign
> the column values to variables.
Hey, I've already said all of that! (Except the point of not sending
mail from a trigger.) But I said it in a different newsgroup, as Sren
posted the message independently to two newsgroups. With the result
that I and David waste our time to say the same thing.
Please do not do that again!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks David.
The cursor is in the sp not the trigger. I understand your point about
email though, and I will consider another path. But, this doesn't seem
to be the problem here as the email is sent.|||Sren Larsen (sblar1_thisisnotme@.surfpost.dk) writes:
> Anyway, thanks for your answers. I'm aware of the problem if my trigger
> receives multiple rows, which it dont cause its only called from my SP
> with the cursor loop. I would however very much like to avoid this way
> of processing but I can't see how if I want to do some processing on
> each row inserted. For example:
> if Status = 1
> set @.result = 2
> if Status = 2
> set @.result = 3
> update sometable set Status = @.Status where number = select number from
> inserted
> Any suggestions?
Without knowledge of the business problem, it's difficult to suggest a
complete solution. But for the particular problem you appear to illustrate
you can use the CASE expression:
UPDATE a
SET result = CASE b.status
WHEN 1 THEN 'OK'
WHEN 2 THEN 'OK with warnings'
WHEN 3 THEN 'Failed'
ELSE 'Complete disaster'
END
FROM a
JOIN b ON a.col = b.col
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> skrev i en meddelelse
news:Xns9745A1206A4E9Yazorman@.127.0.0.1...
> Without knowledge of the business problem, it's difficult to suggest a
> complete solution. But for the particular problem you appear to illustrate
> you can use the CASE expression:
> UPDATE a
> SET result = CASE b.status
> WHEN 1 THEN 'OK'
> WHEN 2 THEN 'OK with warnings'
> WHEN 3 THEN 'Failed'
> ELSE 'Complete disaster'
> END
> FROM a
> JOIN b ON a.col = b.col
>
Aha - thats neat. Does this mean that inserted are traversed and a.result
would be updated for every row in inserted and can it be done more than once
like this?
UPDATE a
SET result = CASE inserted.status
WHEN 1 THEN 'OK'
WHEN 2 THEN 'OK with warnings'
WHEN 3 THEN 'Failed'
ELSE 'Complete disaster'
END
FROM a
JOIN inserted ON a.col = inserted.col
UPDATE c
SET somefield = CASE inserted.someotherfield
WHEN 1 THEN 11
WHEN 2 THEN 12
WHEN 3 THEN 13
ELSE 0
END
FROM c
JOIN inserted ON c.col = inserted.col
Will this do an update of both a and c based on inserted rows?
/Sren|||I almost forgot; why is the cursorloop broken in the first place? There was
no error in trigger, unless RETURN is considered an error!
/Sren|||Sren Larsen (sblar1_thisisnotme@.surfpost.dk) writes:
> Aha - thats neat. Does this mean that inserted are traversed and
> a.result would be updated for every row in inserted and can it be done
> more than once like this?
> UPDATE a
> SET result = CASE inserted.status
> WHEN 1 THEN 'OK'
> WHEN 2 THEN 'OK with warnings'
> WHEN 3 THEN 'Failed'
> ELSE 'Complete disaster'
> END
> FROM a
> JOIN inserted ON a.col = inserted.col
> UPDATE c
> SET somefield = CASE inserted.someotherfield
> WHEN 1 THEN 11
> WHEN 2 THEN 12
> WHEN 3 THEN 13
> ELSE 0
> END
> FROM c
> JOIN inserted ON c.col = inserted.col
> Will this do an update of both a and c based on inserted rows?
Yes. CASE is extremely powerful when working with set-based operations.
The above is a simplifed form. The more general form is like this:
CASE WHEN somecolumn IN (1, 2, 3) THEN 'This'
WHEN othercolumn = 'A' AND somecolumn = 34 THEN 'That'
WHEN somecolumn < 0 THEN CASE col WHEN 1 THEN 'J' ELSE 'N' END
END
So you can test for more general conditions, and you can nest CASE.
The conditions are always evaluated top-down, and evaluation stops
as soon one matches. If no condition is true, and there is on ELSE,
the value is NULL.
Important to understand is that CASE is an *expression*, and as a
an expression it always return the same data type. If you try:
CASE something WHEN 1 THEN 0 ELSE 'x' END
this will fail, when something is not 1, because this CASE expression
returns an integer value, as integer is higher than char in the
data-type precedence in SQL Server.

> I almost forgot; why is the cursorloop broken in the first place? There
> was no error in trigger, unless RETURN is considered an error!
Was there a ROLLBACK? A rollback in a trigge aborts execution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cursor loop is broken

In a stored procedure (SP1) I am looping through a cursor with records
from Table1. Each record in the cursor is inserted into Table2.
Insert trigger on Table2 is inserting the record into Table3 (in
another DB).
In the insert trigger on Table3, a series of checks are done on the
inserted record and in case of an error, an email is sent and the
trigger returns.
This break the cursorloop in SP1 and the rest of the records in the
cursor is not treated.
How do I make sure that all records are treated?

This is the flow:

-- SP1 ----------
DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1

OPEN csrListe

-- The first record is treated here...
:

-- Treat the rest
WHILE @.@.FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM csrListe INTO @.feltet
IF @.@.FETCH_STATUS = 0 BEGIN
blah-blah-blah
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES

(@.Ordrenr, @.Status, @.Dato, @.Result)
END
END
CLOSE csrListe
DEALLOCATE csrListe

-- Table2_ITrig ----------
INSERT INTO db2.dbo.Table3 SELECT * FROM inserted

-- Table3_ITrig ----------
SET NOCOUNT ON

DECLARE @.STATUS int
DECLARE @.DATOTID smalldatetime
DECLARE @.RESULT int

SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
SELECT @.STATUS = (SELECT STATUS FROM INSERTED)
SELECT @.DATOTID = (SELECT DATO FROM INSERTED)
SELECT @.RESULT = (SELECT RESULT FROM INSERTED)

SET XACT_ABORT ON

IF NOT @.STATUS IN (1,2,3,4,5,6,9,10) BEGIN
SELECT @.ERR = 'ERROR - unknown status = ' + CAST(@.ORDRENR as char(4))

UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@.ORDRENR

EXEC @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N...@.here.dk',
@.TO = N...@.here.dk',
@.priority = N'HIGH',
@.subject = N'Status error',
@.message = N'Status error',
@.type = N'text/plain',
@.server = 'smtp.here.dk'

RETURN
END

The mail is send so it must be the final RETURN that is causing the
trouble.Sren Larsen (sblar1@.surfpost.dk) writes:
> In a stored procedure (SP1) I am looping through a cursor with records
> from Table1. Each record in the cursor is inserted into Table2.
> Insert trigger on Table2 is inserting the record into Table3 (in
> another DB).
> In the insert trigger on Table3, a series of checks are done on the
> inserted record and in case of an error, an email is sent and the
> trigger returns.
> This break the cursorloop in SP1 and the rest of the records in the
> cursor is not treated.
> How do I make sure that all records are treated?

An error in a trigger aborts the batch. Thus, in SQL 2000, there is no
way to handle the situation in T-SQL, you would need to have a client
program that reacts on the error, and restarts the loop, but leaving
out the row that causes problems. In SQL 2005, you could use TRY-CATCH
to handle the situation.

But why are you running a loop in the first place? The normal procedure
to insert rows from one table to another is to say:

INSERT tbl2 (...)
SELECT ...
FROM tbl1

Of course, this would mean that if any of the rows are erroneous, then
all rows inserted would be rolled back by the trigger on Table3. But this
can be handled in different ways. (But exactly how, it's difficult to
say as I don't know the business requirements.)

The reason you should insert all, and not run a cursor, is that performance
for a cursor can be disastrous. If we are talking less than < 100 rows, it's
may be not that big deal. If we are talking 10000 rows, it can mean a
difference in processing time of 30 minutes instead of 30 seconds.

> -- Table3_ITrig ----------
> SET NOCOUNT ON
>
> DECLARE @.STATUS int
> DECLARE @.DATOTID smalldatetime
> DECLARE @.RESULT int
>
> SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
> SELECT @.STATUS = (SELECT STATUS FROM INSERTED)
> SELECT @.DATOTID = (SELECT DATO FROM INSERTED)
> SELECT @.RESULT = (SELECT RESULT FROM INSERTED)

This trigger is poorly implemented. A trigger fires once per statement,
and must be able to handle multi-row operations.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cursor loop

Hello,

I've created a stored procedure that loops through a cursor, with the
following example code:

DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)

OPEN curPeriod

WHILE @.@.FETCH_STATUS=0

BEGIN

FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod

SET @.strTekst = CONVERT(varchar, @.intPeriod)

PRINT @.strTekst

END

CLOSE curPeriod
DEALLOCATE curPeriod

The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens. It seems that
the Cursor stays at the last record or that @.@.Fetch_status isn't 0. But
I Deallocate the cursor. I have to restart the SQL Server before the
stored procedure can be used again.

Does anyone know why the loop can execute only 1 time?

Greetings,
Chris

*** Sent via Developersdex http://www.developersdex.com ***Chris Zopers wrote:

Quote:

Originally Posted by

Hello,
>
I've created a stored procedure that loops through a cursor, with the
following example code:
>
DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)
>
OPEN curPeriod
>
WHILE @.@.FETCH_STATUS=0
>
BEGIN
>
FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod
>
SET @.strTekst = CONVERT(varchar, @.intPeriod)
>
PRINT @.strTekst
>
END
>
CLOSE curPeriod
DEALLOCATE curPeriod
>
The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens. It seems that
the Cursor stays at the last record or that @.@.Fetch_status isn't 0. But
I Deallocate the cursor. I have to restart the SQL Server before the
stored procedure can be used again.
>
Does anyone know why the loop can execute only 1 time?
>
Greetings,
Chris

Hi Chris,

When you say you have to restart SQL Server before it can be used
again, do you mean the server or just Query Analyser?

I suspect the issue you're having is when you next enter the stored
procedure, the FETCH_STATUS is still as it was at the end of the last
time through the loop - non-zero, and so the loop isn't executed.

I've never seen a good pattern for doing cursors that doesn't look
messy (Since most practicioners tend to try to avoid them in the first
place, no-one spends much time tidying them up).

Normal pattern for me is:

declare cursor x for select ...
declare <variables to hold the columns>

open x

fetch next from x into <list of variables>
while @.@.FETCH_STATUS = 0
begin
--Do stuff

fetch next from x into <list of variables>
end

close x
deallocate x

in short, I've never found a way to do it which doesn't have to have
the same fetch statement in two places.

Damien

PS - Usual recommendation would be to have a list of columns, rather
than select * from... However, there is disagreement over this
particular recommendation, I'd suggest you search the archives for some
lively debate on the matter.|||Chris Zopers (test123test12@.12move.nl) writes:

Quote:

Originally Posted by

I've created a stored procedure that loops through a cursor, with the
following example code:
>
DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)
>
OPEN curPeriod
>
WHILE @.@.FETCH_STATUS=0
BEGIN
FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod
SET @.strTekst = CONVERT(varchar, @.intPeriod)
PRINT @.strTekst
END
>
CLOSE curPeriod
DEALLOCATE curPeriod
>
The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens.

This is because you check @.@.fetch_status before you fetch. This is how
you should write cursor loop:

DECLARE cur INSENSITIVE CURSOR FOR
SELECT col1, col2 FROM tbl

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @.par1, @.par2
IF @.@.fetch_status <0
BREAK

-- Do stuff
END

DEALLOCATE cur

Beyond the structure of the cursor loop, please notice:

1) Never use SELECT * with cursor declarations. Add a column to the
table, and your code breaks. That's bad.

2) The cursor must be declared as INSENSITIVE or STATIC (the latter
can be combined with LOCAL, the first cannot). With no specification
you get a dynamic cursor, which is rarely what you want. But dynamic
cursors can have bad impact on both performance and funcion.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cursor isn''t being created.

ElementTypeDep_Cursor seems is not being created in this stored procedure:

Code Snippet

ALTER procedure spCopyTemplateElementTypesToIssues

@.TemplateRecno integer,

@.ProjRecNo integer,

@.IssueRecNo integer

as

declare @.ElementTypeRecno integer

declare @.ElementTypeDepRecno integer

declare @.ProjTypeRecno integer

declare @.PreElementRecNo integer

declare @.PostElementRecno integer

declare @.Count integer

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.count = count (*)

from tblElementCPO

where ProjRecno = @.ProjRecNo

and IssueRecno = @.IssueRecno

and TemplateRecno = @.TemplateRecno

and ElementTypeRecno = @.ElementTypeRecNo

if @.Count = 0

begin

insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno,

ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase,

TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson)

values (0, @.ElementTypeRecno, @.IssueRecno,

@.ProjRecno, 5,0,6,99,

99,@.TemplateRecno, getdate(), current_user)

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

if @.Count > 0 then

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementRecNo, @.PostElementRecno

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo,

PostElementRecno, ChangeDate, ChangePerson)

values (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, getdate(), current_user)

end

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementRecNo, @.PostElementRecno

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

called by

Code Snippet

Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev)

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "spCopyTemplateElementTypesToIssues"

cmd.CommandType = CommandType.StoredProcedure

Dim spmTemplateRecNo As SqlParameter = _

cmd.Parameters.Add("@.TemplateRecNo", SqlDbType.Int)

spmTemplateRecNo.Value = _

Me.cbTemplate.SelectedValue

Dim spmProjRecNo As SqlParameter = _

cmd.Parameters.Add("@.ProjRecNo", SqlDbType.Int)

spmProjRecNo.Value = _

Me.cbProject.SelectedValue

Dim spmIssueRecNo As SqlParameter = _

cmd.Parameters.Add("@.IssueRecNo", SqlDbType.Int)

spmIssueRecNo.Value = _

Me.cbIssue.SelectedValue

cmd.Connection = cnQI02414

cnQI02414.Open()

reader = cmd.ExecuteReader()

' Data is accessible through the DataReader object here.

cnQI02414.Close()

What should I be looking for?

You're not returning anything from the stored procedure. i.e. there is no "select" after you're done inserting. So, your reader will be empty.

To verify if your sproc actually runs, try returning all input and output parameters as the last "select" statement.

Cursor isn''t being created.

ElementTypeDep_Cursor seems is not being created in this stored procedure:

Code Snippet

ALTER procedure spCopyTemplateElementTypesToIssues

@.TemplateRecno integer,

@.ProjRecNo integer,

@.IssueRecNo integer

as

declare @.ElementTypeRecno integer

declare @.ElementTypeDepRecno integer

declare @.ProjTypeRecno integer

declare @.PreElementRecNo integer

declare @.PostElementRecno integer

declare @.Count integer

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.count = count (*)

from tblElementCPO

where ProjRecno = @.ProjRecNo

and IssueRecno = @.IssueRecno

and TemplateRecno = @.TemplateRecno

and ElementTypeRecno = @.ElementTypeRecNo

if @.Count = 0

begin

insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno,

ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase,

TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson)

values (0, @.ElementTypeRecno, @.IssueRecno,

@.ProjRecno, 5,0,6,99,

99,@.TemplateRecno, getdate(), current_user)

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

if @.Count > 0 then

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementRecNo, @.PostElementRecno

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo,

PostElementRecno, ChangeDate, ChangePerson)

values (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, getdate(), current_user)

end

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementRecNo, @.PostElementRecno

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

called by

Code Snippet

Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev)

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "spCopyTemplateElementTypesToIssues"

cmd.CommandType = CommandType.StoredProcedure

Dim spmTemplateRecNo As SqlParameter = _

cmd.Parameters.Add("@.TemplateRecNo", SqlDbType.Int)

spmTemplateRecNo.Value = _

Me.cbTemplate.SelectedValue

Dim spmProjRecNo As SqlParameter = _

cmd.Parameters.Add("@.ProjRecNo", SqlDbType.Int)

spmProjRecNo.Value = _

Me.cbProject.SelectedValue

Dim spmIssueRecNo As SqlParameter = _

cmd.Parameters.Add("@.IssueRecNo", SqlDbType.Int)

spmIssueRecNo.Value = _

Me.cbIssue.SelectedValue

cmd.Connection = cnQI02414

cnQI02414.Open()

reader = cmd.ExecuteReader()

' Data is accessible through the DataReader object here.

cnQI02414.Close()

What should I be looking for?

You're not returning anything from the stored procedure. i.e. there is no "select" after you're done inserting. So, your reader will be empty.

To verify if your sproc actually runs, try returning all input and output parameters as the last "select" statement.

cursor in Stored Procedures

any one Explain me the details about Cursors in Stored Procedures and is there any other way to call views in stored Procedure

Quote:

Originally Posted by hariharanmca

any one Explain me the details about Cursors in Stored Procedures and is there any other way to call views in stored Procedure

Its very Urgent, If i simply write select * from vw_viewName then its giving Error like

Error:
=================================
Server: Msg 8624, Level 16, State 16, Procedure sp_Add_Remove_ItemQtyForNonChargeOrder , Line 18
Internal SQL Server error.

Line 18 is select * from vw_viewName|||See problem description here.

Cursor in stored procedure

I'm trying to do something like that:
CREATE PROCEDURE [dbo].[Test_Proc] (@.SearchText1 nvarchar(10), @.SearchText2
nvarchar(100), @.SearchText3 nvarchar(100)) AS
DECLARE @.SQLString NVARCHAR(4000)
DECLARE @.SQLSelect NVARCHAR(3000)
DECLARE @.SQLWhere NVARCHAR(1000)
SET @.SQLSelect = 'SELECT dbo.View_Clients.Client_id,
dbo.Contacts.Contact_id from dbo.View_Clients LEFT OUTER JOIN dbo.Contacts
ON dbo.View_Clients.Contact_ID = dbo.Contacts.Contact_id'
if @.SearchText1 = 'Client'
SET @.SQLWhere = ' WHERE dbo.View_Clients.Name_Fr = ?'
--Here I would use SearchText2 as parameter 1
if @.SearchText1 = 'Contact'
SET @.SQLWhere = ' WHERE dbo.Contacts.FName = ? and dbo.Contacts.LName
= ?'
--Here I would use SearchText2 as parameter 1 and SearchText3 as
parameter 2
Set @.SQLString = @.SQLSelect + @.SQLWhere
...
I want to Declare a cursor with this SQL String and supply the good
parameters. Is that possible?
If so, how? i'm a little lost when it comes to cursorsI think you are tying yourself in unnecessary knots, you appear to be
able to do this with just some static SQL:
SELECT dbo.View_Clients.Client_id,
dbo.Contacts.Contact_id from dbo.View_Clients LEFT OUTER JOIN
dbo.Contacts
ON dbo.View_Clients.Contact_ID = dbo.Contacts.Contact_id
WHERE (@.SearchText1 = 'Client' AND dbo.View_Clients.Name_Fr =
@.SearchText2)
OR (@.SearchText1 = 'Contact' AND dbo.Contacts.FName = @.SearchText2 AND
dbo.Contacts.LName = @.SearchText2)
Cheers
Will
P.S stay lost when it comes to cursors - it's safer

Cursor in SQL Server

Hi,
I've discovered the stored procedures lately, and moving almost all my requests to use this.

I've a question about declaring a cursor.

For example :
DECLARE @.strSQL varchar(255)
SELECT @.strSQL = 'SELECT au_lname FROM authors'

I want to declare the cursor like this
DECLARE csrAuthor
FOR @.strSQL
READ ONLY

I'm always getting an error. Is there a solution to make a "dynamic" cursor.

Thanks
FrankTry putting your DECLARE CURSOR statement inside an execute statement. Like this:

EXEC ('DECLARE Oprid_Cursor CURSOR FOR ' + @.VAR1)

Then you can OPEN the cursor etc.

Hope this helps.|||It might be possible to do something like this also but I haven't tried it:

DECLARE csrAuthor
FOR exec sp_executesql @.strSQL
READ ONLYsql

Cursor from stored procedure?

I have a situation where I need to create a cursor based on a stored
procedure, so basically something like this...
DECLARE @.proc varchar(250)
Set @.proc = '[' + @.DBServerName + '].thedb.dbo.spGetBCXDiags ' +
Cast(@.LastUpdateDate As Varchar(7))
DECLARE the_cursor CURSOR FOR @.proc
SQL Server expects a 'Select' statement instead of an SP name and therefore
is giving me an error. I am building the stored procedure name to include
the server name because I am linking with the @.DBServerName server before
this statement (with sp_addlinkedserver).
I tried just issuing a built 'Select' statement against the linked server,
but again, the 'Select' statement had to be built as a string to include the
linked server name. Is there a Transact-SQL version of eval() or something
like that to tell it to treat the string version of the 'Select' statement a
s
if it were a 'Select' statement that was just typed in (not built as a
string)? Or, is there a way to build a cursor by defining it to be a stored
procedure instead of a 'Select' statement? Seems as though I'm in a catch-2
2
unless I'm missing something.
Thanks,
ToddI dont think you can define based directly on a SP.
Instead, store the results in a Temp table and base your cursor on the temp
table.
Gopi
"Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
news:AB4C0DE3-DC07-4BFB-99AB-EB10320B37FF@.microsoft.com...
>I have a situation where I need to create a cursor based on a stored
> procedure, so basically something like this...
> DECLARE @.proc varchar(250)
> Set @.proc = '[' + @.DBServerName + '].thedb.dbo.spGetBCXDiags ' +
> Cast(@.LastUpdateDate As Varchar(7))
> DECLARE the_cursor CURSOR FOR @.proc
> SQL Server expects a 'Select' statement instead of an SP name and
> therefore
> is giving me an error. I am building the stored procedure name to include
> the server name because I am linking with the @.DBServerName server before
> this statement (with sp_addlinkedserver).
> I tried just issuing a built 'Select' statement against the linked server,
> but again, the 'Select' statement had to be built as a string to include
> the
> linked server name. Is there a Transact-SQL version of eval() or
> something
> like that to tell it to treat the string version of the 'Select' statement
> as
> if it were a 'Select' statement that was just typed in (not built as a
> string)? Or, is there a way to build a cursor by defining it to be a
> stored
> procedure instead of a 'Select' statement? Seems as though I'm in a
> catch-22
> unless I'm missing something.
> Thanks,
> Todd|||The problem is that I have to build a string that represents the remote
stored procedure or string that represents a 'Select' statement. I must
build the string representations in order to dynamically include the remotel
y
linked server name into the equation. If I build a string representation of
an SP SQL Server will run it, but I have no way of seeing the records it
returns that I know of (can't build a cursor from an SP). If I build a
string representation of a 'Select' statement that includes the remote serve
r
name, how the heck do you tell SQL Server to run the 'Select'? It's just a
string value to SQL Server.
I guess my other options are to have a separate SP for each server I'm going
to link to or to put the code on each SQL Server machine and link back up
with the main server. That way the linked server name will be constant. I
didn't want to do either of these unless, of course, the other way just won'
t
work.
"rgn" wrote:

> I dont think you can define based directly on a SP.
> Instead, store the results in a Temp table and base your cursor on the tem
p
> table.
> Gopi
> "Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
> news:AB4C0DE3-DC07-4BFB-99AB-EB10320B37FF@.microsoft.com...
>
>sql

Cursor for a result set returned by a stored proc

Hi all,

i got a question. I have a stored procedure sp1 calling another stored procedure sp2 using Exec sp2 cmd.

sp2 returns 2 rows in the result set.
Now I want to loop thru those 2 rows and get access to its column values
from sp1. How can this be acheived?

thanks for your help

omair-I came across this after reading your post:

One legitimate use for temp tables is to use them to pass recordsets from a nested stored procedure to a calling stored procedure. This is the only way to pass recordsets from one stored procedure to another. When you do this, follow the other tips on this Web site to maximize temp table performance. [6.5, 7.0, 2000] Updated 3-6-2006

Hope it helps.

Hannah

CURSOR FETCH STATEMENT IS HANGING

Hi All,
I have a stored proc that uses a cursor to iterate over the join resultset
of 5 tables. After fetching roughly 12,000 records and running for about 30
minutes, the next FETCH statement inside the WHILE loop hangs. This happens
consisently. I tried to do a commit / checkpoint after every 10,000 records,
but the problem persists.
Can any one please provide some thoughts about why this could be happening?
ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
development database.
Thanks,
RajeshWhat are you trying to accomplish?
Why are you using cursors?
What kind of cursor are you using?
Any research about a set-based solution?
Can we see some code?
AMB
"rajeshlh" wrote:

> Hi All,
> I have a stored proc that uses a cursor to iterate over the join resultse
t
> of 5 tables. After fetching roughly 12,000 records and running for about 3
0
> minutes, the next FETCH statement inside the WHILE loop hangs. This happen
s
> consisently. I tried to do a commit / checkpoint after every 10,000 record
s,
> but the problem persists.
> Can any one please provide some thoughts about why this could be happening
?
> ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
> development database.
> Thanks,
> Rajesh
>|||I have to process a set of records obtained from the join of 5 tables. For
each record in the resultset, i need to insert/update into 5-6 tables, plus
i
need to write to a log table the key for each inserted row or updated row.
Following is the code.
The FETCH FROM Subscription_Cursor fails inside the While Loop after some
11,000 records. Since iam writing debug statements to another table, i can
say that after processing 11000 records inside the cursor, the FETCH
statement freezes.
I did think about SET-based approach, but the processing logic forces me to
use a cursor. The Job is run nightly and processes about 100,000+ records.
Following is the SP Code.
CREATE PROCEDURE TEST_SP
AS
--Variables to store Name record values
DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
@.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
@.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
varchar(11),@.Fax_Number varchar(11)
--Variables to store Product record values
DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
@.Current_Email_Address varchar(50),@.Email_Password varchar(50)
--Variables to store Order record values
DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
varchar(2),
@.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
datetime,@.Order_Entry_Type varchar(5)
--Variables to store Demographic record values
DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
varchar(1024)
--variales to store newly created reg_visitor_id,account_id and order_id -
for subscriptions not existing in Elogic Reg DB
DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
integer,@.Address_Id integer
--variables to store values present in staging tables - For subscriptions
already existing in Elogic Reg DB
DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
--variables used for logging
DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
@.Log_Summary_Id integer,@.Source_File_Name varchar(100)
--Variables to store Donor's data
DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
@.Donor_State_Prov varchar(2),@.Donor_Country_Code
varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
@.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
--Variables to store CDS to Elogic Converted values
DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
--Misc variables
DECLARE @.SP_NAME varchar(50),@.Ret integer
DECLARE @.Reg_Visitor_Product varchar(100)
DECLARE @.Exec_Start_Time datetime
DECLARE @.Promo_Code_Pos2 varchar(1)
DECLARE @.Delivery_Type varchar(1)
DECLARE @.Cnt int
--Cursor to subscriptions stored in the staging tables
DECLARE Subscriptions_Cursor CURSOR FOR
SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_name
,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_na
me,nr.job_title,nr.company_name,
nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code,nr
.telephone,nr.fax_number,
pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.num
_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.ord
er_net_value,ord.source_code,ord.medium_code,ord.document_key,
ord.setcode,ord.orig_start_issue,ord.order_entry_type,
dr.version_number,dr.segment_number,dr.demo_data,
delivery_type
FROM cds_name_record nr
JOIN cds_product_record pr
ON nr.account_number=pr.account_number
AND nr.publisher_code=pr.publisher_code
AND nr.mag_code=pr.mag_code
JOIN cds_order_record ord
ON ord.account_number=pr.account_number
AND ord.publisher_code=pr.publisher_code
AND ord.mag_code=pr.mag_code
JOIN cds_demographic_record dr
ON dr.account_number=pr.account_number
AND dr.publisher_code=pr.publisher_code
AND dr.mag_code = pr.mag_code
JOIN publication_subscription ps
ON ps.external_multi_mag_code = pr.publisher_code
AND ps.code = pr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address
AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
Non-Gift and Donee Orders
AND ord.order_status='B' --Get only Base Orders
AND dr.demo_status = 'B' --Get only Base Demo Records
--AND nr.account_number <>'0010115103'
ORDER BY CAST(nr.account_number AS int)
--cursor to the reg_feed_detail_log table , used for populating the
reg_feed_summary_log table
DECLARE Summary_Cursor CURSOR FOR
SELECT
publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*) a
s
summary_count
FROM reg_feed_detail_log
GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
SET @.Exec_Start_Time = CURRENT_TIMESTAMP
OPEN Subscriptions_Cursor
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
SET @.Cnt =1
PRINT 'start'
WHILE @.@.FETCH_STATUS = 0
BEGIN --B1
INSERT INTO Demo_data values ('inside while loop',null,null)
INSERT INTO debug_table
(eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code,m
ag_code,order_status,set_code,delivery_t
ype,seq)
VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_p
ublication_id,@.Account_Number
,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
@.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
NULL,@.Donor_Fax_Number = NULL,
@.Donor_Account_number = NULL
SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
SELECT @.Promo_Code_Pos2 = NULL
IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
BEGIN --B2
SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
nr.postal_code,@.Donor_Company_Name = nr.company_name,
@.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
nr.address_line_2,@.Donor_City = nr.city,
@.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
nr.fax_number
FROM cds_name_record nr
JOIN cds_order_record ord
ON ord.account_number=nr.account_number
AND ord.publisher_code=nr.publisher_code
AND ord.mag_code=nr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address Name record
AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Order
or Non-Subscibing Donor Order
AND nr.publisher_code = @.Publisher_Code
AND nr.mag_code = @.Mag_Code
AND ord.order_number = @.Order_Number
-- Note : Donor and Donee will have different account numbers, but same
publisher code, mag code and order number
END --END B2
SET @.eLogic_Account_Status = CASE
WHEN @.Service_Status = 'A' THEN 'A'
WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
WHEN @.Service_Status = 'C' THEN 'X'
WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
END
SET @.eLogic_Pay_Type = CASE
WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
ELSE ''
END
SET @.eLogic_Auto_Renew= CASE
WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN 1
ELSE 0
END
SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
SET @.eLogic_Account_Type=CASE
WHEN @.Source_Code = 'CC' THEN
CASE
WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
ELSE 'CONTROLLED'
END
WHEN @.Source_Code = 'CA' THEN 'COMP'
ELSE 'PAID'
END
SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
FROM account A
JOIN publication_subscription PS
ON A.pub_code = PS.code
WHERE A.account_number = @.Account_Number
AND PS.external_multi_mag_code = @.Publisher_Code
--If a reg_visitor is not determined in the Staging tables or by looking up
the Account table , a new reg_visitor
--is created
IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
BEGIN --B3
--No Reg_Visitor corresponding to the mag subscription - mag subscription
generated at CDS
--Create a new reg_visitor and associated rows in Account,Account_Order
and visitor_demographic
SELECT @.Reg_Visitor_Product = master_brand
FROM publication
WHERE external_multi_mag_code=@.Publisher_Code
--insert into reg_visior table
EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTPUT,
@.p_common_name = @.Common_Name,
@.p_company_name = @.Company_Name,
@.p_email = @.Current_Email_Address,
@.p_encrypted_password = @.Email_Password,
@.p_given_name = NULL,
@.p_login_id = @.Email_User_Name,
@.p_merged_visitor_id = NULL,
@.p_middle_initial = NULL,
@.p_middle_name = NULL,
@.p_name_suffix = NULL,
@.p_password = @.Email_Password,
@.p_product = @.Reg_Visitor_Product,
@.p_subproduct = NULL,
@.p_professional_title = @.Job_Title,
@.p_record_status = 1,
@.p_registration_level = 1,
@.p_salutation = NULL,
@.p_sur_name = NULL,
@.p_zip = NULL,
@.p_country = 'TESTDTS'
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAME)
--ROLLBACK TRAN T1
RETURN -2
END
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'reg_visitor',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = NULL,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--insert into address table
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Company_Name,
@.p_address_line_1 = @.Address_Line_1,
@.p_address_line_2 = @.Address_Line_2,
@.p_city = @.City,
@.p_postal_code = @.Postal_Code,
@.p_state_prov = @.State_Prov,
@.p_country_code = @.Country_Code,
@.p_phone = @.Telephone,
@.p_fax = @.Fax_Number,
@.p_address_type = 0 --shipping
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
BEGIN
--Insert the corresponding Donor Address
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Donor_Company_Name,
@.p_address_line_1 = @.Donor_Address_Line_1,
@.p_address_line_2 = @.Donor_Address_Line_2,
@.p_city = @.Donor_City,
@.p_postal_code = @.Donor_Postal_Code,
@.p_state_prov = @.Donor_State_Prov,
@.p_country_code = @.Donor_Country_Code,
@.p_phone = @.Donor_Telephone,
@.p_fax = @.Donor_Fax_Number,
@.p_address_type = 1 --Billing
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into address for donor')
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into detail log for donor
address')
END
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -3
END
--Insert into Account table
EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id OUTPUT,
@.p_account_number = @.Account_Number,
@.p_pub_code = @.Mag_Code,
@.p_account_type = @.eLogic_Account_type ,
@.p_status = @.eLogic_Account_Status,
@.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
Order, NULL will be inserted for Supp_Account_Number
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -4
END
INSERT INTO Demo_data values ('completed inserting into account
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed inserting into detail log for
account table',@.Reg_Visitor_id,@.Account_Number)
--insert into account_order table
EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id,
@.p_order_id = @.Order_Id OUTPUT,
@.p_term = @.Order_Term,
@.p_term_unit = NULL,
@.p_pay_type = @.eLogic_Pay_Type,
@.p_net_amt = @.Order_Net_Value,
@.p_quantity = @.Num_Copies,
@.p_vendor_order_number = @.Order_Number,
@.p_promo_response_key = @.Document_Key,
@.p_number_of_installments=1,
@.p_auto_renew =@.eLogic_Auto_Renew
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -5
END
INSERT INTO Demo_data values ('completed inserting into account_order
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account_order',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = @.Order_Id,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed logging to detail log table for
account_order',@.Reg_Visitor_id,@.Account_
Number)
--insert into visitor_demographics table only for Online Magazines ;
Delivery Type W - Online, P- Print
IF(@.Delivery_Type = 'W')
BEGIN
INSERT INTO Demo_data values ('calling demographcis
sp',@.Reg_Visitor_id,@.Account_Number)
EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id,
@.Mag_Code = @.Mag_Code,
@.Demo_Data = @.Demo_Data,
@.Segment_Number = @.Segment_Number,
@.Version_Number = @.Version_Number
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting/updating into Visitor_Demographics
table!', 18, 6, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -6
END
END
INSERT INTO Demo_data values ('DEBUG LINE
HIT',@.Reg_Visitor_id,@.Account_Number)
END --END B3
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('About to fetch next row- current row details
-->',@.Reg_visitor_id,@.Account_number)
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
SET @.Cnt = @.Cnt + 1
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
END --END B1
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
"Alejandro Mesa" wrote:
> What are you trying to accomplish?
> Why are you using cursors?
> What kind of cursor are you using?
> Any research about a set-based solution?
> Can we see some code?
>
> AMB
> "rajeshlh" wrote:
>|||- Declare the cursor LOCAL FAST_FORWARD.
- In the WHERE clause, change:
GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date
by:
(nr.address_start_date <= GETDATE() and nr.address_end_date >= GETDATE())
- Do you need the ORDER BY clause in the select associated?
- Are the rows, of the result, processed in group or one by one?. For
example, Do you need to process multiple rows
per nr.account_number as a group?
- Can you do it by chuncks?
declare @.min int
declare @.max int
select @.min = min(nr.account_number), @.max = max(nr.account_number)
from ...
where ...
while @.min <= @.max
begin
DECLARE Subscriptions_Cursor CURSOR local fast_forward
for
select ...
from ...
where ...
and nr.account_number between @.min and case when (@.min + 1000) > @.max
then @.max else (@.min + 1000) end
open cursor ...
while 1 = 1
begin
fetch ...
if @.@.error != 0 or @.@.fetch_status != 0 break
..
end
close cursor ...
deallocate cursor ...
set @.min = @.min + 1000
end
...
AMB
"rajeshlh" wrote:
> I have to process a set of records obtained from the join of 5 tables. For
> each record in the resultset, i need to insert/update into 5-6 tables, plu
s i
> need to write to a log table the key for each inserted row or updated row
.
> Following is the code.
> The FETCH FROM Subscription_Cursor fails inside the While Loop after some
> 11,000 records. Since iam writing debug statements to another table, i can
> say that after processing 11000 records inside the cursor, the FETCH
> statement freezes.
> I did think about SET-based approach, but the processing logic forces me t
o
> use a cursor. The Job is run nightly and processes about 100,000+ records.
> Following is the SP Code.
> CREATE PROCEDURE TEST_SP
> AS
> --Variables to store Name record values
> DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
> varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
> @.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
> varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
> @.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
> varchar(11),@.Fax_Number varchar(11)
> --Variables to store Product record values
> DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
> datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
> @.Current_Email_Address varchar(50),@.Email_Password varchar(50)
> --Variables to store Order record values
> DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
> integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
> varchar(2),
> @.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
> datetime,@.Order_Entry_Type varchar(5)
> --Variables to store Demographic record values
> DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
> varchar(1024)
> --variales to store newly created reg_visitor_id,account_id and order_id -
> for subscriptions not existing in Elogic Reg DB
> DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
> integer,@.Address_Id integer
> --variables to store values present in staging tables - For subscriptions
> already existing in Elogic Reg DB
> DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
> ,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
> --variables used for logging
> DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
> varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
> @.Log_Summary_Id integer,@.Source_File_Name varchar(100)
> --Variables to store Donor's data
> DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
> varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
> @.Donor_State_Prov varchar(2),@.Donor_Country_Code
> varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
> @.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
> --Variables to store CDS to Elogic Converted values
> DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
> varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
> --Misc variables
> DECLARE @.SP_NAME varchar(50),@.Ret integer
> DECLARE @.Reg_Visitor_Product varchar(100)
> DECLARE @.Exec_Start_Time datetime
> DECLARE @.Promo_Code_Pos2 varchar(1)
> DECLARE @.Delivery_Type varchar(1)
> DECLARE @.Cnt int
>
> --Cursor to subscriptions stored in the staging tables
> DECLARE Subscriptions_Cursor CURSOR FOR
> SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_na
me,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_
name,nr.job_title,nr.company_name,
> nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code
,nr.telephone,nr.fax_number,
> pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.
num_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
> ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.
order_net_value,ord.source_code,ord.medium_code,ord.document_key,
> ord.setcode,ord.orig_start_issue,ord.order_entry_type,
> dr.version_number,dr.segment_number,dr.demo_data,
> delivery_type
> FROM cds_name_record nr
> JOIN cds_product_record pr
> ON nr.account_number=pr.account_number
> AND nr.publisher_code=pr.publisher_code
> AND nr.mag_code=pr.mag_code
> JOIN cds_order_record ord
> ON ord.account_number=pr.account_number
> AND ord.publisher_code=pr.publisher_code
> AND ord.mag_code=pr.mag_code
> JOIN cds_demographic_record dr
> ON dr.account_number=pr.account_number
> AND dr.publisher_code=pr.publisher_code
> AND dr.mag_code = pr.mag_code
> JOIN publication_subscription ps
> ON ps.external_multi_mag_code = pr.publisher_code
> AND ps.code = pr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
> Get Only current address
> AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
> Non-Gift and Donee Orders
> AND ord.order_status='B' --Get only Base Orders
> AND dr.demo_status = 'B' --Get only Base Demo Records
> --AND nr.account_number <>'0010115103'
> ORDER BY CAST(nr.account_number AS int)
>
> --cursor to the reg_feed_detail_log table , used for populating the
> reg_feed_summary_log table
> DECLARE Summary_Cursor CURSOR FOR
> SELECT
> publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*)
as
> summary_count
> FROM reg_feed_detail_log
> GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
> SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
> SET @.Exec_Start_Time = CURRENT_TIMESTAMP
> OPEN Subscriptions_Cursor
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_D
ata,@.Delivery_Type
>
> SET @.Cnt =1
> PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN --B1
> INSERT INTO Demo_data values ('inside while loop',null,null)
> INSERT INTO debug_table
> (eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code
,mag_code,order_status,set_code,delivery
_type,seq)
> VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_
publication_id,@.Account_Number
> ,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
> SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
> NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
> SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
> NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
> @.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
> NULL,@.Donor_Fax_Number = NULL,
> @.Donor_Account_number = NULL
> SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
> NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
> SELECT @.Promo_Code_Pos2 = NULL
> IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
> BEGIN --B2
> SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
> nr.postal_code,@.Donor_Company_Name = nr.company_name,
> @.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
> nr.address_line_2,@.Donor_City = nr.city,
> @.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
> nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
> nr.fax_number
> FROM cds_name_record nr
> JOIN cds_order_record ord
> ON ord.account_number=nr.account_number
> AND ord.publisher_code=nr.publisher_code
> AND ord.mag_code=nr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date -
-
> Get Only current address Name record
> AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
> AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Orde
r
> or Non-Subscibing Donor Order
> AND nr.publisher_code = @.Publisher_Code
> AND nr.mag_code = @.Mag_Code
> AND ord.order_number = @.Order_Number
> -- Note : Donor and Donee will have different account numbers, but same
> publisher code, mag code and order number
> END --END B2
>
> SET @.eLogic_Account_Status = CASE
> WHEN @.Service_Status = 'A' THEN 'A'
> WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
> WHEN @.Service_Status = 'C' THEN 'X'
> WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
> END
> SET @.eLogic_Pay_Type = CASE
> WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
> WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
> ELSE ''
> END
> SET @.eLogic_Auto_Renew= CASE
> WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN
1
> ELSE 0
> END
> SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
> SET @.eLogic_Account_Type=CASE
> WHEN @.Source_Code = 'CC' THEN
> CASE
> WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
> WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
> ELSE 'CONTROLLED'
> END
> WHEN @.Source_Code = 'CA' THEN 'COMP'
> ELSE 'PAID'
> END
>
> SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
> FROM account A
> JOIN publication_subscription PS
> ON A.pub_code = PS.code
> WHERE A.account_number = @.Account_Number
> AND PS.external_multi_mag_code = @.Publisher_Code
> --If a reg_visitor is not determined in the Staging tables or by looking
up
> the Account table , a new reg_visitor
> --is created
> IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
> BEGIN --B3
> --No Reg_Visitor corresponding to the mag subscription - mag subscriptio
n
> generated at CDS
> --Create a new reg_visitor and associated rows in Account,Account_Order
> and visitor_demographic
> SELECT @.Reg_Visitor_Product = master_brand
> FROM publication
> WHERE external_multi_mag_code=@.Publisher_Code
> --insert into reg_visior table
> EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTP
UT,
> @.p_common_name = @.Common_Name,
> @.p_company_name = @.Company_Name,
> @.p_email = @.Current_Email_Address,
> @.p_encrypted_password = @.Email_Password,
> @.p_given_name = NULL,
> @.p_login_id = @.Email_User_Name,
> @.p_merged_visitor_id = NULL,
> @.p_middle_initial = NULL,
> @.p_middle_name = NULL,
> @.p_name_suffix = NULL,
> @.p_password = @.Email_Password,
> @.p_product = @.Reg_Visitor_Product,
> @.p_subproduct = NULL,
> @.p_professional_title = @.Job_Title,
> @.p_record_status = 1,
> @.p_registration_level = 1,
> @.p_salutation = NULL,
> @.p_sur_name = NULL,
> @.p_zip = NULL,
> @.p_country = 'TESTDTS'
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAM
E)
> --ROLLBACK TRAN T1
> RETURN -2
> END
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'reg_visitor',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = NULL,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --insert into address table
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Company_Name,
> @.p_address_line_1 = @.Address_Line_1,
> @.p_address_line_2 = @.Address_Line_2,
> @.p_city = @.City,
> @.p_postal_code = @.Postal_Code,
> @.p_state_prov = @.State_Prov,
> @.p_country_code = @.Country_Code,
> @.p_phone = @.Telephone,
> @.p_fax = @.Fax_Number,
> @.p_address_type = 0 --shipping
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
> BEGIN
> --Insert the corresponding Donor Address
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Donor_Company_Name,
> @.p_address_line_1 = @.Donor_Address_Line_1,
> @.p_address_line_2 = @.Donor_Address_Line_2,
> @.p_city = @.Donor_City,
> @.p_postal_code = @.Donor_Postal_Code,
> @.p_state_prov = @.Donor_State_Prov,
> @.p_country_code = @.Donor_Country_Code,
> @.p_phone = @.Donor_Telephone,
> @.p_fax = @.Donor_Fax_Number,
> @.p_address_type = 1 --Billing
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into address for donor')
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into detail log for donor
> address')
> END
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -3
> END
> --Insert into Account table
> EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id OUTPUT,
> @.p_account_number = @.Account_Number,
> @.p_pub_code = @.Mag_Code,
> @.p_account_type = @.eLogic_Account_type ,
> @.p_status = @.eLogic_Account_Status,
> @.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
> Order, NULL will be inserted for Supp_Account_Number
>
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -4
> END
> INSERT INTO Demo_data values ('completed inserting into account
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed inserting into detail log for
> account table',@.Reg_Visitor_id,@.Account_Number)
> --insert into account_order table
> EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id,
> @.p_order_id = @.Order_Id OUTPUT,
> @.p_term = @.Order_Term,
> @.p_term_unit = NULL,
> @.p_pay_type = @.eLogic_Pay_Type,
> @.p_net_amt = @.Order_Net_Value,
> @.p_quantity = @.Num_Copies,
> @.p_vendor_order_number = @.Order_Number,
> @.p_promo_response_key = @.Document_Key,
> @.p_number_of_installments=1,
> @.p_auto_renew =@.eLogic_Auto_Renew
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_N
AME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -5
> END
> INSERT INTO Demo_data values ('completed inserting into account_order
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account_order',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = @.Order_Id,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed logging to detail log table for
> account_order',@.Reg_Visitor_id,@.Account_
Number)
> --insert into visitor_demographics table only for Online Magazines ;
> Delivery Type W - Online, P- Print
> IF(@.Delivery_Type = 'W')
> BEGIN
> INSERT INTO Demo_data values ('calling demographcis
> sp',@.Reg_Visitor_id,@.Account_Number)
> EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id
,
> @.Mag_Code = @.Mag_Code,
> @.Demo_Data = @.Demo_Data,
> @.Segment_Number = @.Segment_Number,
> @.Version_Number = @.Version_Number
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting/updating into Visitor_Demographics
> table!', 18, 6, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -6
> END
> END
> INSERT INTO Demo_data values ('DEBUG LINE
> HIT',@.Reg_Visitor_id,@.Account_Number)
>
> END --END B3
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('About to fetch next row- current row details
> -->',@.Reg_visitor_id,@.Account_number)
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_
Data,@.Delivery_Type
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
> SET @.Cnt = @.Cnt + 1
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
> END --END B1
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
>
> "Alejandro Mesa" wrote:
>