Store query result in variable

Question

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.

Why not use a temp table instead? Using variables like this is going to be very challenging to populate. — Sean Lange
– Sean Lange, Commented May 31, 2016 at 18:26
Think in terms of data sets in relational database design, not rows/records. — xQbert
– xQbert, Commented May 31, 2016 at 18:31
My goal is to use these resulting SKU's in another query in where clause. That's why I'm trying to store these in a variable and use them later. Any other suggestions? — Eclipse
– Eclipse, Commented May 31, 2016 at 18:36

TLaV · Accepted Answer · 2016-05-31 19:03:00Z

2

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);

edited May 31, 2016 at 19:03

answered May 31, 2016 at 18:43

TLaV

3892 silver badges4 bronze badges

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

6 Comments

Eclipse Over a year ago

This works. If I want to use @sib1 as a column name in another query, do I use dynamic sql? if so, an example please. I'm trying to format a query something like sqltext='select SKU, ..... , sum(convert(int, a.qty)) as ' + SIB1 + '.... but this is giving me an error at the as ' + SIB1 + ' part. this comment isn't letting me use the at symbol but SIB1 and sqltest are variables with at sign

TLaV Over a year ago

@Eclipse - You should be able to do something like in the "EDIT" section I just added.

Eclipse Over a year ago

Tim, If my query doesn't return 6 rows and only returns 1 row, variables sib2-sib6 would be empty. that's causing my other query to fail! any solution for that?

TLaV Over a year ago

Ah! I'm not sure exactly what result you are looking for, but you could surround the variable with ISNULL and set it to something else (like an empty string?) if it's null. So 'Select SKU,.... AS ' + ISNULL(@sib1,'') + ' FROM...'

Eclipse Over a year ago

Tim, Can this portion be looped instead of repeating six times? SELECT Sib1 = SKU FROM @TempTbl WHERE RowID = 1;

|

Rahul · Accepted Answer · 2016-05-31 18:28:42Z

1

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.

answered May 31, 2016 at 18:28

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

Store query result in variable

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related