Show results from query if results exist without running the same query again

Question

I want to have a stored procedure that will take one SerialNumber nvarchar as it's input and check several databases to see if that serial number exists and if it does exist then return the result of the query, otherwise move onto the next database and do the same thing until all databases have been checked.

Current pseudocode:

IF(exists(select top 1 * from Server1.Database1.Table where num = @SerialNumberInput))
BEGIN
    select top 1 * from Server1.Database1.Table where num = @SerialNumberInput
END ELSE
    IF(exists(select top 1 * from Server2.Database2.Table where num = @SerialNumberInput))
BEGIN
    select top 1 * from Server2.Database2.Table where num = @SerialNumberInput
END ELSE
--Server3.Database3
--Server4.Database4
--etc...

But I don't like all this query repetition and I don't like how I'm having to make a call to the server twice by calling the same query twice. I could save the result to a table variable and just check that but that feels hacky.

In your example "Server1" is a database name. Is that really the case or are you attempting to reference tables in linked servers? — SMor, Nov 28 '18 at 19:59
I wouldn’t regard the @table route as a hack.. `insert select top1`.. and if the rowcount was 1, select from the table var — Caius Jard, Nov 28 '18 at 20:03
Table variable must reflect source table, but if your tables have different structure, then, as I see it, you need to query two times - for getting the count of rows and getting actual rows. — JohnyL, Nov 28 '18 at 20:17

S3S · Accepted Answer · 2018-11-28T20:39:52.560

Too long to comment.

But I don't like all this query repetition

Me neither, but for this case, it's the cleanest method or most readable IMHO.

I don't like how I'm having to make a call to the server twice by calling the same query twice.

You aren't, at least not exactly. EXISTS returns a BOOLEAN value so as long as there is an INDEX on your predicate, it should be pretty fast. The second query, where you are returning the first row with all of the columns would be slightly slower. Also, you don't need top 1 * in the EXISTS unless you just like that. You can use SELECT 1 or anything since the result is BOOLEAN.

Another thing is you are using TOP without and ORDER BY which means you don't care what row is returned, and are OK with that row being different (potentially) each time you execute this. More on that in this blog.

If you really want to not use EXISTS, you can break this up using @@ROWCOUNT.

select top 1 * from Server1.Database1.Table where num = @SerialNumberInput
if @@ROWCOUNT = 1
   return
else
select top 1 * from Server2.Database2.Table where num = @SerialNumberInput
if @@ROWCOUNT = 1
   return
else
...

Or, if the schema is the same and you don't want NULL datasets... something like you said with the table variable.

create table #Temp(...)

insert into #Temp
select top 1 * from Server1.Database1.Table where num = @SerialNumberInput
if @@ROWCOUNT = 1
    select * from #Temp
    return
else
insert into #Temp
select top 1 * from Server2.Database2.Table where num = @SerialNumberInput
if @@ROWCOUNT = 1
    select * from #Temp
    return
else
...

Since you are only inserting a single row, it'd be pretty quick. Larger datasets would naturally take longer.

+1 Thanks for the insights. And to your query example: won't that return a NULL dataset every time until it returns one with a row? — LCIII, Nov 28 '18 at 20:33
Yeah it would that that may not be wanted. I thought you were just trying to short circuit so the other queries didn't run. If the schema is identical then you could `select into #temp` and thus, when it returns you'll just have 1 row. — S3S, Nov 28 '18 at 20:36

Show results from query if results exist without running the same query again

1 Answers1