Assign value to variable in SELECT statement

Question

I have this SQL Server query:

DECLARE @HoursUsed float

SELECT COLUMN_A 
,(SELECT (@HoursUsed = (SELECT dbo.fn_GetProjectHoursUsed(Project.fg_projectId))) 
,@HoursUsed*100
,@HoursUsed*200
FROM Project, OTHER_TABLES)

I need the result from the function to be added to the variable because I will need it in several places later in the query.

The DB function is working fine by itself. If I remove the variable from the SELECT it works fine.

If I try to run the query like this it throws errors like:

Incorrect syntax near =, ), as.

The entire query uses this function several times and I have performance issues with it. So that's why I want to put the value in a variable and call the function only once.

I appreciate your help

Edit: Here is the complete query.

DECLARE @HoursUsed float

SELECT  DISTINCT
    Account.Name as AccountName
    ,Project.FG_SalesRepIdName as Rep
    ,Project.fg_Name as ProjectName
    ,Project.fg_projectId

,(SELECT dbo.fn_GetProjectPriceRate(Project.fg_projectId)) as ProjectPriceRate

,(
SELECT SalesOrder.totalAmount FROM fg_Project, SalesOrder
        WHERE fg_Project.fg_orderId = SalesOrder.salesOrderId 
            AND fg_Project.fg_Name = Project.fg_Name
            AND fg_Project.fg_projectId = Project.fg_projectId
)/dbo.fn_GetProjectPriceRate(Project.fg_projectId) as TotalHours  
    --The total hours are found by division of the total price of the project     with the price rate
    ,(
    SELECT SalesOrder.totalAmount FROM fg_Project, SalesOrder
            WHERE fg_Project.fg_orderId = SalesOrder.salesOrderId 
                AND fg_Project.fg_projectId = Project.fg_projectId
    ) AS TotalAmount

    ,(SELECT @HoursUsed = dbo.fn_GetProjectHoursUsed(Project.fg_projectId)
        SELECT @HoursUsed
    ) as HoursUsed

    ,(
    (SELECT dbo.fn_GetProjectHoursUsed(Project.fg_projectId))
    /
        NULLIF(((SELECT SalesOrder.totalAmount FROM fg_Project, SalesOrder
            WHERE fg_Project.fg_orderId = SalesOrder.salesOrderId 
                AND fg_Project.fg_Name = Project.fg_Name
                AND fg_Project.fg_projectId = Project.fg_projectId
               )
        /
              NULLIF(dbo.fn_GetProjectPriceRate(Project.fg_projectId),0)
             ),0)
    )*100 as PercentHoursUsed
    ,(SELECT dbo.fn_GetProjectHoursBilled(Project.fg_projectId)) as HoursBilled
    ,(SELECT dbo.fn_GetProjectHoursBilled(Project.fg_projectId))
     *
     (SELECT dbo.fn_GetProjectPriceRate(Project.fg_projectId)) as AmountBilled



FROM Account, fg_Project as Project, fg_ProjectElement, Product

WHERE       Account.accountId = Project.fg_regardingId
    AND Project.fg_ProjectId = fg_ProjectElement.fg_projectElemenstId
    AND fg_ProjectElement.fg_ProductId = Product.ProductId
    AND Product.ProductNumber = 'WEB DEV'

The entire query uses this function several times and I have performance issues with it. So that's why I want to put the value in a variable and call the function only once. — Mité
– Mité, Commented Jul 21, 2011 at 23:35

Moe Sisko · Accepted Answer · 2011-07-22 06:51:30Z

2

I'm partially guessing your schema, but maybe using a temp table would help ? e.g. something like :

DECLARE @HoursUsedTable TABLE (ProjectId int primary key, HoursUsed float)
INSERT @HoursUsedTable (ProjectId, HoursUsed) 
SELECT fg_projectId, dbo.fn_GetProjectHoursUsed(p.fg_projectId) from Project p


SELECT COLUMN_A, 
hu.HoursUsed,
hu.HoursUsed * 100,
hu.HoursUsed * 200
FROM Project p JOIN @HoursUsedTable hu ON hu.ProjectId = p.fg_projectId
, OTHER_TABLES

