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

Question

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

Q: Example SQL please? Q: What makes you think calling a function two times in the same query is necessarily a Bad Thing? If that's what the statement needs, and it results in one and only one call to the server, then it's the optimal thing to do! — paulsm4
– paulsm4, Commented Jun 29, 2012 at 5:32
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 — Girish Chaudhari
– Girish Chaudhari, Commented Jun 29, 2012 at 5:51
You can edit your question. And format code in questions (using {}). Asif has already suggested an edit, you should just need to approve it. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 29, 2012 at 6:07

Chris Moutray · Accepted Answer · 2012-06-29 05:34:58Z

2

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

answered Jun 29, 2012 at 5:34

Chris Moutray

18.4k7 gold badges49 silver badges66 bronze badges

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

Comments

Asif · Accepted Answer · 2012-06-29 05:59:12Z

1

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'

answered Jun 29, 2012 at 5:59

Asif

2,67721 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related