CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

how to store sql results into variable in sql server 2008

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

Upvotes: 0

Views: 53

Answers (2)

Jade
Jade

Reputation: 2992

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

Upvotes: 0

Patrick
Patrick

Reputation: 6948

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

Upvotes: 2

Related Questions