Note also that using scalar valued UDFs in some contexts can lead to peformance issues (I'm guessing your UDF returns a scalar ?) - better to use an inline table valued UDF if possible.

answered Jul 22, 2011 at 6:51

Moe Sisko

12.2k9 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andriy M · Accepted Answer · 2011-07-22 11:39:46Z

2

You can use a subselect like this:

SELECT
  *,
  HoursUsed * 100 AS Hours100
  HoursUsed * 200 AS Hours200
FROM (
  SELECT
    HoursUsed = dbo.fn_GetProjectHoursUsed(Project.fg_projectId),
    other columns
  FROM Project, other tables
  …
) s

No variable is required.

answered Jul 22, 2011 at 11:39

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Comments

t-clausen.dk · Accepted Answer · 2011-07-22 09:57:06Z

1

Try this, it will only use the function once for every line and you can refer to the function result

SELECT COLUMN_A, a.HoursUsed, a.HoursUsed*100, a.HoursUsed*200 
FROM Project p CROSS APPLY 
(SELECT  dbo.fn_GetProjectHoursUsed(P.fg_projectId) HoursUsed) a

edited Jul 22, 2011 at 9:57

answered Jul 22, 2011 at 6:00

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Comments

Chandu · Accepted Answer · 2011-07-21 23:23:11Z

0

Try this:

SELECT @HoursUsed = dbo.fn_GetProjectHoursUsed(Project.fg_projectId)
...and rest of your query

answered Jul 21, 2011 at 23:23

Chandu

83.2k19 gold badges137 silver badges136 bronze badges

3 Comments

Mité Over a year ago

I forgot to mention, this whole query, is a subquery for a column in another query.

t-clausen.dk Over a year ago

mitte this is the correct solution, if your query still fails, you have a problem elsewhere in code which we don't have access to

Mité Over a year ago

I added the whole query to the question

davecoulter · Accepted Answer · 2011-07-21 23:27:27Z

0

This worked for me:

    DECLARE @HoursUsed float

    SELECT @HoursUsed = (SELECT dbo.fn_GetProjectHoursUsed(Project.fg_projectId))

Try removing the outer parans.

answered Jul 21, 2011 at 23:27

davecoulter

1,82413 silver badges15 bronze badges

Comments

RMorrisey · Accepted Answer · 2011-07-21 23:42:07Z

0

When you set a variable within the SELECT clause, the variable does not need its own SELECT keyword. The correct form should be:

DECLARE @HoursUsed float

SELECT COLUMN_A,@HoursUsed = dbo.fn_GetProjectHoursUsed(Project.fg_projectId) 
,@HoursUsed*100
FROM SOME_TABLES

If fn_GetProjectHoursUsed() has a table return value, this query isn't sufficiently complex to set @HoursUsed, and you'll need to give us a little more detail on what you're trying to do.

Edit: This turns out to be incorrect; can't combine variable assignment with normal select. Instead, use:

SELECT COLUMN_A,dbo.fn_GetProjectHoursUsed(Project.fg_projectId)*100
    FROM SOME_TABLES

edited Jul 21, 2011 at 23:42

answered Jul 21, 2011 at 23:34

RMorrisey

7,73911 gold badges59 silver badges77 bronze badges

5 Comments

Mité Over a year ago

When I try like this it gives me this error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

RMorrisey Over a year ago

In that case, you will just have to use the result directly. Treat the scalar function call no differently than a normal arithmetic expression: SELECT COLUMN_A ,100*dbo.fn_GetProjectHoursUsed(Project.fg_projectId) FROM SOME_TABLES

Mité Over a year ago

i try to put in a variable because if i call the function 5-6 times it will take more time the query to be executed. If I use a variable I think it will be called only once.

RMorrisey Over a year ago

Maybe you can set the variable in a separate statement, then? It's hard to give better advice without having a clear picture of what you're doing.

Mité Over a year ago

I added the whole query to the question.

Collectives™ on Stack Overflow

Assign value to variable in SELECT statement

6 Answers 6

Comments

Comments

Comments

3 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

3 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related