CODEWITHSUNDEEP
sql-servervariablesstored-procedures
Eclipse
Eclipse

Reputation: 309

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:

  DECLARE
    @Sib1 varchar(20),
    @Sib2 varchar(20),
    @Sib3 varchar(20),
    @Sib4 varchar(20),
    @Sib5 varchar(20),
    @Sib6 varchar(20)

select
    PC.SKU
from
    Product PC
    where
        Parent_code in (select
                                  Parent_code
                              from
                                  Product 
                              where
                                  SKU =12345)
        and ParentFlag <> 'p'
        and SKU <> 12345
order by Parent_Child_Priority desc

I'd like to put each one of the resulting SKU in each @SIB variables. if it only returns 1 result, I'd like to put null values into the rest of the @SIB variables.

Thanks.

Upvotes: 1

Views: 22738

Answers (2)

TLaV
TLaV

Reputation: 389

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 @Sib1 VARCHAR(20)
    ,@Sib2 VARCHAR(20)
    ,@Sib3 VARCHAR(20)
    ,@Sib4 VARCHAR(20)
    ,@Sib5 VARCHAR(20)
    ,@Sib6 VARCHAR(20);

DECLARE @TempTbl TABLE (
    RowID INT IDENTITY
    ,SKU VARCHAR(20)
    )

INSERT INTO @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 <> 12345
order by Parent_Child_Priority desc

SELECT @Sib1 = SKU
FROM @TempTbl
WHERE RowID = 1;

SELECT @Sib2 = SKU
FROM @TempTbl
WHERE RowID = 2;

SELECT @Sib3 = SKU
FROM @TempTbl
WHERE RowID = 3;

SELECT @Sib4 = SKU
FROM @TempTbl
WHERE RowID = 4;

SELECT @Sib5 = SKU
FROM @TempTbl
WHERE RowID = 5;

SELECT @Sib6 = SKU
FROM @TempTbl
WHERE RowID = 6;

EDIT

DECLARE @SQL VARCHAR(MAX);

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

Upvotes: 2

Rahul
Rahul

Reputation: 77856

Rather use a table variable like

DECLARE @MyTableVar table(
    SKU int NOT NULL);

Then insert into it

insert into @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 <> 12345
order by Parent_Child_Priority desc;

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

Upvotes: 1

Related Questions