return only the last select results from stored procedure
- by Madalina Dragomir
The requirement says: stored procedure meant to search data, based on 5 identifiers. If there is an exact match return ONLY the exact match, if not but there is an exact match on the not null parameters return ONLY these results, otherwise return any match on any 4 not null parameters... and so on
My (simplified) code looks like:
create procedure xxxSearch @a nvarchar(80), @b nvarchar(80)...
as
begin
select whatever
from MyTable t
where ((@a is null and t.a is null) or (@a = t.a)) and
((@b is null and t.b is null) or (@b = t.b))...
if @@ROWCOUNT = 0
begin
select whatever
from MyTable t
where ((@a is null) or (@a = t.a)) and
((@b is null) or (@b = t.b))...
if @@ROWCOUNT = 0
begin
...
end
end
end
As a result there can be more sets of results selected, the first ones empty and I only need the last one.
I know that it is easy to get the only the last result set on the application side, but all our stored procedure calls go through a framework that expects the significant results in the first table and I'm not eager to change it and test all the existing SPs.
Is there a way to return only the last select results from a stored procedure?
Is there a better way to do this task ?
