Return result of select query only if not empty

Question

Working with Sql Server. Writing a stored procedure. Here is the pseudocode for what I want to achieve:

    IF EXISTS ( SELECT  field1
                FROM    t1
                WHERE   field1 = ... AND field2 = ...) 
        BEGIN
            SELECT  field1
                FROM    t1
                WHERE   field1 = ... AND field2 = ...
        END

any better way of doing this? Any help appreciated.

Chirayu

Update: The problem is that the same query is executed twice. I cannot also just the run query once and return null (if the result is null i would like to return an alternative result).

Then just run the second query - if nothing exists, `NULL` will be returned — marc_s, Aug 22 '11 at 17:26
@marc_s Well actually an empty resultset will be returned in that case. Maybe a nit-pick but you won't be able to check the resulting column for `IS NULL` for example - there is no spoon. — Aaron Bertrand, Aug 22 '11 at 17:41
@Chirayu: If the EXISTS condition returns false, do you want to exit the procedure or do another query in the else case? Can you elaborate on what the goal is? From your example, it looks like you just want to exit the procedure if the EXISTS condition returns false. — James Johnson, Aug 22 '11 at 18:44
Regarding your update: If the "alternative result" does not have the same structure (number of columns and their names, data types, etc) then @Remus Rusanu's answer would still apply i.e. "would be a nightmare to use from any API" If the structure is the same then you should be able to write one query e.g. `UNION` the two results together. However, it is hard to generalize without seeing the queries involved other result. — onedaywhen, Aug 23 '11 at 08:09

samaspin · Answer 1 · 2012-09-20T08:23:06.530

I have done this before using a CTE and table variable, it requires more lines of code but the query is only written once, therefore your logic exists in a single place.

DECLARE @Results TABLE (Result INT);
WITH ResultsCTE AS
(
    --Your query goes here 
    SELECT  1 as Result 
    WHERE 1 = 1
)
INSERT INTO @Results
SELECT Result
FROM ResultsCTE

IF (SELECT COUNT(*) FROM @Results) > 0
BEGIN
    SELECT * FROM @Results
END
ELSE BEGIN
    SELECT 'Do Something Else or Do Nothing!'
END

score 3 · Answer 2 · answered Aug 22 '11 at 17:27

3

You could check @@ROWCOUNT after running the query once to determine whether or not to return the value:

http://msdn.microsoft.com/en-us/library/ms187316.aspx

answered Aug 22 '11 at 17:27

Chris B. Behrens

6,255
8
45
71

is there any way to do it without executing the same query twice? – Chirayu Shishodiya Aug 22 '11 at 17:28
Sure - you capture the value from the first query execution in a variable, then check @@rowcount > 0. If it's true, return the variable, otherwise do whatever your else condition is. – Chris B. Behrens Aug 22 '11 at 17:30
do i use select into to capture the result? – Chirayu Shishodiya Aug 22 '11 at 17:33
No, you use `DECLARE @field1 ; SELECT @field1 = field1 FROM t1 ......; IF @@ROWCOUNT > 0 SELECT @field1;` ... however it seems like your application is going to have to check for the presence of a result, and it seems more appropriate to just provide an empty result set rather than not provide a result set at all. How is the app going to check whether there is a result set at all? – Aaron Bertrand Aug 22 '11 at 17:40
@Aaron - If I had to guess I think he is going to check several conditions and only wants to return one result set, for which ever one has results – JNK Aug 22 '11 at 17:43
@JNK Well hopefully the OP will state the actual requirement rather than have us guess their ultimate need. :-) – Aaron Bertrand Aug 22 '11 at 17:46
@aaron - It's so much more fun to guess though! – JNK Aug 22 '11 at 17:47

score 2 · Answer 3 · answered Aug 22 '11 at 17:28

2

If the select doesn't yield any results, no results will be returned. I don't see any reason to use a condition here, unless I'm missing something...

answered Aug 22 '11 at 17:28

James Johnson

45,496
8
73
110

An empty resultset is returned, This is different than no resultset. I can't speak for the OP, but it's not unreasonable to have an application that depends on that difference. – Michael J Swart Aug 22 '11 at 18:04
@Michael J Stewart: In cases where results are returned from the procedure, that extra condition means that two queries have to be executed instead of one, which will ultimately diminish performance. When there are no results a query is still executed anyway, so there's no gain there either. By just doing a select and letting it return an empty result set, the procedure only has to execute one query whether or not results are returned. – James Johnson Aug 22 '11 at 18:24
(It's Swart) There's no performance gains for sure, but logically it's different. It's clear from the title, the question and the update to the question that the OP does not want to return an empty resultset. – Michael J Swart Aug 22 '11 at 18:38

score 2 · Answer 4 · answered Aug 22 '11 at 18:08

A stored procedure that sometimes returns a result while sometimes it doesn't would be a nightmare to use from any API. The client side API has different entry points depending on whether you return a result set (SqlCommand.ExecuteReader) or it does not return a result set (SqlCommand.ExecuteNonQuery). It would be impossible for the application to know ahead of time which API to use! Modeling tools use the SET FMTONLY option to analyze the metadata of returned result sets and the modeling tools are very confused when your returned result set start changing shape at random. In other words, you are down the wrong path, stop and turn around.

Just run the query, it no rows match your criteria it will simply return an empty result set. Which is exactly what every client API and modeling tool expects from your procedure.

Return result of select query only if not empty

4 Answers4