Store Query Result In Variable

I have declared 6 variables in a stored procedure and I'd like to store a query result (which may bring up to 6 records) into each one of those variables. My query looks like this:

Solution 1:

You could insert the SKU's into a table variable, with an identity column. Then set the variables equal to the sku in the table based on the identity columns value.

DECLARE@Sib1VARCHAR(20)
    ,@Sib2VARCHAR(20)
    ,@Sib3VARCHAR(20)
    ,@Sib4VARCHAR(20)
    ,@Sib5VARCHAR(20)
    ,@Sib6VARCHAR(20);

DECLARE@TempTblTABLE (
    RowID INTIDENTITY
    ,SKU VARCHAR(20)
    )

INSERTINTO@TempTbl (SKU)
select
    PC.SKU
from
    Product PC
    where
        Parent_code in (select
                                  Parent_code
                              from
                                  Product 
                              where
                                  SKU =12345)
        and ParentFlag <>'p'and SKU <>12345orderby Parent_Child_Priority descSELECT@Sib1= SKU
FROM@TempTblWHERE RowID =1;

SELECT@Sib2= SKU
FROM@TempTblWHERE RowID =2;

SELECT@Sib3= SKU
FROM@TempTblWHERE RowID =3;

SELECT@Sib4= SKU
FROM@TempTblWHERE RowID =4;

SELECT@Sib5= SKU
FROM@TempTblWHERE RowID =5;

SELECT@Sib6= SKU
FROM@TempTblWHERE RowID =6;

EDIT

DECLARE@SQLVARCHAR(MAX);

SET@SQL='SELECT SKU, ..., sum(convert(INT, a.qty)) AS '+@sib1+' FROM ...'EXEC (@SQL);

Solution 2:

Rather use a table variable like

DECLARE@MyTableVartable(
    SKU intNOTNULL);

Then insert into it

insertinto@MyTableVar(SKU)
select
    PC.SKU
from
    Product PC
    where
        Parent_code in (select
                                  Parent_code
                              from
                                  Product 
                              where
                                  SKU =12345)
        and ParentFlag <>'p'and SKU <>12345orderby Parent_Child_Priority desc;

Now you can use that @MyTableVar as you need. You don't need to declare N variable for N records.