looping thru a recordset in a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

looping thru a recordset in a SP

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-10-08 : 17:47:00

Here's my SP. Those between *** are the parts I don't know how to code. Will someone help pls?

CREATE PROCEDURE CW_TotalNOTrans
@icn varchar(4)
as

SET NOCOUNT ON
-- create temp table
CREATE TABLE #Temp
(
ID int IDENTITY,
txtName varchar(30),
txtICN varchar(4),
txtBusUnit varchar(10),
intTotal int,
)

DECLARE @cycle datetime

-- get latest cycle date for the ICN
SELECT @cycle = MAX(dteCycleDate)
FROM dbo.CycleDateInfo
WHERE txtInternetNum = @icn

INSERT INTO #Temp (txtName, txtICN, txtBusUnit)
SELECT txtName, txtInternetCompNum, txtAccountingCode
FROM Account
WHERE txtInternetCompNum = @icn

******
For each txtaccounting code
count the number of transactions from another table
based on icn, and accounting code and then
insert into temp table as intTotal
******

DROP TABLE #Temp
SET NOCOUNT OFF

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-08 : 18:17:12

How about this. In the last INSERT INTO...SELECT you posted, try something like this


INSERT INTO #Temp (txtName, txtICN, txtBusUnit, intTotal ) 
SELECT a.txtName, a.txtInternetCompNum, a.txtAccountingCode,
(SELECT COUNT(*) FROM Another_Table at WHERE at.ICn = @ICN AND at.AccountingCode = a.AccountingCode)
FROM Account a
WHERE txtInternetCompNum = @icn

Tough to read, but something like that should work.
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

PeterG
Posting Yak Master

156 Posts

Posted - 2002-10-08 : 19:31:19

Thanks Michael P.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources