Solved

updating a table using stored procedure cursors

Posted on 2006-11-22
15
294 Views
Last Modified: 2008-07-03
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.

0
Comment
Question by:mgmhicks
  • 8
  • 7
15 Comments
 
LVL 9

Expert Comment

by:dduser
ID: 17996762
Can you give this with example as Data, it would be helpfule???

Regards,

dduser
0
 

Author Comment

by:mgmhicks
ID: 17998780
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.

0
 
LVL 9

Expert Comment

by:dduser
ID: 18000858
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
0
 

Author Comment

by:mgmhicks
ID: 18045273
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.

               
0
 
LVL 9

Expert Comment

by:dduser
ID: 18047283
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.ResId,b.[OpenBal.],b.[Invoice #] from leasmoveout as a inner join openitems as b on a.PropertyId = b.PropertyId and a.UnitId = b.UnitId and a.ResId = b.Resid

OPEN PRIMARY_CURSOR

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE
WHILE @@FETCH_STATUS = 0
BEGIN

      SET @PROC_TNAME = ''
      DECLARE SECONDARY_CURSOR CURSOR FOR
      SELECT LTRIM(RTRIM(IsNull(FirstName,'') + ' ' + IsNull(LastName,''))) from Occupants where Resposbile = 'R' AND PROPERTYID = @PROPERTYID AND UNITID = @UNITID AND RESID = @RESID

      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_PROPERTYID,@PROC_UNITID,@PROC_INVOICE,@PROC_OPENBAL)

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE

END

Return
0
 
LVL 9

Expert Comment

by:dduser
ID: 18047303
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.ResId,b.[OpenBal.],b.[Invoice #] from leasmoveout as a inner join openitems as b on a.PropertyId = b.PropertyId and a.UnitId = b.UnitId and a.ResId = b.Resid

OPEN PRIMARY_CURSOR

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE
WHILE @@FETCH_STATUS = 0
BEGIN

      SET @PROC_TNAME = ''
      DECLARE SECONDARY_CURSOR CURSOR FOR
      SELECT LTRIM(RTRIM(IsNull(FirstName,'') + ' ' + IsNull(LastName,''))) from Occupants where Resposbile = 'R' AND PROPERTYID = @PROPERTYID AND UNITID = @UNITID AND RESID = @RESID

      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_PROPERTYID,@PROC_UNITID,@PROC_INVOICE,@PROC_OPENBAL)

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE

END

Select * from #TempTable

Return
0
 

Author Comment

by:mgmhicks
ID: 18047562
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 9

Expert Comment

by:dduser
ID: 18047697
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.ResId,b.[OpenBal.],b.[Invoice #] from leasmoveout as a inner join openitems as b on a.PropertyId = b.PropertyId and a.UnitId = b.UnitId and a.ResId = b.Resid

OPEN PRIMARY_CURSOR

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE
WHILE @@FETCH_STATUS = 0
BEGIN

      SET @PROC_TNAME = ''
      DECLARE SECONDARY_CURSOR CURSOR FOR
      SELECT LTRIM(RTRIM(IsNull(FirstName,'') + ' ' + IsNull(LastName,''))) from Occupants where Resposbile = 'R' AND PROPERTYID = @PROC_PROPERTYID AND UNITID = @PROC_UNITID AND RESID = @PROC_RESID

      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_PROPERTYID,@PROC_UNITID,@PROC_INVOICE,@PROC_OPENBAL)

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE

END

Select * from #TempTable

Return
0
 

Author Comment

by:mgmhicks
ID: 18053822
Still keep getting the same error

Server: Msg 102, Level 15, State 1, Procedure TempProc, Line 11
Incorrect syntax near ')'.
0
 
LVL 9

Expert Comment

by:dduser
ID: 18055076
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.ResId,b.[OpenBal.],b.[Invoice #] from leasmoveout as a inner join openitems as b on a.PropertyId = b.PropertyId and a.UnitId = b.UnitId and a.ResId = b.Resid

OPEN PRIMARY_CURSOR

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE
WHILE @@FETCH_STATUS = 0
BEGIN

      SET @PROC_TNAME = ''
      DECLARE SECONDARY_CURSOR CURSOR FOR
      SELECT LTRIM(RTRIM(IsNull(FirstName,'') + ' ' + IsNull(LastName,''))) from Occupants where Resposbile = 'R' AND PROPERTYID = @PROC_PROPERTYID AND UNITID = @PROC_UNITID AND RESID = @PROC_RESID

      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_PROPERTYID,@PROC_UNITID,@PROC_INVOICE,@PROC_OPENBAL)

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE

