mgmhicks
asked on
updating a table using stored procedure cursors
I know I can do this in vb code however I'm wondering if a stored procedure could take care of this for me. I have to go through a table and grab the people that moved out this month. This table has no resident information just the apartment information. Now I have all units that had moveouts for the month. Then I need to grab just 1 name out of the occupants table so that the moveout has a name. There may be move then 1 person for that unit in the occupant table however I only need 1. Then I have to go to openitems table and get the open items from this unit. So I basically end up with getting the moveouts with a name and then getting all the open items that this unit is has. (open items are passed due invoices). I would then like to update a temp table that I can use to print a report from. Like I said I know I can do this in code, but I think a stored procedure could handle it just wondering how to do it.
ASKER
sure and thanks for trying to help me. I have a table called leasemoveouts, which has propertyid, unitid, and resiid fields. Property, Unit and resident number. So I have to find all moveouts for a date period. Then there is a openitems table which is linked by propertyid, unitd and resiid. Then there is a occupants table that has the names of the residents, and that linked by propertyid, unitid and resiid. The problem becomes that I only want 1 name coming from occupants table, even though if the unit had 2 or 3 residents each would have the same resiid. But only 1 maybe 2 will have a field called responsible set as 'R' for the names I would like to show on the report. However when I try and just run a sql query to get the data, if the unit has lets say 2 responsible parties from occupants, it would list the open invoice 2 times, over stating the amount that the unit owed when moving out. Hope that helps.
Dear Friend, still this is unclear i would like you to show me as example like this:-
Leasemoveouts
PropertyId UnitId resiid
--------- -------- -------
OpemItmes
PropertyId UnitId resiid
------------ -------- --------
Occupants
Please give propery field names with data as example....
Regards,
dduser
Leasemoveouts
PropertyId UnitId resiid
--------- -------- -------
OpemItmes
PropertyId UnitId resiid
------------ -------- --------
Occupants
Please give propery field names with data as example....
Regards,
dduser
ASKER
Sorry for not getting back, Ok here we go I get all lease moveouts from leasemove out table The info I need from this.
Propertyid UnitID ResiID Moveout Date
BF 100 3 11/30/2006
BF 101 7 11/15/2006
CG 2A 1 11/30/2006
Then I need to get all open invoices from the OpenItems table that belong to these moveout. (There may not be any open invoices.) But they are tied to the moveouts table by Propertyid, unitid, and resID
Propertyid UnitID ResiID OpenBal. Invoice #
BF 100 3 25.00 1234
BF 100 3 200.00 1235
BF 101 7 50.00 1100
Thats easy enough. But now I have to tie a name to resiID. There is a Occupants table that has each listed occupant in that propertyid, unitid. There may be more then 1 resident for the property and unit ID's I only want to get the peope that are the responsible parties. Designated as follows
PropertyID UnitID ResiID Responible FristName LastName
BF 100 3 R Joe Jones
BF 100 3 R Sally JOnes
BF 100 3 N Baby Jones
CG 2A 2 R Tom Smith
BF 101 7 R Dave Johnson
BF 101 7 N Sally Johnson
So I only want the responbile (R) people and because there may be 2 when I tried to do it, it would double the invoices for those people. So my preferrable result would be
Joe Jone / Sally Jones BF 100
invoice 1234 25.00
invoice 1235 200.00
Dave Johnson
invoice 1100 50.00
Hope that helps and thanks again.
Propertyid UnitID ResiID Moveout Date
BF 100 3 11/30/2006
BF 101 7 11/15/2006
CG 2A 1 11/30/2006
Then I need to get all open invoices from the OpenItems table that belong to these moveout. (There may not be any open invoices.) But they are tied to the moveouts table by Propertyid, unitid, and resID
Propertyid UnitID ResiID OpenBal. Invoice #
BF 100 3 25.00 1234
BF 100 3 200.00 1235
BF 101 7 50.00 1100
Thats easy enough. But now I have to tie a name to resiID. There is a Occupants table that has each listed occupant in that propertyid, unitid. There may be more then 1 resident for the property and unit ID's I only want to get the peope that are the responsible parties. Designated as follows
PropertyID UnitID ResiID Responible FristName LastName
BF 100 3 R Joe Jones
BF 100 3 R Sally JOnes
BF 100 3 N Baby Jones
CG 2A 2 R Tom Smith
BF 101 7 R Dave Johnson
BF 101 7 N Sally Johnson
So I only want the responbile (R) people and because there may be 2 when I tried to do it, it would double the invoices for those people. So my preferrable result would be
Joe Jone / Sally Jones BF 100
invoice 1234 25.00
invoice 1235 200.00
Dave Johnson
invoice 1100 50.00
Hope that helps and thanks again.
Hi,
We need to create Stored Procedure to do this:-
Create Procedure TempProc As
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESID AS INT
DECLARE @PROC_UNITID AS INT
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS INT
Create Table #TempTable(Name As Varchar(200),PropertyId as Varchar(2),UnitId as Int,Invoice as Int,OpenBalance as Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(FirstNa
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
INSERT INTO #TEMPTABLE VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
Return
We need to create Stored Procedure to do this:-
Create Procedure TempProc As
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESID AS INT
DECLARE @PROC_UNITID AS INT
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS INT
Create Table #TempTable(Name As Varchar(200),PropertyId as Varchar(2),UnitId as Int,Invoice as Int,OpenBalance as Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(FirstNa
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
INSERT INTO #TEMPTABLE VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
Return
Create Procedure TempProc As
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESID AS INT
DECLARE @PROC_UNITID AS INT
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS INT
Create Table #TempTable(Name As Varchar(200),PropertyId as Varchar(2),UnitId as Int,Invoice as Int,OpenBalance as Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(FirstNa
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
INSERT INTO #TEMPTABLE VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
Select * from #TempTable
Return
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESID AS INT
DECLARE @PROC_UNITID AS INT
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS INT
Create Table #TempTable(Name As Varchar(200),PropertyId as Varchar(2),UnitId as Int,Invoice as Int,OpenBalance as Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(FirstNa
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
INSERT INTO #TEMPTABLE VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
Select * from #TempTable
Return
ASKER
Ok I get this error when putting it into query analizyer
Server: Msg 195, Level 15, State 10, Procedure TempProc, Line 11
'Varchar' is not a recognized function name.
Server: Msg 137, Level 15, State 1, Procedure TempProc, Line 25
Must declare the variable '@PROPERTYID'.
I know what to do about varchar put cant find other one.
thanks
Server: Msg 195, Level 15, State 10, Procedure TempProc, Line 11
'Varchar' is not a recognized function name.
Server: Msg 137, Level 15, State 1, Procedure TempProc, Line 25
Must declare the variable '@PROPERTYID'.
I know what to do about varchar put cant find other one.
thanks
Create Procedure TempProc As
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESID AS INT
DECLARE @PROC_UNITID AS INT
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS INT
Create Table #TempTable(Name As Varchar(200),PropertyId as Varchar(2),UnitId as Int,Invoice as Int,OpenBalance as Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(FirstNa
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
INSERT INTO #TEMPTABLE VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
Select * from #TempTable
Return
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESID AS INT
DECLARE @PROC_UNITID AS INT
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS INT
Create Table #TempTable(Name As Varchar(200),PropertyId as Varchar(2),UnitId as Int,Invoice as Int,OpenBalance as Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(FirstNa
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
INSERT INTO #TEMPTABLE VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
Select * from #TempTable
Return
ASKER
Still keep getting the same error
Server: Msg 102, Level 15, State 1, Procedure TempProc, Line 11
Incorrect syntax near ')'.
Server: Msg 102, Level 15, State 1, Procedure TempProc, Line 11
Incorrect syntax near ')'.
Create Procedure TempProc As
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESID AS INT
DECLARE @PROC_UNITID AS INT
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS INT
Create Table #TempTable(Name Varchar(200),PropertyId Varchar(2),UnitId Int,Invoice Int,OpenBalance Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(FirstNa
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
INSERT INTO #TEMPTABLE VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
Select * from #TempTable
Return
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESID AS INT
DECLARE @PROC_UNITID AS INT
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS INT
Create Table #TempTable(Name Varchar(200),PropertyId Varchar(2),UnitId Int,Invoice Int,OpenBalance Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(FirstNa
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
INSERT INTO #TEMPTABLE VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
Select * from #TempTable
Return
ASKER
Ok, getting this result
Kerri Maus/Sarah Turner B1 1512 S000002599 19
HP * S000003011 14
HP * S000003016 30
WG * S000009758 -20
WG * S000009759 -20
HP * S000003205 14
BW * 0 -100
BW * 0 -400
*** Add this is the error.
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 43
Syntax error converting the varchar value '4108J' to a column of data type int.
Sorry for that. Keep trying we are almost there!!
Thanks
Kerri Maus/Sarah Turner B1 1512 S000002599 19
HP * S000003011 14
HP * S000003016 30
WG * S000009758 -20
WG * S000009759 -20
HP * S000003205 14
BW * 0 -100
BW * 0 -400
*** Add this is the error.
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Line 23
A cursor with the name 'SECONDARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 25
The cursor is already open.
(1 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 43
Syntax error converting the varchar value '4108J' to a column of data type int.
Sorry for that. Keep trying we are almost there!!
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
They are all characters.
thanks again.
thanks again.
ASKER
Here is a copy of your procedure cleaned up so you can just use this. This runs and gives me data,, I am checking for correctness. I will be back at ya. There was error messages I wll paste that as well
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESIID AS INT
DECLARE @PROC_UNITID AS VARCHAR(15)
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS VARCHAR(25)
Create Table TAG_BadDebt(Name Varchar(200),PropertyId Varchar(2),UnitId Varchar(15),Invoice Varchar(25),OpenBalance Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(OccuFir
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
Close SECONDARY_CURSOR
DEALLOCATE SECONDARY_CURSOR
INSERT INTO TAG_BadDebt VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
CLOSE PRIMARY_CURSOR
DEALLOCATE PRIMARY_CURSOR
Select * from TAG_BadDebt
Return
ERRORS I RECEIEVED
Server: Msg 16915, Level 16, State 1, Line 13
A cursor with the name 'PRIMARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 15
The cursor is already open.
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESIID AS INT
DECLARE @PROC_UNITID AS VARCHAR(15)
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS VARCHAR(25)
Create Table TAG_BadDebt(Name Varchar(200),PropertyId Varchar(2),UnitId Varchar(15),Invoice Varchar(25),OpenBalance Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(OccuFir
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
Close SECONDARY_CURSOR
DEALLOCATE SECONDARY_CURSOR
INSERT INTO TAG_BadDebt VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
CLOSE PRIMARY_CURSOR
DEALLOCATE PRIMARY_CURSOR
Select * from TAG_BadDebt
Return
ERRORS I RECEIEVED
Server: Msg 16915, Level 16, State 1, Line 13
A cursor with the name 'PRIMARY_CURSOR' already exists.
Server: Msg 16905, Level 16, State 1, Line 15
The cursor is already open.
I believe it is just that you would have run the previous procedure, giving an error in middle before
"Close Primary Cursor
Deallocate Primary Cursor" happens thats the reason this error is coming try again it would work.....
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESIID AS INT
DECLARE @PROC_UNITID AS VARCHAR(15)
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS VARCHAR(25)
Create Table #TAG_BadDebt(Name Varchar(200),PropertyId Varchar(2),UnitId Varchar(15),Invoice Varchar(25),OpenBalance Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(OccuFir
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
Close SECONDARY_CURSOR
DEALLOCATE SECONDARY_CURSOR
INSERT INTO TAG_BadDebt VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
CLOSE PRIMARY_CURSOR
DEALLOCATE PRIMARY_CURSOR
Select * from #TAG_BadDebt
Return
Regards,
dduser
"Close Primary Cursor
Deallocate Primary Cursor" happens thats the reason this error is coming try again it would work.....
DECLARE @PROC_PROPERTYID AS VARCHAR(2)
DECLARE @PROC_RESIID AS INT
DECLARE @PROC_UNITID AS VARCHAR(15)
DECLARE @PROC_OPENBAL AS FLOAT
DECLARE @PROC_NAME AS VARCHAR(200)
DECLARE @PROC_TNAME AS VARCHAR(200)
DECLARE @PROC_INVOICE AS VARCHAR(25)
Create Table #TAG_BadDebt(Name Varchar(200),PropertyId Varchar(2),UnitId Varchar(15),Invoice Varchar(25),OpenBalance Float)
DECLARE PRIMARY_CURSOR CURSOR FOR
Select a.PropertyId,a.UnitId,a.Re
OPEN PRIMARY_CURSOR
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROC_TNAME = ''
DECLARE SECONDARY_CURSOR CURSOR FOR
SELECT LTRIM(RTRIM(IsNull(OccuFir
OPEN SECONDARY_CURSOR
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@PROC_TNAME = '')
SET @PROC_TNAME = @PROC_NAME
ELSE
SET @PROC_TNAME = @PROC_TNAME + '/' + @PROC_NAME
FETCH NEXT FROM SECONDARY_CURSOR
INTO @PROC_NAME
END
Close SECONDARY_CURSOR
DEALLOCATE SECONDARY_CURSOR
INSERT INTO TAG_BadDebt VALUES(@PROC_TNAME,@PROC_P
FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNI
END
CLOSE PRIMARY_CURSOR
DEALLOCATE PRIMARY_CURSOR
Select * from #TAG_BadDebt
Return
Regards,
dduser
Regards,
dduser