how to store sql results into variable in sql server 2008

Question

I have stored procedure and inside i have select statement, insert and update but in my select statement i would like the results to be stored in a variable so i can access it later on in my update statement. How can i store the result first in a variable? Here is my select statement:

 SELECT 
  REV1 = COUNT(CASE WHEN QTR = 1 AND MAIN_SAT =1 AND ACTIVE_FLAG = 1  THEN 1 END),
  REV2= COUNT(CASE WHEN QTR = 1 AND MAIN_EKL =1 AND ACTIVE_FLAG = 1  THEN 1 END),
  REV3= COUNT(CASE WHEN QTR = 1 AND MAIN_LAM =1 AND ACTIVE_FLAG = 1  THEN 1 END),
  REV4= COUNT(CASE WHEN QTR = 1 AND MAIN_JAC =1 AND ACTIVE_FLAG = 1 THEN 1 END)
  FROM MyTable

The result of this select statement looks like this:

REV1    REV2    REV3   REV4
12       45       87    54

Patrick · Accepted Answer · 2013-12-20 01:08:01Z

2

You can either make a table variable or individual variables (depending on your preference). Example with separate variables:

DECLARE @Rev1 int
DECLARE @Rev2 int
DECLARE @Rev3 int
DECLARE @Rev4 int

SELECT 
@Rev1 = COUNT(CASE WHEN QTR = 1 AND MAIN_SAT =1 AND ACTIVE_FLAG = 1  THEN 1 END),
@Rev2= COUNT(CASE WHEN QTR = 1 AND MAIN_EKL =1 AND ACTIVE_FLAG = 1  THEN 1 END),
@Rev3= COUNT(CASE WHEN QTR = 1 AND MAIN_LAM =1 AND ACTIVE_FLAG = 1  THEN 1 END),
@Rev4= COUNT(CASE WHEN QTR = 1 AND MAIN_JAC =1 AND ACTIVE_FLAG = 1 THEN 1 END)
FROM MyTable

answered Dec 20, 2013 at 1:08

Patrick

6,9783 gold badges25 silver badges31 bronze badges

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

Comments

Jade · Accepted Answer · 2013-12-20 01:17:59Z

0

Try This

this will reduced the condition and much clearer.

DECLARE @Rev1 int
DECLARE @Rev2 int
DECLARE @Rev3 int
DECLARE @Rev4 int
SELECT 
    @Rev1 = SUM(CASE WHEN MAIN_SAT =1 THEN 1 ELSE 0 END),
    @Rev2= SUM(CASE WHEN MAIN_EKL =1 THEN 1 ELSE 0 END),
    @Rev3= SUM(CASE WHEN MAIN_LAM =1 THEN 1 ELSE 0 END),
    @Rev4= SUM(CASE WHEN MAIN_JAC =1THEN 1 ELSE 0 END)
FROM MyTable
WHERE QTR = 1
    AND ACTIVE_FLAG = 1

answered Dec 20, 2013 at 1:17

Jade

2,9941 gold badge13 silver badges9 bronze badges

Collectives™ on Stack Overflow

how to store sql results into variable in sql server 2008

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related