Hi all,
I have the following in a Direct DBMS action:
//begin SQL
declare @phone_id int
declare @manu varchar(50)
declare @phone_name varchar(50)
declare @carrier_id int
declare @carrier varchar(50)
declare @rownum_phones int
declare phonelist cursor for
SELECT DISTINCT
b1.phone_id,b2.manufacturer,b1.phone_name,b3.carrier_id,c1.carrier
FROM dbo.bc_phone b1,dbo.bc_manufacturer b2,dbo.bc_pmc2
b3,dbo.bc_marketxdevice b4, dbo.bc_carrier c1 WHERE
((b3.status1='approved1' OR b3.status1='failed1') AND (b4.market_id=1)
AND ((b1.manufacturer_id=b2.manufacturer_id) AND
(b3.phone_id=b1.phone_id) AND (b4.phone_id=b3.phone_id) AND
(b3.carrier_id = c1.carrier_id))
open phonelist
fetch next from phonelist into
@phone_id,@manu,@phone_name,@carrier_id,@carrier
set @rownum_phones = 0
while <@literal value="@@FETCH_STATUS"> = 0
begin
set @rownum_phones = @rownum_phones + 1
if (SELECT COUNT(b1.phone_id) FROM dbo.bc_pvc
b1,dbo.bc_carrier b2,dbo.bc_pmc2 b3 WHERE (b1.phone_...@phone_id AND
b1.carrier_...@carrier_id) AND ((b2.carrier_id=b1.carrier_id) AND
(b3.phone_id=b1.phone_id))) = 0
select @manu,@phone_name,@carrier
fetch next from phonelist into
@phone_id,@manu,@phone_name,@carrier_id,@carrier
end
close phonelist
deallocate phonelist
//end SQL
The SQL call returns a list (phonelist) and then loops through that
list to find if some conditions are met. If so (if zero records are
returned) then I simply "print" the variables using a select call. So,
Witango stores the first return returned value, but no more. I need to
get all of them.
I tried putting in a UNION and UNION ALL beneath the "select
@manu,@phone_name,@carrier", but I got errors (presumably because I
was putting the UNION after the last cycle?).
Any ideas on how I can get all of the results? By the way, I was doing
this using Witango (search, then loop, then test for zero, then
addrows to an array) but it was taking WAY too long.
MS SQL Server 2005.
Any help would be greatly, greatly appreciated.
Thanks!
Brian Humes
Director, Interactive
JohnsonRauhoff
269-428-9257 (direct)
269-428-3377 (main)
269-428-3312 (fax)
www.johnson-rauhoff.com
bhu...@johnson-rauhoff.com
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
