CODEWITHSUNDEEP
sqlsql-serversql-server-2005
test
test

Reputation: 2127

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

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

Upvotes: 44

Views: 169315

Answers (6)

Luk
Luk

Reputation: 5511

What do you mean exactly? Do you want to reuse the result of your query for an other query?

In that case, why don't you combine both queries, by making the second query search inside the results of the first one (SELECT xxx in (SELECT yyy...)

Upvotes: 1

Siddhesh Bondre
Siddhesh Bondre

Reputation: 779

This will work for original question asked:

DECLARE @Result INT;
SELECT @Result = COUNT(*)
FROM  TableName
WHERE Condition

Upvotes: 2

rslite
rslite

Reputation: 84813

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

Upvotes: 70

Allisson Pereira
Allisson Pereira

Reputation: 131

-- 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

Upvotes: 13

Pittsburgh DBA
Pittsburgh DBA

Reputation: 6783

You could also just put the first SELECT in a subquery. Since most optimizers will fold it into a constant anyway, there should not be a performance hit on this.

Incidentally, since you are using a predicate like this:

CONVERT(...) = CONVERT(...)

that predicate expression cannot be optimized properly or use indexes on the columns reference by the CONVERT() function.

Here is one way to make the original query somewhat better:

DECLARE @ooDate datetime
SELECT @ooDate = 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
  FF.FaultDate >= @ooDate AND
  FF.FaultDate < DATEADD(day, 1, @ooDate) AND
  OFIO.OutageID = 1

This version could leverage in index that involved FaultDate, and achieves the same goal.

Here it is, rewritten to use a subquery to avoid the variable declaration and subsequent SELECT.

SELECT 
  COUNT(FF.HALID)
FROM
  Outages.FaultsInOutages AS OFIO 
  INNER JOIN Faults.Faults as FF ON 
    FF.HALID = OFIO.HALID 
WHERE
  CONVERT(varchar(10), FF.FaultDate, 126) = (SELECT CONVERT(varchar(10), OO.Date, 126) FROM OLAP.OutageHours AS OO where OO.OutageID = 1) AND
  OFIO.OutageID = 1

Note that this approach has the same index usage issue as the original, because of the use of CONVERT() on FF.FaultDate. This could be remedied by adding the subquery twice, but you would be better served with the variable approach in this case. This last version is only for demonstration.

Regards.

Upvotes: 2

JPrescottSanders
JPrescottSanders

Reputation: 2151

You could use:

declare @foo as nvarchar(25)

select @foo = 'bar'

select @foo

Upvotes: 3

Related Questions