I'm writing stored procs that are being called by a legacy system. One of the constraints of the legacy system is that there must be at least one row in the single result set returned from the stored proc. The standard is to return a zero in the first column (yes, I know!).
The obvious way to achieve this is create a temp table, put the results into it, test for any rows in the temp table and either return the results from the temp table or the single empty result.
Another way might be to do an EXISTS against the same where clause that's in the main query before the main query is executed.
Neither of these are very satisfying. Can anyone think of a better way. I was thinking down the lines of a UNION kind of like this (I'm aware this doesn't work):
--create table #test
-- id int identity,
-- category varchar(10)
--insert #test values ('A')
--insert #test values ('B')
--insert #test values ('C')
declare @category varchar(10)
set @category = 'D'
where category = @category
where @@rowcount = 0
Very few options I'm afraid.
You always have to touch the table twice, whether COUNT, EXISTS before, EXISTs in UNION, TOP clause etc
select id, category from mytable where category = @category union all --edit, of course it's quicker select 0, '' where NOT EXISTS (SELECT * FROM mytable where category = @category)
An EXISTS solution is better then COUNT because it will stop when it finds a row. COUNT will traverse all rows to actually count them