CODEWITHSUNDEEP
sqlsql-server-2008
Girish Chaudhari
Girish Chaudhari

Reputation: 1617

SQL : How to avoid multiple function call in a single query?

I have a query that accept the scalar-valued function 2 times, at one place in the field within the select list and second in the join condition. My question is that how to avoid that function call within single query? Thanks.

SELECT   i.Id, i.Name, . . . . . dbo.IsItemCApproved(C.Id) AS [Status], . . 
FROM @Item i 
JOIN Card C ON C.ItemId = i.Id 
AND C.Deadline IS NOT NULL 
AND dbo.IsItemCApproved(C.Id) = 'False' 
AND C.IsDeleted = 0

Upvotes: 0

Views: 2199

Answers (2)

Asif
Asif

Reputation: 2677

Select * from
(
SELECT   i.Id, i.Name, . . . . . dbo.IsItemCApproved(C.Id) AS [Status], . . 
FROM @Item i 
JOIN Card C ON C.ItemId = i.Id 
AND C.Deadline IS NOT NULL 
AND C.IsDeleted = 0
)A
WHERE [Status] = 'False'

Upvotes: 1

Chris Moutray
Chris Moutray

Reputation: 18349

Using a CTE

with cte 
as 
(
    select
        id,
        calculated = myFunction(field1, field2)
    from
        table1
)
select
    id,
    field1,
    field2,
    calculated
from
    table1
inner join
    cte on cte.id = table1.id
where 
    cte.calculated = @someCondition

Or Sub querying

select
    id,
    field1,
    field2,
    calculated
from
(
    select 
        field1,
        field2,
        calculated = myFunction(field1, field2)
    from
        table1
) t
where
    calculated = @someCondition

Upvotes: 2

Related Questions