Two SELECT statements in one stored procedure, one supplying input for another and the other returning more than one row

Question

I have a select statement that gets 4 column values in a row for one iteration from a query that has lot of joins. One of the column value has to be given to another select statement as input to check a where condition. This select statement returns three rows for a input from each iteration of the first select statement. I need to get all the column values from the three rows of the second select statement along with the all the column values of the first select statement.

SELECT val1, val2, val3, val4 from ....

SELECT val5, val6 from anotherTable where someColumn = val1

RESULT required :

val1, val2, val3, val4, val51, val61, val52, val62, val53, val63

I, am using two connections and two readers to make this happen, but its slowing me down. I'd like it if I can get this done in a single stored procedure.

Answer 1

You can do something like this

WITH first AS 
(
  SELECT val1, val2, val3, val4
    FROM Table1
   WHERE 1 = 1
), second AS
(
  SELECT val1,
         MIN(CASE WHEN rnum = 1 THEN val5 END) val51,
         MIN(CASE WHEN rnum = 1 THEN val6 END) val61,
         MIN(CASE WHEN rnum = 2 THEN val5 END) val52,
         MIN(CASE WHEN rnum = 2 THEN val6 END) val62,
         MIN(CASE WHEN rnum = 3 THEN val5 END) val53,
         MIN(CASE WHEN rnum = 3 THEN val6 END) val63
    FROM
  (
    SELECT t2.val1, val5, val6, 
           ROW_NUMBER() OVER (PARTITION BY t2.val1 ORDER BY (SELECT 1)) rnum
      FROM Table2 t2 JOIN first f
        ON t2.val1 = f.val1
  ) a
   GROUP BY val1
)
SELECT * 
  FROM first f JOIN second s
    ON f.val1 = s.val1

Here is SQLFiddle demo

Answer 2

Try this out if ur return value are only 1 and u just want to use val1 as your second query parameter

DECLARE @val1 NVARCHAR(255)

SELECT val1, val2, val3, val4 from ....

SET @val1 = (Select ordinal_position from information_schema.columns
where Column_Name='@val1' and Table_name = '@yourTableName')

SELECT val5, val6 from anotherTable where someColumn = @val1

Your return result

val1, val2, val3, val4, val51, val61, val52, val62, val53, val63

The return result is based on what u select, in this case, your 2nd query may not work

try to change ur 2nd query to something like below

Select val1,val2,val3, val4, COALESCE(val5,val1), COALESCE(val6,val1)