Multiple select - SQL Server Forums

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

Transact-SQL (2000)

Multiple select

Author

Topic

waxdart23
Starting Member

33 Posts

Posted - 2005-07-08 : 08:40:16

I am using the follwing code in a stored procedure to pull in data using another stored procedure:


DECLARE @associate_no int
SELECT 	@associate_no = (select associate_no from tx_xref where category = 'E' and type = 4 and customer_no = @customer_no)

Insert	#t1(log_date, log_user, log_activity_type, log_contact_type, log_notes, log_amount, log_activity_no)
Exec 	lp_customer_log @customer_no = @associate_no

This doesnt work when the "select associate_no from tx_xref..." statement returns more than one value. Is there way to loop this so that the lp_customer_log stored procedure is run for every record returned in this statement?

Many thanks in advance...

Thanks
P

iminore
Posting Yak Master

141 Posts

Posted - 2005-07-08 : 08:46:08

Assuming associate_no is unique in ts_ref:

DECLARE @associate_no int
SET @associate_no = (select min(associate_no) from tx_xref where category = 'E' and type = 4 and customer_no = @customer_no)
while @associate_no is not null
begin
Insert #t1(log_date, log_user, log_activity_type, log_contact_type, log_notes,log_amount, log_activity_no)
Exec lp_customer_log @customer_no = @associate_no
SET @associate_no = (select min(associate_no) from tx_xref where category = 'E' and type = 4 and customer_no = @customer_no and associate_no > @associate_no)
end