Return only the latest selection results from a stored procedure
The requirement states: a stored procedure designed to find data based on 5 identifiers. If there is an exact match, return ONLY an exact match, if not, but there is an exact match on non-null parameters, return ONLY these results, otherwise return any match on any 4 non-null parameters ... and so on
My (simplified) code looks like this:
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 may be more sets of selected results, the former are empty and I only want the latter. I know it's easy to get only the last set of results on the application side, but all of our stored procedure calls go through a structure that expects significant results in the first table, and I don't want to change and test all existing SPs.
Is there a way to only return the latest selection results from a stored procedure? Is there a better way to accomplish this task?
a source to share
Use a table variable:
create procedure xxxSearch @a nvarchar(80), @b nvarchar(80)...
as
begin
DECLARE @res TABLE(...)
INSERT INTO @res(...)
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
INSERT INTO @res(...)
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
SELECT ... FROM @res
end
a source to share
You can use a local table variable to store the results and then a SELECT, so there is only one SELECT.
You can repeat your queries (eventually you can get rid of the nesting):
create procedure xxxSearch @a nvarchar(80), @b nvarchar(80)...
as
begin
IF EXISTS (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))... )
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))...
RETURN
END
etc.
end
Or you might find a way to combine all the queries into one query - perhaps with UNION.
a source to share