1

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?

drewwyatt
  • 5,989
  • 15
  • 60
  • 106

1 Answers1

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
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • 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? – drewwyatt Feb 19 '14 at 16:32
  • 1
    If you do return the same columns, then you can still combine the PROC using a technique [like this one](http://stackoverflow.com/a/3415629/314291), 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](http://stackoverflow.com/q/11329823/314291) (I'm assuming sql-server, btw) – StuartLC Feb 19 '14 at 16:38