May 4, 2004 at 12:08 pm
I have a calling procedure that loops through 5 possible searches to produce a result set. Based on what I get back from the procedure I'm calling either I will keep trying or break out of the loop if rows are returned. I don't want to return anything if no rows are returned until all of the searches have been tried, especially a print statement. How can I suppress a return of 1 until all attempts have been exausted?
Here is the calling procedure snippet:
IF (@intRetVal = 1)
BEGIN
--Print 'No Records found'
Break
end
IF (@intRetVal = 0)
Begin
--BREAK
end
Here is the code in the procedure I'm calling:
IF ( SELECT COUNT(*) FROM #Return ) = 0
Begin
Return 1
End
IF ( SELECT COUNT(*) FROM #Return ) <> 0
Begin
SELECT vchReturnValue
FROM #Return
ORDER BY ret_id
end
return 0
May 4, 2004 at 3:18 pm
Here is the whole script for what will be the calling procedure. Area of interest is in red. This might clear things up so I cn get a reply.
DECLARE @counter INT
DECLARE @int_sche_id INT
DECLARE @Count_Total INT
DECLARE @srch_id INT
Declare @cscd_srch_string varchar(500)
DECLARE @vchSQLStmt varchar(1000)
DECLARE @intRetVal INT
Select @counter = 0
SELECT @srch_id = MIN(sa.srea_srch_id)
From srch_elmt_assoc sa
Join ##Srch_val s on sa.srea_sche_id = s.srch_val_id
Join search sr on sa.srea_srch_id = sr.srch_id
Where sa.srea_priority = 1
and s.srch_val is not null
and sa.srea_srch_id <> 6
Set @cscd_srch_string = ''
WHILE @srch_id IS NOT NULL --Start of the loop
BEGIN
Select @cscd_srch_string = @cscd_srch_string+srch_val+Char(9)
from ##srch_val
JOIN srch_elmt_assoc on srch_val_id = srea_sche_id
Where srch_val_id in (Select srch_val_id from ##Srch_val where srch_val IS NOT Null)
AND srea_srch_id = @srch_id
Set @cscd_srch_string = LEFT(@cscd_srch_string,LEN(@cscd_srch_string)-LEN(CHAR(9)))--Get rid of the trailing tab
exec @intRetVal = RHD_OMAP..rhd_search_source @srch_id, @cscd_srch_string
--Don't know what to do here?
IF (@intRetVal = 0)
begin
Continue
Else
Break
end
SET @counter = @counter + 1
SELECT @srch_id = MIN(sa.srea_srch_id)
From srch_elmt_assoc sa
Join ##Srch_val s on sa.srea_sche_id = s.srch_val_id
Join search sr on sa.srea_srch_id = sr.srch_id
Where sa.srea_priority = 1
and s.srch_val is not null
and sa.srea_srch_id <> 6
and sa.srea_srch_id > @srch_id
Set @cscd_srch_string = ''
END
May 4, 2004 at 10:53 pm
Ended up figuring it out myself. Changes are in red.
WHILE @counter <= @Max_srch_id
BEGIN
Select @cscd_srch_string = @cscd_srch_string+srch_val+Char(9)
from ##srch_val
JOIN srch_elmt_assoc on srch_val_id = srea_sche_id
Where srch_val_id in (Select srch_val_id from ##Srch_val where srch_val IS NOT Null)
AND srea_srch_id = @srch_id
IF @cscd_srch_string = ''
BREAK
Set @cscd_srch_string = LEFT(@cscd_srch_string,LEN(@cscd_srch_string)-1)--Get rid of the trailing tab
--Print @srch_id
exec @intRetVal = RHD_OMAP..rhd_search_source @srch_id, @cscd_srch_string
IF (@intRetVal <> 1)
BEGIN
BREAK
END
SET @counter = @counter + 1
SELECT @srch_id = MIN(sa.srea_srch_id)
From srch_elmt_assoc sa
Join ##Srch_val s on sa.srea_sche_id = s.srch_val_id
Join search sr on sa.srea_srch_id = sr.srch_id
Where sa.srea_priority = 1
and s.srch_val is not null
and sa.srea_srch_id <> 6
and sa.srea_srch_id > @srch_id
Set @cscd_srch_string = ''
END
IF (@intRetVal = 1) --Placed error return outside of the loop.
Print 'No Records found'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply