Can I return an additional value along with SQL data in a stored procedure?

Question

I am attempting to combine two stored procedures into one. Previously I had:

@argument int

SELECT val1, val2, val3 
  FROM table as x
       INNER JOIN(
                   ... ) as y 
                  ON x.val1 = y.val2
 WHERE someCondition = @argument

Then I would fire a virtually identical stored procedure, where the only difference was the condition.

I would like to find a way to combine these procedures to create something like this:

@argument int

SELECT val1, val2, val3,
       isCondition1 = true -- Additional return value
  FROM table as x
       INNER JOIN (
                    ... ) as y 
                  ON x.val1 = y.val2
  WHERE someCondition = @argument

SELECT val1, val2, val3,
       isCondition1 = false -- Additional return value
    FROM table as x
         INNER JOIN(
             ...) as y 
             ON x.val1 = y.val2
    WHERE someOtherCondition = @argument

Is this possible?

StuartLC · Accepted Answer · 2014-02-19 19:01:52Z

2

It isn't a good idea to have a stored proc which can return a variable number of columns.

It does however look like the additional return values of the 2 queries are compatable ?

If the number, types or names of the result columns differ, then I would keep the 2 SPROC's, but I would DRY up the common heavy lifting in a VIEW which is then used by both of the PROCs

CREATE VIEW vwWithCommonWork
  AS
    SELECT val1, val2, val3, -- any other useful common columns here
    FROM table as x
    INNER JOIN(
    ...
    ) as y ON x.val1 = y.val2
    -- No WHERE

The procs then consume the view:

Proc 1

SELECT val1, val2, val3, isCondition1 = 'true'
FROM vwWithCommonWork
WHERE someCondition = @argument

Proc 2

SELECT val1, val2, val3, isCondition1 = 'false'
FROM vwWithCommonWork
WHERE someOtherCondition = @argument

edited Feb 19, 2014 at 19:01

answered Feb 19, 2014 at 16:25

StuartLC

108k18 gold badges225 silver badges295 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

drewwyatt Over a year ago

This is totally acceptable, and I will use this in some capacity. Thanks! To answer your question, yes. In my original question, my requested solution would return the same number of columns. Both queries would return a boolean value. Is that do-able? Could I co,bine that with your answer here?

StuartLC Over a year ago

If you do return the same columns, then you can still combine the PROC using a technique like this one, however, it comes with the dilemma of whether to use an if / else branch and have 2 separate queries (bad for parameter sniffing), or a single query with an optional filters pattern (bad for query plans), or dynamic sql (smelly). Another example here (I'm assuming sql-server, btw)

Collectives™ on Stack Overflow

Can I return an additional value along with SQL data in a stored procedure?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related