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
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 ...
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');
SET (@var1, @var2, ..., @varn) = (SELECT ... )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.
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');
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%'
