Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hello everyone,

I have a small issue.
In a where clause of some views and functions another function is called to count on selecteditems in a table. Since the number (integer) is asked in the where clause a table scan was created (retrieving 1 row takes as lons a retrievin 100,000 rows). When declaring an @var as int and then use the @var = 0 in the where clause the table scan is out of sight. How can I do this in functions and views? Since declaring @var is not possible in views and functions.
Posted
Updated 12-Dec-11 0:00am
v2
Comments
Valery Possoz 12-Dec-11 6:27am    
your question is a bit confusing, could you show us some code?

1 solution

You can cope with this problem with Common Table Expressions(CTEs) very easily. I mean nested ones. For example :

SQL
with cte1(...) as
(
   select some rows
), cte2(...) as
(
   do what ever you want with each row of cte1 (in this case implement your function here for each row)
), cte3(...) as
(
   do other things with each row of cte2
)
select * from cte3


By this way you can convert any function into a simple select statement.

Hope it helps
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900