END

Select * from #TempTable

Return
0
 

Author Comment

by:mgmhicks
ID: 18060069
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

0
 
LVL 9

Accepted Solution

by:
dduser earned 500 total points
ID: 18062222
Below is the procedure with some changes, i would like to know whether ProprertyId,UnitId,ResId,Invoice are Int or any of this are Characters???


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.ResId,b.[OpenBal.],b.[Invoice #] from leasmoveout as a inner join openitems as b on a.PropertyId = b.PropertyId and a.UnitId = b.UnitId and a.ResId = b.Resid

OPEN PRIMARY_CURSOR

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE
WHILE @@FETCH_STATUS = 0
BEGIN

      SET @PROC_TNAME = ''
      DECLARE SECONDARY_CURSOR CURSOR FOR
      SELECT LTRIM(RTRIM(IsNull(FirstName,'') + ' ' + IsNull(LastName,''))) from Occupants where Resposbile = 'R' AND PROPERTYID = @PROC_PROPERTYID AND UNITID = @PROC_UNITID AND RESID = @PROC_RESID

      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 #TEMPTABLE VALUES(@PROC_TNAME,@PROC_PROPERTYID,@PROC_UNITID,@PROC_INVOICE,@PROC_OPENBAL)

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESID,@PROC_OPENBAL,@PROC_INVOICE

END

CLOSE PRIMARY_CURSOR
DEALLOCATE PRIMARY_CURSOR

Select * from #TempTable

Return

0
 

Author Comment

by:mgmhicks
ID: 18068644
They are all characters.

thanks again.
0
 

Author Comment

by:mgmhicks
ID: 18068756
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.ResiId,b.[BalDue],b.[BilledInvoiceNo] from leasemoveout as a inner join openitems as b on a.PropertyId = b.PropertyId and a.UnitId = b.UnitId and a.ResiId = b.Resiid

OPEN PRIMARY_CURSOR

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESIID,@PROC_OPENBAL,@PROC_INVOICE
WHILE @@FETCH_STATUS = 0
BEGIN

      SET @PROC_TNAME = ''
      DECLARE SECONDARY_CURSOR CURSOR FOR
      SELECT LTRIM(RTRIM(IsNull(OccuFirstName,'') + ' ' + IsNull(OccuLastName,''))) from OccupantHeader where ResponsibleFlag = 'R' AND PROPERTYID = @PROC_PROPERTYID AND UNITID = @PROC_UNITID AND RESIID = @PROC_RESIID

      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_PROPERTYID,@PROC_UNITID,@PROC_INVOICE,@PROC_OPENBAL)

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESIID,@PROC_OPENBAL,@PROC_INVOICE

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.
0
 
LVL 9

Expert Comment

by:dduser
ID: 18069566
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.ResiId,b.[BalDue],b.[BilledInvoiceNo] from leasemoveout as a inner join openitems as b on a.PropertyId = b.PropertyId and a.UnitId = b.UnitId and a.ResiId = b.Resiid

OPEN PRIMARY_CURSOR

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESIID,@PROC_OPENBAL,@PROC_INVOICE
WHILE @@FETCH_STATUS = 0
BEGIN

      SET @PROC_TNAME = ''
      DECLARE SECONDARY_CURSOR CURSOR FOR
      SELECT LTRIM(RTRIM(IsNull(OccuFirstName,'') + ' ' + IsNull(OccuLastName,''))) from OccupantHeader where ResponsibleFlag = 'R' AND PROPERTYID = @PROC_PROPERTYID AND UNITID = @PROC_UNITID AND RESIID = @PROC_RESIID

      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_PROPERTYID,@PROC_UNITID,@PROC_INVOICE,@PROC_OPENBAL)

FETCH NEXT FROM PRIMARY_CURSOR
INTO @PROC_PROPERTYID,@PROC_UNITID,@PROC_RESIID,@PROC_OPENBAL,@PROC_INVOICE

END

CLOSE PRIMARY_CURSOR
DEALLOCATE PRIMARY_CURSOR

Select * from #TAG_BadDebt

Return

Regards,

dduser
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now