I am using the following code which works fine except that when there are alot of rows being used (> 500) this performs really slow and I get a timeout error. Any ideas on how to make this faster since I need to update multiple rows, multiple times with multiple values?
<code>
DECLARE Item_Cursor CURSOR LOCAL FAST_FORWARD FOR
Select FileObjectId,CopyFileObjectId From FileObject
Where CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime
set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE
OPEN Item_Cursor
FETCH NEXT FROM Item_Cursor INTO @.NewId,@.OldId
WHILE @.@.FETCH_STATUS = 0
BEGIN
--update idhierarchies
Update FileObject Set IdHierarchy=Replace(IdHierarchy,'.'+Cast(@.OldId as varchar)+'.','.'+Cast(@.NewId as varchar)+'.')
Where CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime
set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE
--update parent ids
Update FileObject Set ParentId=@.NewId
Where ParentId=@.OldId
AND CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime
set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE
FETCH NEXT FROM Item_Cursor INTO @.NewId,@.OldId
END
CLOSE Item_Cursor
DEALLOCATE Item_Cursor
</code>I've read that when you declare a cursor the result of the select-statement actually gets written to the temp-database and that's whats causing you such delays. But from what I can read from your procedure here it should be possible to do those updates without the use of a cursor...?|||If you have any suggestions, let me know... I can't figure out how to do it in one or two update statements, that's definitely how I would prefer to do it and try to shoot for for every op. I just couldn't figur eout how to do that here.
Originally posted by Frettmaestro
I've read that when you declare a cursor the result of the select-statement actually gets written to the temp-database and that's whats causing you such delays. But from what I can read from your procedure here it should be possible to do those updates without the use of a cursor...?sql
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Thursday, March 29, 2012
cursor type
Hi there,
Can anyone tell me what's happening here?
I have the following code snippet that opens a recordset using some stored
procedure that returns some rows.
Dim rst as new ADODB.recordset
Dim Count as long
'rst.CursorType = adOpenStatic
rst.Open "Execute my_storedprocedure" & ItemID, _
m_con, adOpenStatic, adLockReadOnly
Count =rst.Recordcount
I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
out that after opening the recordset, rst.CursorType = 0 (adOpenForwardOnly)
again. I tried setting rst.CursorType = adOpenStatic before specifically
before opening the recordset but it didn't help. It will still be reset to
adOpenForwardOnly after it's open. I think that's why RecordCount
returns -1.
Many thanks.
SusanPerhaps this will help:
http://www.sqlteam.com/item.asp?ItemID=11842
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||I found out that this only happens when I use "EXEC my_storedprocedure" to
open a recordset. If I use embedded sql to open a recordset, e.g.
rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
then it will returns the RecordCount fine.
But how do I work around that? I still like to use stored procedure though.
Thanks,
Susan
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||Could you clarify that you are using SET NOCOUNT ON within you stored
procedure?
Do you have any PRINT statetments withing your stored procedure?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Susan" <xxx> wrote in message
news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> I found out that this only happens when I use "EXEC my_storedprocedure" to
> open a recordset. If I use embedded sql to open a recordset, e.g.
> rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
> then it will returns the RecordCount fine.
> But how do I work around that? I still like to use stored procedure
though.
> Thanks,
> Susan
> "Susan" <xxx> wrote in message
news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
stored
found
adOpenStatic
>|||I suspected that too but no, I don't have SET NOCOUNT ON or Print statement.
Actually I found out sortly that if I set the connection's cursor location
to adUseClinet
m_con.CursorLocation = adUseClient
then it will return the RecordCount just fine. Does this mean that if I use
a stored procedure to open a recordset, then I need to specifically set
adUseClient to get a recordset other than a firehose forward only recordset?
But adUseServer is fine if I use embedded sql statement to open a recordset?
Susan
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Could you clarify that you are using SET NOCOUNT ON within you stored
> procedure?
> Do you have any PRINT statetments withing your stored procedure?
>
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Susan" <xxx> wrote in message
> news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> though.
> news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> stored
> found
> adOpenStatic
>|||I thought that all ADO recordsets returned from a stored procedure are
client side.
"Susan" <xxx> wrote in message news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
>I suspected that too but no, I don't have SET NOCOUNT ON or Print
>statement.
> Actually I found out sortly that if I set the connection's cursor location
> to adUseClinet
> m_con.CursorLocation = adUseClient
> then it will return the RecordCount just fine. Does this mean that if I
> use a stored procedure to open a recordset, then I need to specifically
> set adUseClient to get a recordset other than a firehose forward only
> recordset? But adUseServer is fine if I use embedded sql statement to open
> a recordset?
> Susan
>
> "Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
> news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||No, you can open a server-side cursor on a stored procedure. What's
difficult is opening a server-side scrollable cursor that supports
RecordCount on a stored procedure:
http://groups.google.com/group/micr.../>
aef2?hl=en&
To the OP, I hope you take to heart the advice in that thread to use a less
expensive way to count your records.
Bob Barrows
JT wrote:
> I thought that all ADO recordsets returned from a stored procedure are
> client side.
> "Susan" <xxx> wrote in message
> news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Can anyone tell me what's happening here?
I have the following code snippet that opens a recordset using some stored
procedure that returns some rows.
Dim rst as new ADODB.recordset
Dim Count as long
'rst.CursorType = adOpenStatic
rst.Open "Execute my_storedprocedure" & ItemID, _
m_con, adOpenStatic, adLockReadOnly
Count =rst.Recordcount
I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
out that after opening the recordset, rst.CursorType = 0 (adOpenForwardOnly)
again. I tried setting rst.CursorType = adOpenStatic before specifically
before opening the recordset but it didn't help. It will still be reset to
adOpenForwardOnly after it's open. I think that's why RecordCount
returns -1.
Many thanks.
SusanPerhaps this will help:
http://www.sqlteam.com/item.asp?ItemID=11842
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||I found out that this only happens when I use "EXEC my_storedprocedure" to
open a recordset. If I use embedded sql to open a recordset, e.g.
rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
then it will returns the RecordCount fine.
But how do I work around that? I still like to use stored procedure though.
Thanks,
Susan
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||Could you clarify that you are using SET NOCOUNT ON within you stored
procedure?
Do you have any PRINT statetments withing your stored procedure?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Susan" <xxx> wrote in message
news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> I found out that this only happens when I use "EXEC my_storedprocedure" to
> open a recordset. If I use embedded sql to open a recordset, e.g.
> rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
> then it will returns the RecordCount fine.
> But how do I work around that? I still like to use stored procedure
though.
> Thanks,
> Susan
> "Susan" <xxx> wrote in message
news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
stored
found
adOpenStatic
>|||I suspected that too but no, I don't have SET NOCOUNT ON or Print statement.
Actually I found out sortly that if I set the connection's cursor location
to adUseClinet
m_con.CursorLocation = adUseClient
then it will return the RecordCount just fine. Does this mean that if I use
a stored procedure to open a recordset, then I need to specifically set
adUseClient to get a recordset other than a firehose forward only recordset?
But adUseServer is fine if I use embedded sql statement to open a recordset?
Susan
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Could you clarify that you are using SET NOCOUNT ON within you stored
> procedure?
> Do you have any PRINT statetments withing your stored procedure?
>
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Susan" <xxx> wrote in message
> news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> though.
> news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> stored
> found
> adOpenStatic
>|||I thought that all ADO recordsets returned from a stored procedure are
client side.
"Susan" <xxx> wrote in message news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
>I suspected that too but no, I don't have SET NOCOUNT ON or Print
>statement.
> Actually I found out sortly that if I set the connection's cursor location
> to adUseClinet
> m_con.CursorLocation = adUseClient
> then it will return the RecordCount just fine. Does this mean that if I
> use a stored procedure to open a recordset, then I need to specifically
> set adUseClient to get a recordset other than a firehose forward only
> recordset? But adUseServer is fine if I use embedded sql statement to open
> a recordset?
> Susan
>
> "Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
> news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||No, you can open a server-side cursor on a stored procedure. What's
difficult is opening a server-side scrollable cursor that supports
RecordCount on a stored procedure:
http://groups.google.com/group/micr.../>
aef2?hl=en&
To the OP, I hope you take to heart the advice in that thread to use a less
expensive way to count your records.
Bob Barrows
JT wrote:
> I thought that all ADO recordsets returned from a stored procedure are
> client side.
> "Susan" <xxx> wrote in message
> news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Cursor to populate a table
Hi!
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursor
On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegr oups.com...
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,
|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
>
>
>
>
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursor
On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegr oups.com...
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,
|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
>
>
>
>
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
Cursor to populate a table
Hi!
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
>
>
>
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
>
>
>
>
>
>
>
>
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
>
>
>
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
>
>
>
>
>
>
>
>
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
Cursor to populate a table
Hi!
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > Hi!
> > I am trying to create a cursor (please see the code below) that will
> > populate client_all table that has two columns clientid and cid.
> > The first value for that table is drawn from TClient table (@.clientid)
> > and the second one is drawn from get_client_all function (cid).
> > I have to populate client_all table until I reach the end of Tclient
> > table. Tclient table has about 16000 rows. Each of @.clientID might
> > have multiple cids. This cursor runs a long time and doesn't
> > complete.
> > Anybody has any idea?
> > Thanks,
> > declare @.rownumber int
> > declare @.rowcount int
> > select @.rowcount = count(*) from tciclien
> > Declare PopulateTable_cursor Cursor for
> > select idnumber from TClient
> > open PopulateTable_cursor
> > declare @.clientid int
> > Fetch Next from PopulateTable_cursor
> > Into @.clientid
> > WHILE @.@.FETCH_STATUS <> -1
> > Begin
> > while @.rownumber < = @.rowcount
> > Begin
> > insert into Client_all
> > select @.clientid, cd.cid
> > from get_client_all(@.clientid, -1, 0) cd
> > End
> > End
> > close PopulateTable_cursor
> > deallocate PopulateTable_cursor
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>> What does get_client_all do? Can you move away from using cursors and
>> instead just join TClient against the equivalent of whatever this
>> function
>> returns?
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
> > What does get_client_all do? Can you move away from using cursors and
> > instead just join TClient against the equivalent of whatever this function
> > returns?
> > "tolcis" <nytolly...@.gmail.com> wrote in message
> >news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > > Hi!
> > > I am trying to create a cursor (please see the code below) that will
> > > populate client_all table that has two columns clientid and cid.
> > > The first value for that table is drawn from TClient table (@.clientid)
> > > and the second one is drawn from get_client_all function (cid).
> > > I have to populate client_all table until I reach the end of Tclient
> > > table. Tclient table has about 16000 rows. Each of @.clientID might
> > > have multiple cids. This cursor runs a long time and doesn't
> > > complete.
> > > Anybody has any idea?
> > > Thanks,
> > > declare @.rownumber int
> > > declare @.rowcount int
> > > select @.rowcount = count(*) from tciclien
> > > Declare PopulateTable_cursor Cursor for
> > > select idnumber from TClient
> > > open PopulateTable_cursor
> > > declare @.clientid int
> > > Fetch Next from PopulateTable_cursor
> > > Into @.clientid
> > > WHILE @.@.FETCH_STATUS <> -1
> > > Begin
> > > while @.rownumber < = @.rowcount
> > > Begin
> > > insert into Client_all
> > > select @.clientid, cd.cid
> > > from get_client_all(@.clientid, -1, 0) cd
> > > End
> > > End
> > > close PopulateTable_cursor
> > > deallocate PopulateTable_cursor
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > Hi!
> > I am trying to create a cursor (please see the code below) that will
> > populate client_all table that has two columns clientid and cid.
> > The first value for that table is drawn from TClient table (@.clientid)
> > and the second one is drawn from get_client_all function (cid).
> > I have to populate client_all table until I reach the end of Tclient
> > table. Tclient table has about 16000 rows. Each of @.clientID might
> > have multiple cids. This cursor runs a long time and doesn't
> > complete.
> > Anybody has any idea?
> > Thanks,
> > declare @.rownumber int
> > declare @.rowcount int
> > select @.rowcount = count(*) from tciclien
> > Declare PopulateTable_cursor Cursor for
> > select idnumber from TClient
> > open PopulateTable_cursor
> > declare @.clientid int
> > Fetch Next from PopulateTable_cursor
> > Into @.clientid
> > WHILE @.@.FETCH_STATUS <> -1
> > Begin
> > while @.rownumber < = @.rowcount
> > Begin
> > insert into Client_all
> > select @.clientid, cd.cid
> > from get_client_all(@.clientid, -1, 0) cd
> > End
> > End
> > close PopulateTable_cursor
> > deallocate PopulateTable_cursor
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>> What does get_client_all do? Can you move away from using cursors and
>> instead just join TClient against the equivalent of whatever this
>> function
>> returns?
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
> > What does get_client_all do? Can you move away from using cursors and
> > instead just join TClient against the equivalent of whatever this function
> > returns?
> > "tolcis" <nytolly...@.gmail.com> wrote in message
> >news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > > Hi!
> > > I am trying to create a cursor (please see the code below) that will
> > > populate client_all table that has two columns clientid and cid.
> > > The first value for that table is drawn from TClient table (@.clientid)
> > > and the second one is drawn from get_client_all function (cid).
> > > I have to populate client_all table until I reach the end of Tclient
> > > table. Tclient table has about 16000 rows. Each of @.clientID might
> > > have multiple cids. This cursor runs a long time and doesn't
> > > complete.
> > > Anybody has any idea?
> > > Thanks,
> > > declare @.rownumber int
> > > declare @.rowcount int
> > > select @.rowcount = count(*) from tciclien
> > > Declare PopulateTable_cursor Cursor for
> > > select idnumber from TClient
> > > open PopulateTable_cursor
> > > declare @.clientid int
> > > Fetch Next from PopulateTable_cursor
> > > Into @.clientid
> > > WHILE @.@.FETCH_STATUS <> -1
> > > Begin
> > > while @.rownumber < = @.rowcount
> > > Begin
> > > insert into Client_all
> > > select @.clientid, cd.cid
> > > from get_client_all(@.clientid, -1, 0) cd
> > > End
> > > End
> > > close PopulateTable_cursor
> > > deallocate PopulateTable_cursor
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
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
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 syntax / help :)
Hey lads and lasses, I have been playing around with cursors and I just cant get my code to do what I tell it ;)
Below is the code that I have written so far, which is trying to do something like this -
1) Create table - tServiceCategory
2) Create table - tTemp
3) Populate tServiceCategory
4) Declare variables and cursor
5) Run SQL I want to cursor through
6) Open cursor
7) While @.@.FETCH_STATUS = 0 insert the values obtained by the cursor into tTemp
8) Close and deallocate cursor
9) Drop tables
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
DECLARE @.Total numeric
DECLARE @.Limit_Description varchar(16)
DECLARE MyCursor CURSOR FOR
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
OPEN MyCursor
-- FETCH NEXT FROM MyCursor
-- INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
INSERT INTO tTemp(Total) VALUES (@.Total)
END
SELECT * FROM tTemp
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE tTemp
DROP TABLE tServiceCategory
Results
Select * FROM Ttemp
Total | Limit_Desc
------
Total | Limit_Desc
------
227 0 to 6 months
448 7 to 12 months
573 13 to 24 months
910 25 to 60 months
911 61 to 120 months
614 121 to 240 months
250 241+ months
So it appears that tTemp is not getting populated... But I'm banging my head against the wall with these cursors, and I can't work out what I'm doing wrong :p
If I havn't provided enough information then let me know please :)
-GeorgeVAre you doing this for fun or because you think you need to? Coz a cursor does not look like the ideal mechanism here.|||Beause I've been asked to make this a query in FoxPro so that our users can run it... Except the system doesn't support the kind of join in a regular query, so I'm making it a process. I am trying to put the results of the SQL statement into a temporary table so I can report on it... Complicated to explain, but this was the solution I came up with!
If you can think of a better way then please feel free to pass opinion :)|||What is the value of @.@.FETCH_STATUS before the first fetch?
I usually fetch before checking that value, like so:
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END|||Are you able to create a view\ stored procedure in sql server and call that from fox pro?|||Also - your problem is the bit you commented out. Why you do that George? Why? :)|||Oops - you also need to jiggle a couple of lines:
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
CREATE TABLE Employee
(
continuous_start_date SMALLDATETIME
)
INSERT INTO employee
SELECT '20050101'
UNION ALL
SELECT '20060101'
UNION ALL
SELECT '20070101'
UNION ALL
SELECT '20070202'
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
DECLARE @.Total numeric
DECLARE @.Limit_Description varchar(16)
DECLARE MyCursor CURSOR FOR
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END
SELECT * FROM tTemp
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE tTemp
DROP TABLE tServiceCategory
DROP TABLE Employee|||Original
OPEN MyCursor
-- FETCH NEXT FROM MyCursor
-- INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
INSERT INTO tTemp(Total) VALUES (@.Total)
END
With changes (they seem so obvious now!)
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END
Thank you for your help guys, much appreciated. My first attempt at a cursor wasn't so bad ;)
*gives Poots and ivon gold stars* :)|||My first attempt at a cursor wasn't so bad ;)
Apart from the fact that a cursor appears totally inappropriate for what you are doing ;) have you not had the "OMG - don't write cursors unless you are certain there is no other way to do it" speil yet?|||OOh, nobody has given me that talk so far!
The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk" :p
So what would be my other options? I'm more than willing to re-evaluate my approach... But coding in our system sucks, I don't need a cursor or anything if I didn't have to make it available to users... I hate users! :D|||If you can think of a better way then please feel free to pass opinion :)Two cursor-less alternatives:
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
CREATE TABLE Employee
(
continuous_start_date SMALLDATETIME
)
INSERT INTO employee
SELECT '20050101'
UNION ALL
SELECT '20060101'
UNION ALL
SELECT '20070101'
UNION ALL
SELECT '20070202'
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
GO
--Alternative #1 - use a view
CREATE VIEW myview
AS
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
GO
SELECT *
FROM dbo.myview
--Alternative #2 - if you simply MUST populate a temp table use SET BASED logic
INSERT INTO tTemp (total, limit_desc)
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
SELECT *
FROM dbo.ttemp
DROP TABLE tTemp
DROP TABLE tServiceCategory
DROP TABLE Employee
DROP VIEW myview|||The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk" :p
Either he is doing a lot of specialised struff or he is not a SQL expert.
SQL Server is rubbish at iterative stuff - SQL is a declarative, set based language. ALWAYS try to do everything set based where possible and only consider curosrs as a last resort. This is an area where a lot of procedural coders get tripped up when programming in SQL.
Cursors are useful for:
Admin DDL (building dynamic strings by looping through "collections" (for example looping through a table with database names and performing the same SQL action on each database)).
Certain DML statements where "previous"\ "later" rows (logically speaking of course ;)) need to be prepared to the "current" row (e.g. culminative row counts). Even here though you are best off testing. I have some links to articles if you would like.
I can't, off the top of my head, think of another time you would want to use a cursor.
HTH|||INSERT INTO! D'oh!
Well, it was nice learning cursors a wee bit anyway ;)
Why didn't I think of that?
Well, I guess it's high time to port this into FoxPro so I can play with it and see what damage I can cause :D
The table isn't neccessarily temporary, as in, it might not be dropped depending on how I can get FoxPro to interact with temporary tables... We'll see I suppose!
I'll definately come back with an answer on how I solved the overall problem :D|||... and finally. Do you remember me saying I am hopeless with cursor syntax? The reason is the above - I barely use them. In fact, I code up loops anyway so I never use them but the point is I do very little iterative stuff in SQL.
:)|||I'll definately come back with an answer on how I solved the overall problem :DHeh heh - if you want one last bit of advice - dump the temp table thing too and just use the view. My last tuppeneth I promise ;)|||Always open to advice :)
I don't think it's going to be a temp table or a view - I think it's simply going to be a table, which will be populated once and left to rot. I just needed a way of putting the results of my SQL statement into a table so I could display them for the user.
Thanks for everything Poots :beer:|||I don't think it's going to be a temp table or a view - I think it's simply going to be a table, which will be populated once and left to rot. I just needed a way of putting the results of my SQL statement into a table so I could display them for the user.
My last tuppeneth I promise ;)must... try ... to ...resist...
it's no good - what happens next month when all your counts are out?|||Ah sorry, meant to clarify that.
This little fella will run before you run the INSERT INTO
DELETE FROM <mytable>|||Hmm, so my insert into wasn't working, so I tried views.
...Which works fantastically well using QA...
But the view is not picked up by the system and cannot be created the same by the system either...
Looks like the temp table + cursor might be the way to go (*sigh*)
I hate FoxPro / our HR system.
I'll keep you posted.|||Hmm, so my insert into wasn't working, so I tried views.
...Which works fantastically well using QA...
But the view is not picked up by the system and cannot be created the same by the system either...
Looks like the temp table + cursor might be the way to go (*sigh*)
I hate FoxPro / our HR system.
I'll keep you posted.No - trouble shoot the view. The cursor is terrible. The delete\ insert is not much cop either.
Volatile data like this should always be derived at run time rather than stored if at all possible.
What do you mean by " the view is not picked up by the system and cannot be created the same by the system either..."?|||the system can only see views that it creates using it's in built data dictionary. Anything I produce in QA or EM simply canot be seen by the system - you cannot query it or even look at it.
The view cannot be built in the system because it exceeds the maximum characters (no matter how much I trim it down)... WHY IS THERE A LIMIT?!?
I'm afraid to say that I'm fed up of this problem today, so I'm going to get on with something else. The problem was solved hours and hours ago in QA, but porting this into our system is an absolute bitch.|||Ah - I think I see.
A third party system?
What is the char limit? Is it a limit on the SQL within the view? Can you not just insert info into one of application tables (*note - please don't try this - just a question)?|||3rd Party... Limit on the SQL... Don't understand :p|||I've effing done it!
Created a view using the software and then altered the view in QA.
Basically tricking it into doing what I wanted it to do :)
Damn that feels good.
(EDIT: want to know the best part? I didn't have to use any FoxPro! :D:D:D:D:D)
Now... to get exclusive access so I can put this live...
Thanks to everybody's 2 cents (and Poots's $2)!
I've learned a lot and finally cracked it (technically, I guess by "hacking" the program, but still!)
Below is the code that I have written so far, which is trying to do something like this -
1) Create table - tServiceCategory
2) Create table - tTemp
3) Populate tServiceCategory
4) Declare variables and cursor
5) Run SQL I want to cursor through
6) Open cursor
7) While @.@.FETCH_STATUS = 0 insert the values obtained by the cursor into tTemp
8) Close and deallocate cursor
9) Drop tables
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
DECLARE @.Total numeric
DECLARE @.Limit_Description varchar(16)
DECLARE MyCursor CURSOR FOR
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
OPEN MyCursor
-- FETCH NEXT FROM MyCursor
-- INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
INSERT INTO tTemp(Total) VALUES (@.Total)
END
SELECT * FROM tTemp
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE tTemp
DROP TABLE tServiceCategory
Results
Select * FROM Ttemp
Total | Limit_Desc
------
Total | Limit_Desc
------
227 0 to 6 months
448 7 to 12 months
573 13 to 24 months
910 25 to 60 months
911 61 to 120 months
614 121 to 240 months
250 241+ months
So it appears that tTemp is not getting populated... But I'm banging my head against the wall with these cursors, and I can't work out what I'm doing wrong :p
If I havn't provided enough information then let me know please :)
-GeorgeVAre you doing this for fun or because you think you need to? Coz a cursor does not look like the ideal mechanism here.|||Beause I've been asked to make this a query in FoxPro so that our users can run it... Except the system doesn't support the kind of join in a regular query, so I'm making it a process. I am trying to put the results of the SQL statement into a temporary table so I can report on it... Complicated to explain, but this was the solution I came up with!
If you can think of a better way then please feel free to pass opinion :)|||What is the value of @.@.FETCH_STATUS before the first fetch?
I usually fetch before checking that value, like so:
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END|||Are you able to create a view\ stored procedure in sql server and call that from fox pro?|||Also - your problem is the bit you commented out. Why you do that George? Why? :)|||Oops - you also need to jiggle a couple of lines:
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
CREATE TABLE Employee
(
continuous_start_date SMALLDATETIME
)
INSERT INTO employee
SELECT '20050101'
UNION ALL
SELECT '20060101'
UNION ALL
SELECT '20070101'
UNION ALL
SELECT '20070202'
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
DECLARE @.Total numeric
DECLARE @.Limit_Description varchar(16)
DECLARE MyCursor CURSOR FOR
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END
SELECT * FROM tTemp
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE tTemp
DROP TABLE tServiceCategory
DROP TABLE Employee|||Original
OPEN MyCursor
-- FETCH NEXT FROM MyCursor
-- INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
INSERT INTO tTemp(Total) VALUES (@.Total)
END
With changes (they seem so obvious now!)
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END
Thank you for your help guys, much appreciated. My first attempt at a cursor wasn't so bad ;)
*gives Poots and ivon gold stars* :)|||My first attempt at a cursor wasn't so bad ;)
Apart from the fact that a cursor appears totally inappropriate for what you are doing ;) have you not had the "OMG - don't write cursors unless you are certain there is no other way to do it" speil yet?|||OOh, nobody has given me that talk so far!
The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk" :p
So what would be my other options? I'm more than willing to re-evaluate my approach... But coding in our system sucks, I don't need a cursor or anything if I didn't have to make it available to users... I hate users! :D|||If you can think of a better way then please feel free to pass opinion :)Two cursor-less alternatives:
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
CREATE TABLE Employee
(
continuous_start_date SMALLDATETIME
)
INSERT INTO employee
SELECT '20050101'
UNION ALL
SELECT '20060101'
UNION ALL
SELECT '20070101'
UNION ALL
SELECT '20070202'
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
GO
--Alternative #1 - use a view
CREATE VIEW myview
AS
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
GO
SELECT *
FROM dbo.myview
--Alternative #2 - if you simply MUST populate a temp table use SET BASED logic
INSERT INTO tTemp (total, limit_desc)
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
SELECT *
FROM dbo.ttemp
DROP TABLE tTemp
DROP TABLE tServiceCategory
DROP TABLE Employee
DROP VIEW myview|||The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk" :p
Either he is doing a lot of specialised struff or he is not a SQL expert.
SQL Server is rubbish at iterative stuff - SQL is a declarative, set based language. ALWAYS try to do everything set based where possible and only consider curosrs as a last resort. This is an area where a lot of procedural coders get tripped up when programming in SQL.
Cursors are useful for:
Admin DDL (building dynamic strings by looping through "collections" (for example looping through a table with database names and performing the same SQL action on each database)).
Certain DML statements where "previous"\ "later" rows (logically speaking of course ;)) need to be prepared to the "current" row (e.g. culminative row counts). Even here though you are best off testing. I have some links to articles if you would like.
I can't, off the top of my head, think of another time you would want to use a cursor.
HTH|||INSERT INTO! D'oh!
Well, it was nice learning cursors a wee bit anyway ;)
Why didn't I think of that?
Well, I guess it's high time to port this into FoxPro so I can play with it and see what damage I can cause :D
The table isn't neccessarily temporary, as in, it might not be dropped depending on how I can get FoxPro to interact with temporary tables... We'll see I suppose!
I'll definately come back with an answer on how I solved the overall problem :D|||... and finally. Do you remember me saying I am hopeless with cursor syntax? The reason is the above - I barely use them. In fact, I code up loops anyway so I never use them but the point is I do very little iterative stuff in SQL.
:)|||I'll definately come back with an answer on how I solved the overall problem :DHeh heh - if you want one last bit of advice - dump the temp table thing too and just use the view. My last tuppeneth I promise ;)|||Always open to advice :)
I don't think it's going to be a temp table or a view - I think it's simply going to be a table, which will be populated once and left to rot. I just needed a way of putting the results of my SQL statement into a table so I could display them for the user.
Thanks for everything Poots :beer:|||I don't think it's going to be a temp table or a view - I think it's simply going to be a table, which will be populated once and left to rot. I just needed a way of putting the results of my SQL statement into a table so I could display them for the user.
My last tuppeneth I promise ;)must... try ... to ...resist...
it's no good - what happens next month when all your counts are out?|||Ah sorry, meant to clarify that.
This little fella will run before you run the INSERT INTO
DELETE FROM <mytable>|||Hmm, so my insert into wasn't working, so I tried views.
...Which works fantastically well using QA...
But the view is not picked up by the system and cannot be created the same by the system either...
Looks like the temp table + cursor might be the way to go (*sigh*)
I hate FoxPro / our HR system.
I'll keep you posted.|||Hmm, so my insert into wasn't working, so I tried views.
...Which works fantastically well using QA...
But the view is not picked up by the system and cannot be created the same by the system either...
Looks like the temp table + cursor might be the way to go (*sigh*)
I hate FoxPro / our HR system.
I'll keep you posted.No - trouble shoot the view. The cursor is terrible. The delete\ insert is not much cop either.
Volatile data like this should always be derived at run time rather than stored if at all possible.
What do you mean by " the view is not picked up by the system and cannot be created the same by the system either..."?|||the system can only see views that it creates using it's in built data dictionary. Anything I produce in QA or EM simply canot be seen by the system - you cannot query it or even look at it.
The view cannot be built in the system because it exceeds the maximum characters (no matter how much I trim it down)... WHY IS THERE A LIMIT?!?
I'm afraid to say that I'm fed up of this problem today, so I'm going to get on with something else. The problem was solved hours and hours ago in QA, but porting this into our system is an absolute bitch.|||Ah - I think I see.
A third party system?
What is the char limit? Is it a limit on the SQL within the view? Can you not just insert info into one of application tables (*note - please don't try this - just a question)?|||3rd Party... Limit on the SQL... Don't understand :p|||I've effing done it!
Created a view using the software and then altered the view in QA.
Basically tricking it into doing what I wanted it to do :)
Damn that feels good.
(EDIT: want to know the best part? I didn't have to use any FoxPro! :D:D:D:D:D)
Now... to get exclusive access so I can put this live...
Thanks to everybody's 2 cents (and Poots's $2)!
I've learned a lot and finally cracked it (technically, I guess by "hacking" the program, but still!)
Cursor Question (dhl)
Moving code that is running on Sybase to SQL Sever 2005. Code uses a curser. When I try running the code on the SQL server I keep getting the following errors. Here is the error message and code. Thank you for your help. David
Msg 137, Level 15, State 2, Line 356
Must declare the scalar variable "@.@.sqlstatus".
Msg 156, Level 15, State 1, Line 389
Incorrect syntax near the keyword 'CURSOR'.
DECLARE SUSPENSE_AMOUNT_CUR CURSOR FOR
SELECT
ENDDATE,
SBSB_ID,
PRINTDB_DUE,
SSA_DUE,
ACH_DUE,
OTHER_DUE,
GRGR_ID,
SGSG_ID,
SBSB_NAME,
MEME_HICN,
MEME_CK,
GRGR_CK,
SBSB_CK,
BLEI_CK
FROM #TEST3 WHERE (PRINTDB_DUE + SSA_DUE + ACH_DUE)=0
OPEN SUSPENSE_AMOUNT_CUR
FETCH SUSPENSE_AMOUNT_CUR INTO
@.SUSPENSE_ENDDATE,
@.SUSPENSE_SBSB_ID,
@.SUSPENSE_PRINTDB_DUE,
@.SUSPENSE_SSA_DUE,
@.SUSPENSE_ACH_DUE,
@.SUSPENSE_OTHER_DUE,
@.SUSPENSE_GRGR_ID,
@.SUSPENSE_SGSG_ID,
@.SUSPENSE_SBSB_NAME,
@.SUSPENSE_MEME_HICN,
@.SUSPENSE_MEME_CK,
@.SUSPENSE_GRGR_CK,
@.SUSPENSE_SBSB_CK,
@.SUSPENSE_BLEI_CK
WHILE @.@.sqlstatus = 0
BEGIN
select @.SUSPENSE_AMOUNT=Sum(BLAC.BLAC_CREDIT_AMT)-sum(BLAC.BLAC_DEBIT_AMT)
from fauafpr0_pids.dbo.CMC_BLAC_BILL_ACCT AS BLAC
where BLAC.BLEI_CK=@.SUSPENSE_BLEI_CK
AND BLAC.ACGL_TYPE='S'AND BLAC.ACGL_ACTIVITY='A'
AND BLAC.BLAC_POSTING_DT BETWEEN '01/01/2005' AND @.ENDDATE
BEGIN
IF @.SUSPENSE_AMOUNT>0
DELETE FROM #TEST3 WHERE BLEI_CK=@.SUSPENSE_BLEI_CK
COMMIT TRAN
END
FETCH SUSPENSE_AMOUNT_CUR INTO
@.SUSPENSE_ENDDATE,
@.SUSPENSE_SBSB_ID,
@.SUSPENSE_PRINTDB_DUE,
@.SUSPENSE_SSA_DUE,
@.SUSPENSE_ACH_DUE,
@.SUSPENSE_OTHER_DUE,
@.SUSPENSE_GRGR_ID,
@.SUSPENSE_SGSG_ID,
@.SUSPENSE_SBSB_NAME,
@.SUSPENSE_MEME_HICN,
@.SUSPENSE_MEME_CK,
@.SUSPENSE_GRGR_CK,
@.SUSPENSE_SBSB_CK,
@.SUSPENSE_BLEI_CK
END
CLOSE SUSPENSE_AMOUNT_CUR
DEALLOCATE CURSOR SUSPENSE_AMOUNT_CURI think you want to use @.@.fetch_status in SQL Server instead of @.@.sqlstatus|||and DEALLOCATE CURSOR SUSPENSE_AMOUNT_CUR should be DEALLOCATE SUSPENSE_AMOUNT_CUR.
this does not have to be a cursor.|||Thanks, I took out the "CURSOR" and changed @.@.sqlstatus = 0 to @.@.FETCH_STATUS = 0. Now I getting this error:
Msg 3902, Level 16, State 1, Line 368
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Thanks for your help. First time working with cursor's. david|||Thanks, I took out the "CURSOR" and changed @.@.sqlstatus = 0 to @.@.FETCH_STATUS = 0. Now I getting this error:
Msg 3902, Level 16, State 1, Line 368
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Thanks for your help. First time working with cursor's. david
Well that error is pretty specific...where's your BEGIN TRAN?
Also, you should convert this to a set based process
Got DDL?|||The Code had two begin but they just say begin not BEGIN TRAN. Not sure how to convert to a based process or what is DDL. Never worked with cursor. This code is running on a Sybase system and I need to move it to SQL Server 2005. Thanks you for your help. david|||I beleive this will eliminate all of your code
Post the DDL so I can test it
DELETE t
FROM #TEST3 t
JOIN fauafpr0_pids.dbo.CMC_BLAC_BILL_ACCT AS BLAC
ON t.BLEI_CK = BLAC.BLEI_CK
WHERE BLAC.ACGL_TYPE='S'
AND BLAC.ACGL_ACTIVITY='A'
AND BLAC.BLAC_POSTING_DT BETWEEN '01/01/2005'
HAVING SUM(BLAC.BLAC_CREDIT_AMT)-SUM(BLAC.BLAC_DEBIT_AMT) > 0|||The Code had two begin but they just say begin not BEGIN TRAN. Not sure how to convert to a based process or what is DDL. Never worked with cursor. This code is running on a Sybase system and I need to move it to SQL Server 2005. Thanks you for your help. david
Read the hint sticky at the top of the forum to see what you need to post|||This is one small part of a larger procedure. The other parts that use the cursor are having the same problem. I posted the above code, it was the smallest number of lines. Still need to find out why I am getting a the same error on the other code that uses the cursor. Msg 3902, Level 16, State 1, Line 368
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
David|||Thanks, I took out the "CURSOR" and changed @.@.sqlstatus = 0 to @.@.FETCH_STATUS = 0. Now I getting this error:
Msg 3902, Level 16, State 1, Line 368
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
What do you mean by "I took out the cursor"?|||In the code DEALLOCATE CURSOR SUSPENSE_AMOUNT_CUR
after I removed the word CURSOR in the line of code the error that fixed the error: Incorrect syntax near the keyword 'CURSOR'.
Now I getting this error: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. David|||If @.suspense_amount>0
begin Tran
Delete From #test3 Where Blei_ck=@.suspense_blei_ck
Commit Tran
End|||When I change my code to "BEGIN TRAN" and add in the If code, I now get this error: Incorrect syntax near the keyword 'CLOSE'.
David
P.S. Is there a good place on the web that I can find good documations on how to use Cursor's.
Msg 137, Level 15, State 2, Line 356
Must declare the scalar variable "@.@.sqlstatus".
Msg 156, Level 15, State 1, Line 389
Incorrect syntax near the keyword 'CURSOR'.
DECLARE SUSPENSE_AMOUNT_CUR CURSOR FOR
SELECT
ENDDATE,
SBSB_ID,
PRINTDB_DUE,
SSA_DUE,
ACH_DUE,
OTHER_DUE,
GRGR_ID,
SGSG_ID,
SBSB_NAME,
MEME_HICN,
MEME_CK,
GRGR_CK,
SBSB_CK,
BLEI_CK
FROM #TEST3 WHERE (PRINTDB_DUE + SSA_DUE + ACH_DUE)=0
OPEN SUSPENSE_AMOUNT_CUR
FETCH SUSPENSE_AMOUNT_CUR INTO
@.SUSPENSE_ENDDATE,
@.SUSPENSE_SBSB_ID,
@.SUSPENSE_PRINTDB_DUE,
@.SUSPENSE_SSA_DUE,
@.SUSPENSE_ACH_DUE,
@.SUSPENSE_OTHER_DUE,
@.SUSPENSE_GRGR_ID,
@.SUSPENSE_SGSG_ID,
@.SUSPENSE_SBSB_NAME,
@.SUSPENSE_MEME_HICN,
@.SUSPENSE_MEME_CK,
@.SUSPENSE_GRGR_CK,
@.SUSPENSE_SBSB_CK,
@.SUSPENSE_BLEI_CK
WHILE @.@.sqlstatus = 0
BEGIN
select @.SUSPENSE_AMOUNT=Sum(BLAC.BLAC_CREDIT_AMT)-sum(BLAC.BLAC_DEBIT_AMT)
from fauafpr0_pids.dbo.CMC_BLAC_BILL_ACCT AS BLAC
where BLAC.BLEI_CK=@.SUSPENSE_BLEI_CK
AND BLAC.ACGL_TYPE='S'AND BLAC.ACGL_ACTIVITY='A'
AND BLAC.BLAC_POSTING_DT BETWEEN '01/01/2005' AND @.ENDDATE
BEGIN
IF @.SUSPENSE_AMOUNT>0
DELETE FROM #TEST3 WHERE BLEI_CK=@.SUSPENSE_BLEI_CK
COMMIT TRAN
END
FETCH SUSPENSE_AMOUNT_CUR INTO
@.SUSPENSE_ENDDATE,
@.SUSPENSE_SBSB_ID,
@.SUSPENSE_PRINTDB_DUE,
@.SUSPENSE_SSA_DUE,
@.SUSPENSE_ACH_DUE,
@.SUSPENSE_OTHER_DUE,
@.SUSPENSE_GRGR_ID,
@.SUSPENSE_SGSG_ID,
@.SUSPENSE_SBSB_NAME,
@.SUSPENSE_MEME_HICN,
@.SUSPENSE_MEME_CK,
@.SUSPENSE_GRGR_CK,
@.SUSPENSE_SBSB_CK,
@.SUSPENSE_BLEI_CK
END
CLOSE SUSPENSE_AMOUNT_CUR
DEALLOCATE CURSOR SUSPENSE_AMOUNT_CURI think you want to use @.@.fetch_status in SQL Server instead of @.@.sqlstatus|||and DEALLOCATE CURSOR SUSPENSE_AMOUNT_CUR should be DEALLOCATE SUSPENSE_AMOUNT_CUR.
this does not have to be a cursor.|||Thanks, I took out the "CURSOR" and changed @.@.sqlstatus = 0 to @.@.FETCH_STATUS = 0. Now I getting this error:
Msg 3902, Level 16, State 1, Line 368
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Thanks for your help. First time working with cursor's. david|||Thanks, I took out the "CURSOR" and changed @.@.sqlstatus = 0 to @.@.FETCH_STATUS = 0. Now I getting this error:
Msg 3902, Level 16, State 1, Line 368
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Thanks for your help. First time working with cursor's. david
Well that error is pretty specific...where's your BEGIN TRAN?
Also, you should convert this to a set based process
Got DDL?|||The Code had two begin but they just say begin not BEGIN TRAN. Not sure how to convert to a based process or what is DDL. Never worked with cursor. This code is running on a Sybase system and I need to move it to SQL Server 2005. Thanks you for your help. david|||I beleive this will eliminate all of your code
Post the DDL so I can test it
DELETE t
FROM #TEST3 t
JOIN fauafpr0_pids.dbo.CMC_BLAC_BILL_ACCT AS BLAC
ON t.BLEI_CK = BLAC.BLEI_CK
WHERE BLAC.ACGL_TYPE='S'
AND BLAC.ACGL_ACTIVITY='A'
AND BLAC.BLAC_POSTING_DT BETWEEN '01/01/2005'
HAVING SUM(BLAC.BLAC_CREDIT_AMT)-SUM(BLAC.BLAC_DEBIT_AMT) > 0|||The Code had two begin but they just say begin not BEGIN TRAN. Not sure how to convert to a based process or what is DDL. Never worked with cursor. This code is running on a Sybase system and I need to move it to SQL Server 2005. Thanks you for your help. david
Read the hint sticky at the top of the forum to see what you need to post|||This is one small part of a larger procedure. The other parts that use the cursor are having the same problem. I posted the above code, it was the smallest number of lines. Still need to find out why I am getting a the same error on the other code that uses the cursor. Msg 3902, Level 16, State 1, Line 368
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
David|||Thanks, I took out the "CURSOR" and changed @.@.sqlstatus = 0 to @.@.FETCH_STATUS = 0. Now I getting this error:
Msg 3902, Level 16, State 1, Line 368
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
What do you mean by "I took out the cursor"?|||In the code DEALLOCATE CURSOR SUSPENSE_AMOUNT_CUR
after I removed the word CURSOR in the line of code the error that fixed the error: Incorrect syntax near the keyword 'CURSOR'.
Now I getting this error: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. David|||If @.suspense_amount>0
begin Tran
Delete From #test3 Where Blei_ck=@.suspense_blei_ck
Commit Tran
End|||When I change my code to "BEGIN TRAN" and add in the If code, I now get this error: Incorrect syntax near the keyword 'CLOSE'.
David
P.S. Is there a good place on the web that I can find good documations on how to use Cursor's.
Cursor question
I've recently began studying cursors. The following code is from my server but in don't understand the purpose of the highlighted part.
declare c_cursor cursor for
select distinct suniq from Stustat where trkuniq in (select trkuniq from track where schoolc = @.schoolc) and
graden >= @.beginningGradeForCalc
open c_cursor
fetch next from c_cursor into @.variable
while (@.@.fetch_status= 0)
begin
-- CHECK GPA (FIRES OFF SINGLE STUDENT DYNAMIC GPA CALCULATION FOR EACH SUNIQ IN STUGRP_ACTIVE FOR A SPECIFIED SCHOOL)
-- exec siSp_dyngpa @.suniq, @.schyear, @.graden example: exec sisp_dyngpa @.variable, 2007, 9, 1
exec sisp_dyngpa @.variable, @.beginningYrForCalc, @.beginningGradeForCalc, '1'
fetch next from c_cursor into @.variable
end
fetch next from c_cursor into @.variable
deallocate c_cursor
Is there an obvious reason for the FETCH after teh END statment?
I don't think it should be there as its outside the while loop. Doesn't serve any purpose as you are deallocating the cursor straight afterwards anyway and so no processing is done with the results.
HTH!
|||No, there is no obvious reason. also, remember to close the cursor before deallocating it.
AMB
|||What are the ramification of not closing the cursor before deallocating it? Or it is more a matter of procedure?|||I am not familiar with the internals, but I think that the description in BOL is clear.
AMB
sqlCursor Problem in an DTS ActivX package.
Hi
I am using Cursor in an DTS ActivX package.
I got the problem when i update it's not updating prorerly Please find the code below and help me ..My insert is working fine but the problem with update it's only updating first record that to it's replace the last record in first record place
sSSDInsUpd = " DECLARE "& _ " @.CallID2 AS Int "& _ " Declare InsertCur Cursor For "& _ " SELECT DISTINCT CALLID "& _ " FROM [SSD] "& _ " WHERE ASSOCCLAIM is not null AND SSN IS NOT NULL AND "& _ " FILENETPROCESSFLAG=1 "& _ " OPEN InsertCur "& _ " FETCH NEXT FROM InsertCur "& _ " into @.CallID2 "& _ " WHILE @.@.FETCH_STATUS = 0 "& _ " BEGIN "& _ " If @.CallID2 not in (select CALLID FROM SSDTOARTS) "& _ " Begin "& _ " INSERT INTO SSDTOARTS ( "& _ " [ID], [CALLID],[FIRSTNAME],[MIDDLEINIT],[LASTNAME],[SUFFIX], "& _ " [DATEOFBIRTH],[SSN],[ADDR1],[ADDR2], [CITY], [STATE],[ZIP], "& _ " [DISTRICTCODE ],[COUNTYCODE ],[ACCENTCASENO],[ASSOCCLAIM], "& _ " [ASSOCCLMDTFILED ],[ASSOCCLMDECDT],[ASSOCCLMSTATUS], "& _ " [ASSOCCLMADDLACCENTACTION] , "& _ " [ASSOCCLMADDLACCENTACTIONTAKEN], "& _ " [SSAAPLFILED ],[SSAAPLDT],[SSAAPLRESULT], "& _ " [SSAAPLADDLACCENTACTION],[SSAAPLADDLACCENTACTIONTAKEN], "& _ " [SSAAplDecisionDt],[ROUNDNUMBER], [ARTSAplprocessedFlag],[ARTSprocessedDt],[DateModified] ) "& _ " SELECT DISTINCT "& _ " S.ID, S.CALLID, S.FIRSTNAME, S.MIDDLEINIT, S.LASTNAME,S.SUFFIX, "& _ " S.DATEOFBIRTH, S.SSN, S.ADDR1, S.ADDR2, S.CITY, S.STATE, S.ZIP, "& _ " S.DISTRICTCODE , S.COUNTYCODE , S.ACCENTCASENO, S.ASSOCCLAIM, "& _ " S.ASSOCCLMDTFILED ,S.ASSOCCLMDECDT, S.ASSOCCLMSTATUS, "& _ " S.ASSOCCLMADDLACCENTACTION , "& _ " S.ASSOCCLMADDLACCENTACTIONTAKEN, "& _ " S.SSAAPLFILED , S.SSAAPLDT, S.SSAAPLRESULT, "& _ " S.SSAAPLADDLACCENTACTION,S.SSAAPLADDLACCENTACTIONTAKEN, "& _ " S.SSAAplDecisionDt , S.ROUNDNUMBER, "& _ " 0 as ARTSAplprocessedFlag,Null as ARTSprocessedDt,getdate() as DateModified "& _ " FROM [SSD] AS S "& _ " WHERE S.ASSOCCLAIM = 1 AND S.SSN IS NOT NULL AND "& _ " S.CALLID = @.CallID2 AND S.FILENETPROCESSFLAG=1 "& _ " DECLARE @.err2 int "& _ " SELECT @.err2 = @.@.error "& _ " IF (@.err2 <> 0) "& _ " BEGIN "& _ " INSERT INTO SSDEXCEPTIONS (RecordData,ErrorMsg,Fileid,DateCreated) "& _ " SELECT "& _ " DISTINCT isnull(convert( varchar(10),CALLID),'') + ' |' + isnull(FIRSTNAME,'') + ' |' + "& _ " isnull(MIDDLEINIT,'') + '| ' + "& _ " isnull(LASTNAME,'')+ '|' + "& _ " isnull((SUFFIX),'') + '|' + "& _ " isnull(convert(varchar(10),DATEOFBIRTH ),'') + '|' + "& _ " isnull(convert(varchar(10),SSN),'') + '|' + "& _ " isnull(ADDR1,'') + '|' + "& _ " isnull(ADDR2,'') + '|' + "& _ " isnull(CITY,'') + '|' + "& _ " isnull([STATE],'') + '|' + "& _ " isnull(ZIP,'')+ '|' + "& _ " isnull(convert(varchar(10),DISTRICTCODE),'') + '|' + "& _ " isnull(convert(varchar (10),COUNTYCODE),'') + '|' + "& _ " isnull(ACCENTCASENO,'')+ '|' + "& _ " isnull(convert(nvarchar(2),ASSOCCLAIM),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDTFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDECDT),'') + '|' + "& _ " isnull(ASSOCCLMSTATUS,'') + '|' + "& _ " isnull(convert(nvarchar(2 ),ASSOCCLMADDLACCENTACTION),'') + '|' + "& _ " isnull(ASSOCCLMADDLACCENTACTIONTAKEN,'')+ '|' + "& _ " isnull(convert(nvarchar(2),SSAAPLFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,SSAAPLDT),'') + '|' + "& _ " isnull(SSAAPLRESULT,'')+ '|' + "& _ " isnull(convert(nvarchar(2 ),SSAAPLADDLACCENTACTION),'') + '|' + "& _ " isnull(SSAAPLADDLACCENTACTIONTAKEN,'') + '|' + "& _ " isnull(convert(varchar(10 ),SSAAplDecisionDt),'')+ '|' + "& _ " isnull(convert(varchar(10 ),ROUNDNUMBER),'') ,ltrim(str(@.err2))," & sFileId & ",GETDATE() "& _ " FROM SSD WHERE CALLID=@.CALLID2 "& _ " END "& _ " END "& _ " ELSE "& _ " UPDATE [SSDTOARTS] SET "& _ " [ID]= S.ID, "& _ " [CALLID]= S.CALLID , "& _ " [FIRSTNAME]=S.FIRSTNAME , "& _ " [MIDDLEINIT]=S.MIDDLEINIT , "& _ " [LASTNAME]= S.LASTNAME, "& _ " [Suffix]= S.Suffix , "& _ " [DateOfBirth]=S.DateOfBirth , "& _ " [SSN]= S.SSN , "& _ " [ADDR1]= S.ADDR1 , "& _ " [ADDR2]= S.ADDR2 , "& _ " [CITY]= S.CITY , "& _ " [STATE]= S.STATE , "& _ " [ZIP]= S.ZIP , "& _ " [DistrictCode ]= S.DistrictCode , "& _ " [CountyCode ]= S.CountyCode , "& _ " [ACCENTCASENO]= S.ACCENTCASENO ,"& _ " [ASSOCCLAIM]= S.ASSOCCLAIM, "& _ " [ASSOCCLMDTFILED]= S.ASSOCCLMDTFILED , "& _ " [AssocClmDecDt]= s.AssocClmDecDt , "& _ " [ASSOCCLMSTATUS]= S.ASSOCCLMSTATUS, "& _ " [AssocClmAddlACCENTAction] = S.AssocClmAddlACCENTAction , "& _ " [AssocClmAddlACCENTActionTaken]= S.AssocClmAddlACCENTActionTaken, "& _ " [SSAAplFiled ]= S.SSAAplFiled , "& _ " [SSAAplDt]= S.SSAAplDt , "& _ " [SSAAplResult]= S.SSAAplResult, "& _ " [SSAAplAddlACCENTAction]= S.SSAAplAddlACCENTAction, "& _ " [SSAAplAddlACCENTActionTaken]= S.SSAAplAddlACCENTActionTaken , "& _ " [SSAAplDecisionDt] = S.SSAAplDecisionDt, "& _ " [ROUNDNUMBER]= S.ROUNDNUMBER , "& _ " [DateModified] = getdate(),"& _ " [ARTSprocessedDt]= NULL, "& _ " [ARTSAplprocessedFlag]= 0 "& _ " FROM [SSDtoARTS]AS SA, [SSD] AS S WHERE SA.CallID= @.Callid2 "& _ " AND S.ASSOCCLAIM IS NOT NULL AND S.FILENETPROCESSFLAG=1 "& _ " DECLARE @.err1 as int "& _ " SELECT @.err1 = @.@.error "& _ " IF (@.err1 <> 0) "& _ " BEGIN "& _ " INSERT INTO SSDEXCEPTIONS (RecordData,ErrorMsg,Fileid,DateCreated) "& _ " SELECT "& _ " DISTINCT isnull(convert( varchar(10),CALLID),'') + ' |' + isnull(FIRSTNAME,'') + ' |' + "& _ " isnull(MIDDLEINIT,'') + '| ' + "& _ " isnull(LASTNAME,'')+ '|' + "& _ " isnull((SUFFIX),'') + '|' + "& _ " isnull(convert(varchar(10),DATEOFBIRTH ),'') + '|' + "& _ " isnull(convert(varchar(10),SSN),'') + '|' + "& _ " isnull(ADDR1,'') + '|' + "& _ " isnull(ADDR2,'') + '|' + "& _ " isnull(CITY,'') + '|' + "& _ " isnull([STATE],'') + '|' + "& _ " isnull(ZIP,'')+ '|' + "& _ " isnull(convert(varchar(10),DISTRICTCODE),'') + '|' + "& _ " isnull(convert(varchar (10),COUNTYCODE),'') + '|' + "& _ " isnull(ACCENTCASENO,'')+ '|' + "& _ " isnull(convert(nvarchar(2),ASSOCCLAIM),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDTFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDECDT),'') + '|' + "& _ " isnull(ASSOCCLMSTATUS,'') + '|' + "& _ " isnull(convert(nvarchar(2 ),ASSOCCLMADDLACCENTACTION),'') + '|' + "& _ " isnull(ASSOCCLMADDLACCENTACTIONTAKEN,'')+ '|' + "& _ " isnull(convert(nvarchar(2),SSAAPLFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,SSAAPLDT),'') + '|' + "& _ " isnull(SSAAPLRESULT,'')+ '|' + "& _ " isnull(convert(nvarchar(2 ),SSAAPLADDLACCENTACTION),'') + '|' + "& _ " isnull(SSAAPLADDLACCENTACTIONTAKEN,'') + '|' + "& _ " isnull(convert(varchar(10 ),SSAAplDecisionDt),'')+ '|' + "& _ " isnull(convert(varchar(10 ),ROUNDNUMBER),'') ,ltrim(str(@.err1))," & sFileId1 & ",GETDATE() "& _ " FROM SSD WHERE CALLID=@.CALLID2 "& _ " END "& _ " FETCH NEXT FROM InsertCur "& _ " INTO @.CALLID2 "& _ " END "& _ " CLOSE InsertCur "& _ " DEALLOCATE InsertCur "
Hi
Problem with While loop and if statement begin and end statements.check the below syantax This might help you
DECLARE @.price money
DECLARE @.get_price CURSOR
SET @.get_price = CURSOR FOR
SELECT price FROM titles
OPEN @.get_price
FETCH NEXT FROM @.get_price INTO @.price
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF @.Price < 20
SELECT 'Under 20'
ELSE
SELECT @.Price
FETCH NEXT FROM @.get_price INTO @.price
END
CLOSE @.get_price
DEALLOCATE @.get_price
Cursor Problem in an DTS ActivX package.
Hi
I am using Cursor in an DTS ActivX package.
I got the problem when i update it's not updating prorerly Please find the code below and help me ..My insert is working fine but the problem with update it's only updating first record that to it's replace the last record in first record place
sSSDInsUpd = " DECLARE "& _ " @.CallID2 AS Int "& _ " Declare InsertCur Cursor For "& _ " SELECT DISTINCT CALLID "& _ " FROM [SSD] "& _ " WHERE ASSOCCLAIM is not null AND SSN IS NOT NULL AND "& _ " FILENETPROCESSFLAG=1 "& _ " OPEN InsertCur "& _ " FETCH NEXT FROM InsertCur "& _ " into @.CallID2 "& _ " WHILE @.@.FETCH_STATUS = 0 "& _ " BEGIN "& _ " If @.CallID2 not in (select CALLID FROM SSDTOARTS) "& _ " Begin "& _ " INSERT INTO SSDTOARTS ( "& _ " [ID], [CALLID],[FIRSTNAME],[MIDDLEINIT],[LASTNAME],[SUFFIX], "& _ " [DATEOFBIRTH],[SSN],[ADDR1],[ADDR2], [CITY], [STATE],[ZIP], "& _ " [DISTRICTCODE ],[COUNTYCODE ],[ACCENTCASENO],[ASSOCCLAIM], "& _ " [ASSOCCLMDTFILED ],[ASSOCCLMDECDT],[ASSOCCLMSTATUS], "& _ " [ASSOCCLMADDLACCENTACTION] , "& _ " [ASSOCCLMADDLACCENTACTIONTAKEN], "& _ " [SSAAPLFILED ],[SSAAPLDT],[SSAAPLRESULT], "& _ " [SSAAPLADDLACCENTACTION],[SSAAPLADDLACCENTACTIONTAKEN], "& _ " [SSAAplDecisionDt],[ROUNDNUMBER], [ARTSAplprocessedFlag],[ARTSprocessedDt],[DateModified] ) "& _ " SELECT DISTINCT "& _ " S.ID, S.CALLID, S.FIRSTNAME, S.MIDDLEINIT, S.LASTNAME,S.SUFFIX, "& _ " S.DATEOFBIRTH, S.SSN, S.ADDR1, S.ADDR2, S.CITY, S.STATE, S.ZIP, "& _ " S.DISTRICTCODE , S.COUNTYCODE , S.ACCENTCASENO, S.ASSOCCLAIM, "& _ " S.ASSOCCLMDTFILED ,S.ASSOCCLMDECDT, S.ASSOCCLMSTATUS, "& _ " S.ASSOCCLMADDLACCENTACTION , "& _ " S.ASSOCCLMADDLACCENTACTIONTAKEN, "& _ " S.SSAAPLFILED , S.SSAAPLDT, S.SSAAPLRESULT, "& _ " S.SSAAPLADDLACCENTACTION,S.SSAAPLADDLACCENTACTIONTAKEN, "& _ " S.SSAAplDecisionDt , S.ROUNDNUMBER, "& _ " 0 as ARTSAplprocessedFlag,Null as ARTSprocessedDt,getdate() as DateModified "& _ " FROM [SSD] AS S "& _ " WHERE S.ASSOCCLAIM = 1 AND S.SSN IS NOT NULL AND "& _ " S.CALLID = @.CallID2 AND S.FILENETPROCESSFLAG=1 "& _ " DECLARE @.err2 int "& _ " SELECT @.err2 = @.@.error "& _ " IF (@.err2 <> 0) "& _ " BEGIN "& _ " INSERT INTO SSDEXCEPTIONS (RecordData,ErrorMsg,Fileid,DateCreated) "& _ " SELECT "& _ " DISTINCT isnull(convert( varchar(10),CALLID),'') + ' |' + isnull(FIRSTNAME,'') + ' |' + "& _ " isnull(MIDDLEINIT,'') + '| ' + "& _ " isnull(LASTNAME,'')+ '|' + "& _ " isnull((SUFFIX),'') + '|' + "& _ " isnull(convert(varchar(10),DATEOFBIRTH ),'') + '|' + "& _ " isnull(convert(varchar(10),SSN),'') + '|' + "& _ " isnull(ADDR1,'') + '|' + "& _ " isnull(ADDR2,'') + '|' + "& _ " isnull(CITY,'') + '|' + "& _ " isnull([STATE],'') + '|' + "& _ " isnull(ZIP,'')+ '|' + "& _ " isnull(convert(varchar(10),DISTRICTCODE),'') + '|' + "& _ " isnull(convert(varchar (10),COUNTYCODE),'') + '|' + "& _ " isnull(ACCENTCASENO,'')+ '|' + "& _ " isnull(convert(nvarchar(2),ASSOCCLAIM),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDTFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDECDT),'') + '|' + "& _ " isnull(ASSOCCLMSTATUS,'') + '|' + "& _ " isnull(convert(nvarchar(2 ),ASSOCCLMADDLACCENTACTION),'') + '|' + "& _ " isnull(ASSOCCLMADDLACCENTACTIONTAKEN,'')+ '|' + "& _ " isnull(convert(nvarchar(2),SSAAPLFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,SSAAPLDT),'') + '|' + "& _ " isnull(SSAAPLRESULT,'')+ '|' + "& _ " isnull(convert(nvarchar(2 ),SSAAPLADDLACCENTACTION),'') + '|' + "& _ " isnull(SSAAPLADDLACCENTACTIONTAKEN,'') + '|' + "& _ " isnull(convert(varchar(10 ),SSAAplDecisionDt),'')+ '|' + "& _ " isnull(convert(varchar(10 ),ROUNDNUMBER),'') ,ltrim(str(@.err2))," & sFileId & ",GETDATE() "& _ " FROM SSD WHERE CALLID=@.CALLID2 "& _ " END "& _ " END "& _ " ELSE "& _ " UPDATE [SSDTOARTS] SET "& _ " [ID]= S.ID, "& _ " [CALLID]= S.CALLID , "& _ " [FIRSTNAME]=S.FIRSTNAME , "& _ " [MIDDLEINIT]=S.MIDDLEINIT , "& _ " [LASTNAME]= S.LASTNAME, "& _ " [Suffix]= S.Suffix , "& _ " [DateOfBirth]=S.DateOfBirth , "& _ " [SSN]= S.SSN , "& _ " [ADDR1]= S.ADDR1 , "& _ " [ADDR2]= S.ADDR2 , "& _ " [CITY]= S.CITY , "& _ " [STATE]= S.STATE , "& _ " [ZIP]= S.ZIP , "& _ " [DistrictCode ]= S.DistrictCode , "& _ " [CountyCode ]= S.CountyCode , "& _ " [ACCENTCASENO]= S.ACCENTCASENO ,"& _ " [ASSOCCLAIM]= S.ASSOCCLAIM, "& _ " [ASSOCCLMDTFILED]= S.ASSOCCLMDTFILED , "& _ " [AssocClmDecDt]= s.AssocClmDecDt , "& _ " [ASSOCCLMSTATUS]= S.ASSOCCLMSTATUS, "& _ " [AssocClmAddlACCENTAction] = S.AssocClmAddlACCENTAction , "& _ " [AssocClmAddlACCENTActionTaken]= S.AssocClmAddlACCENTActionTaken, "& _ " [SSAAplFiled ]= S.SSAAplFiled , "& _ " [SSAAplDt]= S.SSAAplDt , "& _ " [SSAAplResult]= S.SSAAplResult, "& _ " [SSAAplAddlACCENTAction]= S.SSAAplAddlACCENTAction, "& _ " [SSAAplAddlACCENTActionTaken]= S.SSAAplAddlACCENTActionTaken , "& _ " [SSAAplDecisionDt] = S.SSAAplDecisionDt, "& _ " [ROUNDNUMBER]= S.ROUNDNUMBER , "& _ " [DateModified] = getdate(),"& _ " [ARTSprocessedDt]= NULL, "& _ " [ARTSAplprocessedFlag]= 0 "& _ " FROM [SSDtoARTS]AS SA, [SSD] AS S WHERE SA.CallID= @.Callid2 "& _ " AND S.ASSOCCLAIM IS NOT NULL AND S.FILENETPROCESSFLAG=1 "& _ " DECLARE @.err1 as int "& _ " SELECT @.err1 = @.@.error "& _ " IF (@.err1 <> 0) "& _ " BEGIN "& _ " INSERT INTO SSDEXCEPTIONS (RecordData,ErrorMsg,Fileid,DateCreated) "& _ " SELECT "& _ " DISTINCT isnull(convert( varchar(10),CALLID),'') + ' |' + isnull(FIRSTNAME,'') + ' |' + "& _ " isnull(MIDDLEINIT,'') + '| ' + "& _ " isnull(LASTNAME,'')+ '|' + "& _ " isnull((SUFFIX),'') + '|' + "& _ " isnull(convert(varchar(10),DATEOFBIRTH ),'') + '|' + "& _ " isnull(convert(varchar(10),SSN),'') + '|' + "& _ " isnull(ADDR1,'') + '|' + "& _ " isnull(ADDR2,'') + '|' + "& _ " isnull(CITY,'') + '|' + "& _ " isnull([STATE],'') + '|' + "& _ " isnull(ZIP,'')+ '|' + "& _ " isnull(convert(varchar(10),DISTRICTCODE),'') + '|' + "& _ " isnull(convert(varchar (10),COUNTYCODE),'') + '|' + "& _ " isnull(ACCENTCASENO,'')+ '|' + "& _ " isnull(convert(nvarchar(2),ASSOCCLAIM),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDTFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDECDT),'') + '|' + "& _ " isnull(ASSOCCLMSTATUS,'') + '|' + "& _ " isnull(convert(nvarchar(2 ),ASSOCCLMADDLACCENTACTION),'') + '|' + "& _ " isnull(ASSOCCLMADDLACCENTACTIONTAKEN,'')+ '|' + "& _ " isnull(convert(nvarchar(2),SSAAPLFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,SSAAPLDT),'') + '|' + "& _ " isnull(SSAAPLRESULT,'')+ '|' + "& _ " isnull(convert(nvarchar(2 ),SSAAPLADDLACCENTACTION),'') + '|' + "& _ " isnull(SSAAPLADDLACCENTACTIONTAKEN,'') + '|' + "& _ " isnull(convert(varchar(10 ),SSAAplDecisionDt),'')+ '|' + "& _ " isnull(convert(varchar(10 ),ROUNDNUMBER),'') ,ltrim(str(@.err1))," & sFileId1 & ",GETDATE() "& _ " FROM SSD WHERE CALLID=@.CALLID2 "& _ " END "& _ " FETCH NEXT FROM InsertCur "& _ " INTO @.CALLID2 "& _ " END "& _ " CLOSE InsertCur "& _ " DEALLOCATE InsertCur "
Hi
Problem with While loop and if statement begin and end statements.check the below syantax This might help you
DECLARE @.price money
DECLARE @.get_price CURSOR
SET @.get_price = CURSOR FOR
SELECT price FROM titles
OPEN @.get_price
FETCH NEXT FROM @.get_price INTO @.price
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF @.Price < 20
SELECT 'Under 20'
ELSE
SELECT @.Price
FETCH NEXT FROM @.get_price INTO @.price
END
CLOSE @.get_price
DEALLOCATE @.get_price
Tuesday, March 27, 2012
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).
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 Issue - Order not correct
I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records.
I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc.
In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode).
Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed.
/*CREATE Sales_Table table */
CREATE TABLE [dbo].[Sales_Table] (
[count_id] [int] NULL ,
[org_id] [int] NULL ,
[postalcode] [varchar] (20) NULL,
[sales] [numeric](18,6) NULL ,
[sales_rank] [integer] NULL,
[org_criteria_input_date] [datetime] NULL
) ON [PRIMARY]
insert into Sales_Table
select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date
from org_model_data omd
join org o on o.org_id = omd.org_id
where o.client_id = @.ClientID
and model_Criteria_id = 27
and org_criteria_value <> 0
order by omd.org_id asc, omd.org_criteria_value desc
-- DECLARE CURSOR for Sales_Table
declare SalesRankCursor CURSOR
SCROLL dynamic FOR
select org_id, sales, sales_rank
from Sales_Table
for update of sales_rank
-- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values
OPEN SalesRankCursor
while exists (Select * from Sales_Table where sales_rank = 0)
Begin
FETCH NEXT FROM SalesRankCursor
set @.StoreNext = @.StoreCurrent
set @.SalesRank = (@.SalesRank + 1)
update Sales_Table
set @.StoreCurrent = org_id
where current of SalesRankCursor
if @.StoreCurrent <> @.StoreNext
begin
set @.SalesRank = 1
end
update Sales_Table
set sales_rank = @.SalesRank
where current of SalesRankCursor
End
CLOSE SalesRankCursor
DEALLOCATE SalesRankCursor
Any ideas?You insert the data ordered, but since the order of the rows are irrelevant to SQL Server, you are not guaranteed to get the rows out in the same order. So, what you basically need is to have the order by in the cursor definition.|||That's what I thought, but how do you order within the cursor definition. When I try an 'order by' statement within the cursor definition, I get a message saying it can only be used in an 'read only' cursor, and my cursor needs to allow an update.|||What I've done for a solution is put a Clustered Index on my source table 'Test Table' on the fields org_id, and org_criteria_value. This appears to have resolved the issue.
I would have preferred to have solved the problem within the cursor, however, so if anyone has a suggestion I would appreciate it.
Thanks.|||What I've done for a solution is put a Clustered Index on my source table 'Test Table' on the fields org_id, and org_criteria_value. This appears to have resolved the issue.
You should be aware that just because it has a clustered index on it, you are not GUARANTEED that the results will be returned in the expected order.
I would have preferred to have solved the problem within the cursor, however, so if anyone has a suggestion I would appreciate it.
Thanks.
Maybe I missed something in the code, but how about something like:
DECLARE SalesRank CURSOR
READ_ONLY
FOR SELECT Store_ID
FROM Stores
ORDER BY Sales DESC
DECLARE @.Store_ID int, @.Counter int
SELECT @.Counter = 1
FETCH NEXT FROM SalesRank INTO @.Store_ID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
UPDATE Stores
SET SalesRank = @.Counter
WHERE Store_ID = @.Store_ID
END
SELECT @.Counter = @.Counter + 1
FETCH NEXT FROM SalesRank INTO @.Store_ID
END
CLOSE SalesRank
DEALLOCATE SalesRank
This would not handle ties or other things, but it should work (with some modification).
Also, there is a RANK function in SQL 2005. That might be an option for you.
Regards,
hmscott|||HMScott,
Let me take a shot at that. I may have to play around with it because I can't have a tie... I'll have to figure out a tie breaker and work from there.
The Clustered IX appeared to solve my problem, but it wasn't the solution I wanted because of lack of certainty. Hopefully the approach you're recommending will work.
Thanks.
I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc.
In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode).
Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed.
/*CREATE Sales_Table table */
CREATE TABLE [dbo].[Sales_Table] (
[count_id] [int] NULL ,
[org_id] [int] NULL ,
[postalcode] [varchar] (20) NULL,
[sales] [numeric](18,6) NULL ,
[sales_rank] [integer] NULL,
[org_criteria_input_date] [datetime] NULL
) ON [PRIMARY]
insert into Sales_Table
select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date
from org_model_data omd
join org o on o.org_id = omd.org_id
where o.client_id = @.ClientID
and model_Criteria_id = 27
and org_criteria_value <> 0
order by omd.org_id asc, omd.org_criteria_value desc
-- DECLARE CURSOR for Sales_Table
declare SalesRankCursor CURSOR
SCROLL dynamic FOR
select org_id, sales, sales_rank
from Sales_Table
for update of sales_rank
-- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values
OPEN SalesRankCursor
while exists (Select * from Sales_Table where sales_rank = 0)
Begin
FETCH NEXT FROM SalesRankCursor
set @.StoreNext = @.StoreCurrent
set @.SalesRank = (@.SalesRank + 1)
update Sales_Table
set @.StoreCurrent = org_id
where current of SalesRankCursor
if @.StoreCurrent <> @.StoreNext
begin
set @.SalesRank = 1
end
update Sales_Table
set sales_rank = @.SalesRank
where current of SalesRankCursor
End
CLOSE SalesRankCursor
DEALLOCATE SalesRankCursor
Any ideas?You insert the data ordered, but since the order of the rows are irrelevant to SQL Server, you are not guaranteed to get the rows out in the same order. So, what you basically need is to have the order by in the cursor definition.|||That's what I thought, but how do you order within the cursor definition. When I try an 'order by' statement within the cursor definition, I get a message saying it can only be used in an 'read only' cursor, and my cursor needs to allow an update.|||What I've done for a solution is put a Clustered Index on my source table 'Test Table' on the fields org_id, and org_criteria_value. This appears to have resolved the issue.
I would have preferred to have solved the problem within the cursor, however, so if anyone has a suggestion I would appreciate it.
Thanks.|||What I've done for a solution is put a Clustered Index on my source table 'Test Table' on the fields org_id, and org_criteria_value. This appears to have resolved the issue.
You should be aware that just because it has a clustered index on it, you are not GUARANTEED that the results will be returned in the expected order.
I would have preferred to have solved the problem within the cursor, however, so if anyone has a suggestion I would appreciate it.
Thanks.
Maybe I missed something in the code, but how about something like:
DECLARE SalesRank CURSOR
READ_ONLY
FOR SELECT Store_ID
FROM Stores
ORDER BY Sales DESC
DECLARE @.Store_ID int, @.Counter int
SELECT @.Counter = 1
FETCH NEXT FROM SalesRank INTO @.Store_ID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
UPDATE Stores
SET SalesRank = @.Counter
WHERE Store_ID = @.Store_ID
END
SELECT @.Counter = @.Counter + 1
FETCH NEXT FROM SalesRank INTO @.Store_ID
END
CLOSE SalesRank
DEALLOCATE SalesRank
This would not handle ties or other things, but it should work (with some modification).
Also, there is a RANK function in SQL 2005. That might be an option for you.
Regards,
hmscott|||HMScott,
Let me take a shot at that. I may have to play around with it because I can't have a tie... I'll have to figure out a tie breaker and work from there.
The Clustered IX appeared to solve my problem, but it wasn't the solution I wanted because of lack of certainty. Hopefully the approach you're recommending will work.
Thanks.
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 inside a cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors
declare Q cursor for
select systudentid from satrans
declare @.id int
open Q
fetch next from Q into @.id
while @.@.fetch_status = 0
begin
declare c cursor for
Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END
From SaTrans , systudent b where satrans.systudentid = b.systudentid
and satrans.systudentid = @.id
declare @.arbalance money, @.type varchar, @.ssn varchar, @.amount money, @.systudentid int, @.transdate datetime, @.sycampusid int, @.before money
set @.arbalance = 0
open c
fetch next from c into @.ssn, @.systudentid, @.transdate, @.type, @.sycampusid, @.amount
while @.@.fetch_status = 0
begin
set @.arbalance = @.arbalance + @.amount
set @.before = @.arbalance -@.amount
insert c2000_utility1..tempbalhistory1
select @.systudentid systudentid, @.sycampusid sycampusid, @.transdate transdate, @.amount amount, @.type type, @.arbalance Arbalance, @.before BeforeBalance
where( convert (int,@.amount) <= -50
or @.amount * -1 > @.before * .02)
and @.type = 'P'
fetch next from c into @.ssn, @.systudentid, @.transdate, @.type, @.sycampusid, @.amount
end
close c
deallocate c
fetch next from Q into @.id
end
close Q
deallocate Q
select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1Did you write this? Or was it given to you?
You don't need a cursor at all for this (although I'm having trouble trying to figure out what you're trying to do)...
Lose Cursor 1 altogethr.
Use the SELECT (which already has the table in it you want) as the Portion of the INSERT Statement...that's it...
Except for this
where( convert (int,@.amount) <= -50
or @.amount * -1 > @.before * .02)
and @.type = 'P'
Which I have no idea what you want.
I expect all you need to do is add that to the select
declare Q cursor for
select systudentid from satrans
declare @.id int
open Q
fetch next from Q into @.id
while @.@.fetch_status = 0
begin
declare c cursor for
Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END
From SaTrans , systudent b where satrans.systudentid = b.systudentid
and satrans.systudentid = @.id
declare @.arbalance money, @.type varchar, @.ssn varchar, @.amount money, @.systudentid int, @.transdate datetime, @.sycampusid int, @.before money
set @.arbalance = 0
open c
fetch next from c into @.ssn, @.systudentid, @.transdate, @.type, @.sycampusid, @.amount
while @.@.fetch_status = 0
begin
set @.arbalance = @.arbalance + @.amount
set @.before = @.arbalance -@.amount
insert c2000_utility1..tempbalhistory1
select @.systudentid systudentid, @.sycampusid sycampusid, @.transdate transdate, @.amount amount, @.type type, @.arbalance Arbalance, @.before BeforeBalance
where( convert (int,@.amount) <= -50
or @.amount * -1 > @.before * .02)
and @.type = 'P'
fetch next from c into @.ssn, @.systudentid, @.transdate, @.type, @.sycampusid, @.amount
end
close c
deallocate c
fetch next from Q into @.id
end
close Q
deallocate Q
select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1Did you write this? Or was it given to you?
You don't need a cursor at all for this (although I'm having trouble trying to figure out what you're trying to do)...
Lose Cursor 1 altogethr.
Use the SELECT (which already has the table in it you want) as the Portion of the INSERT Statement...that's it...
Except for this
where( convert (int,@.amount) <= -50
or @.amount * -1 > @.before * .02)
and @.type = 'P'
Which I have no idea what you want.
I expect all you need to do is add that to the select
Subscribe to:
Posts (Atom)