stored procedure & blank return value/output

[oalex]

I have a stored procedure to query a db by a given zipcode range. works just fine and returns selected records as well as a return value and some output values to be posted on web (asp).
Now, within the stored procedure i want to check if @@rowcount < 10. if true, i need to increase zipcode range and re-query db (ea while @@rowcount <10).
as soon as procedure re-querys, i will get a returned recordset, but return value and output values are blank.
does anybody have an idea? can i do that at all?

 

[SQLSister]

Post your code and maybe we can help. You certainly should be able to do what you describe.

 

[oalex]

Thanks, SQLSister, for the quick response. here's the source:

PROCEDURE1::

CREATE PROCEDURE p_procStat
@intAccountID int,
@intSessionID int,
@intZIPCode int,
@intMilesRange int,
@chrVehType int, @intVehModlYR int,
@chrVehMake char(15),
@chrVehSeries char(30),
@chrVehSubSeries char(25),
@intReportIDOut int OUTPUT

AS
DECLARE @err smallInt
DECLARE @rows int

--// log report process/initialization --> return reportID
--exec p_insertReportLog @intAccountID, @intSessionID, @intZIPCode, @intVehModlYR, @chrVehMake, @chrVehSeries, @chrVehSubSeries, @intReportIDOut OUTPUT
--bypass proc for testing
SELECT @intReportIDOut=123456

if @intReportIDOut > 0
BEGIN
--// get report data
exec p_getReportData @intZIPCode , @intMilesRange, @intVehModlYR, @chrVehMake, @chrVehSeries, @chrVehSubSeries, @rows OUTPUT
if @rows < 10
Set @err=115 --not enough recs
else
set @err=100 --result ok
END
ELSE
set @err=200 -- err in rep id
RETURN(@err)
GO
-----------------------------------------------------------
PROCEDURE2::
CREATE PROCEDURE p_getReportData
@intZIPCode int,
@intMiles int,
@intVehModlYR int,
@chrVehMake char(15),
@chrVehSeries char(30),
@chrVehSubSeries char(30),
@rows int OUTPUT

AS

DECLARE @chrLatSM char(13)
DECLARE @chrLonSM char(13)
DECLARE @ftLatMin float
DECLARE @ftLatMax float
DECLARE @ftLonMin float
DECLARE @ftLonMax float
DECLARE @btDataQualified bit
SELECT @btDataQualified = 0
DECLARE @intRangeRadius int
SELECT @intRangeRadius = 0 -- intial miles (radius) = 0 miles

DECLARE @chrLongitude char(13)
DECLARE @chrLatitude char(13)

--// initiate report request and write to log
--//
--// get latitude/longitude from selected ZIP
exec csp_getZIPCoordinates @intZIPCode, @chrLatitude OUTPUT, @chrLongitude OUTPUT
--//get degree to 1 statute mile values (lon/lat)
exec csp_calcDegree2StatuteMile @chrLatitude, @chrLongitude, @chrLatSM OUTPUT, @chrLonSM OUTPUT

-- >> start looping unitll qualified amount reached
while (@btDataQualified = 0)
BEGIN
--// set lat/lon range
Select @intRangeRadius = @intRangeRadius + @intMiles
set @ftLatMin = cast(@chrLatitude as float) - ( cast(@intRangeRadius as float) * cast(@chrLatSM as float) )
set @ftLatMax = cast(@chrLatitude as float) + ( cast(@intRangeRadius as float) * cast(@chrLatSM as float) )
set @ftLonMin = cast(@chrLongitude as float) - ( cast(@intRangeRadius as float) * cast(@chrLonSM as float) )
set @ftLonMax = cast(@chrLongitude as float) + ( cast(@intRangeRadius as float) * cast(@chrLonSM as float) )
--// query data
--exec p_queryReportData @ftLatMin, @ftLatMax, @ftLonMin, @ftLonMax, @intVehModlYR, @chrVehMake, @chrVehSeries, @chrVehSubSeries, @rows OUTPUT
SELECT * from uveMaster
where OWNRZPCD in (
select ZIP from usZIP where
LATITUDE > cast(@ftLatMin as float) and
LATITUDE < cast(@ftLatMax as float) and
LONGITUDE > cast(@ftLonMin as float) and
LONGITUDE < cast(@ftLonMax as float)
)
and VEHMODLYR = @intVehModlYR
and VEHMAKE = @chrVehMake
and VEHSERIES = @chrVehSeries
and VEHSUBSERIES = @chrVehSubSeries
--// end select
Select @rows=@@rowcount


if ( @intRangeRadius >250 ) -- declare maximum radius
BEGIN
Select @btDataQualified=1
END
ELSE
BEGIN
if @rows > 10
Select @btDataQualified=1
else
BEGIN
-- --// increase range
Select @intRangeRadius = @intRangeRadius + @intMiles
END
END
END
GO