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

User · Answer

This will work for original question asked   DECLARE  Result INT  SELECT  Result   COUNT    FROM  TableName WHERE Condition

User · Answer

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  gt    ooDate AND   FF FaultDate  lt  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

User · Answer

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  gt    ooDate AND   FF FaultDate  lt  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

User · Answer

You could use   declare  foo as nvarchar 25   select  foo    bar   select  foo

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005