sql set variable using COUNT

Question

I am trying to make a simple query to my server and want the result to be stored in the variable @times.

DECLARE @times int

SET @times = SELECT COUNT(DidWin)as "I Win"
FROM thetable
WHERE DidWin = 1 AND Playername='Me'

IntelliSense says Wrong syntax near Select

Mike Valenty · Accepted Answer · 2011-05-25T15:17:08.993

118

You just need parentheses around your select:

SET @times = (SELECT COUNT(DidWin) FROM ...)

Or you can do it like this:

SELECT @times = COUNT(DidWin) FROM ...

edited May 25 '11 at 15:17

answered May 22 '11 at 14:16

Mike Valenty

lambacck · Answer 2 · 2011-05-22T14:34:39.987

11

You can select directly into the variable rather than using set:

DECLARE @times int

SELECT @times = COUNT(DidWin)
FROM thetable
WHERE DidWin = 1 AND Playername='Me'

If you need to set multiple variables you can do it from the same select (example a bit contrived):

DECLARE @wins int, @losses int

SELECT @wins = SUM(DidWin), @losses = SUM(DidLose)
FROM thetable
WHERE Playername='Me'

If you are partial to using set, you can use parentheses:

DECLARE @wins int, @losses int

SET (@wins, @losses) = (SELECT SUM(DidWin), SUM(DidLose)
FROM thetable
WHERE Playername='Me');

edited May 22 '11 at 14:34

answered May 22 '11 at 14:15

lambacck

You can but you don't **need** to select directly into the variable. – ypercubeᵀᴹ May 22 '11 at 14:23
1

@ypercube: But if you get in the habit of using select, you wont try to use 2 or more set + select commands when 1 select can set all the variables at once. I also find the SELECT to be more readable. – lambacck May 22 '11 at 14:25
I disagree on the readability but that's personal taste. Readable for me would be `SET (@var1, @var2, ..., @varn) = (SELECT ... )` – ypercubeᵀᴹ May 22 '11 at 14:28
I like that the assignment is close the the variable name. If you have *many* assignments, it could be harder to pick out which item in the select applies to which variable. Not to mention that you end up having more code. – lambacck May 22 '11 at 14:32

score 2 · Answer 3 · answered May 22 '11 at 14:15

2

You want:

DECLARE @times int

SELECT @times =  COUNT(DidWin)
FROM thetable
WHERE DidWin = 1 AND Playername='Me'

You also don't need the 'as' clause.

answered May 22 '11 at 14:15

James Wiseman

score 0 · Answer 4 · answered May 22 '11 at 14:19

0

You can use SELECT as lambacck said or add parentheses:

SET @times = (SELECT COUNT(DidWin)as "I Win"
FROM thetable
WHERE DidWin = 1 AND Playername='Me');

answered May 22 '11 at 14:19

a1ex07

Vasil Valchev · Answer 5 · 2021-04-20T08:25:04.970

-1

If you need to print the result with column name "c", or read the result in your code by using "c" column name:

DECLARE @c int;
SELECT c = count(*) from '%Table%' WHERE '%condition%'

edited Apr 20 '21 at 08:25

answered Apr 19 '21 at 13:20

Vasil Valchev

1

This will not assign the result to the variable. It will instead return a result set with a column named c. The variable @c would be null. And you can't assign and return in a single statement. – Wes H Apr 19 '21 at 15:04

5 Answers5