SQL Server 2005 Setting a variable to the result of a select query

Question

How do I set a variable to the result of select query without using a stored procedure?

---

I want to do something like: OOdate DATETIME

SET OOdate = Select OO.Date  FROM OLAP.OutageHours as OO WHERE OO.OutageID = 1 

Then I want to use OOdate in this query:

SELECT COUNT(FF.HALID) from Outages.FaultsInOutages as OFIO INNER join Faults.Faults as FF ON FF.HALID = OFIO.HALID WHERE CONVERT(VARCHAR(10),OO.Date,126) = CONVERT(VARCHAR(10),FF.FaultDate,126))  AND OFIO.OutageID = 1 

Answer 1

You can use something like

SET @cnt = (SELECT COUNT(*) FROM User) 

or

SELECT @cnt = (COUNT(*) FROM User) 

For this to work the SELECT must return a single column and a single result and the SELECT statement must be in parenthesis.

Edit: Have you tried something like this?

DECLARE @OOdate DATETIME  SET @OOdate = Select OO.Date from OLAP.OutageHours as OO where OO.OutageID = 1  Select COUNT(FF.HALID)  from Outages.FaultsInOutages as OFIO  inner join Faults.Faults as FF      ON FF.HALID = OFIO.HALID  WHERE @OODate = FF.FaultDate     AND OFIO.OutageID = 1 

Answer 2

-- Sql Server 2005 Management studio

  use Master go DECLARE @MyVar bigint SET @myvar = (SELECT count(*) FROM spt_values); SELECT @myvar  Result: 2346 (in my db) 

-- Note: @myvar = @Myvar