Great, it worked, but can you explain to me exactly what's going on here:

isnull(@franchise,'-999')=case when @franchise is not null then lis_franchise else '-999' end

What does isnull(@franchise,'-999')= mean and what...

Matt, I get a "Conversion failed when converting the varchar value 'some franchise' to data type int." error with your case statement. Again I like to keep the solution using...

Thanks Ramesh, works out great.

I have also been trying something like:

select * from Listings where lis_name=@lis_name

(

case when @franchise is not null then ' AND lis_franchise=@lis_franchise'

)

What I want...

The conversion caused the radius value to lose precision:

declare @sql nvarchar(4000)

declare @radius float

set @radius = -0.256712789599778

set @sql='SELECT lis_id, distance = ' + cast(@radius as varchar(20)) + ' FROM Listings'

EXEC sp_executesql...

Sorry, I don't quite follow. How do I save the following to another CTE for use in another recursive call:

with Recurse_1 as

(

-- select statement

)

select * From Recurse_1