Prev: modeling a contract and effective dates
Next: SQL Server 2005 DB Roles
From: Andy B. on 17 Mar 2010 13:33
I have this scalar function:

create function [Headlines].[HeadlineCount]()
returns int
as
declare @Rows as int

select @Rows = count(*) from [Headlines].[Headlines]
return @Rows


Wierd question: How do you use it now?


From: Andy B. on 17 Mar 2010 13:50

"Andy B." <a_borka(a)sbcglobal.net> wrote in message
news:edXKkffxKHA.5132(a)TK2MSFTNGP05.phx.gbl...
>I have this scalar function:
>
> create function [Headlines].[HeadlineCount]()
> returns int
> as
> declare @Rows as int
>
> select @Rows = count(*) from [Headlines].[Headlines]
> return @Rows
>
>
> Wierd question: How do you use it now?
>
When I do this:

select [Headlines].[HeadlineCount]

I get this:

Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "Headlines.HeadlineCount" could not be bound.



The function does exist.


From: Andy B. on 17 Mar 2010 14:50

"ML" <ML(a)discussions.microsoft.com> wrote in message
news:71797A51-013E-4C67-8C0D-ECE6F633C68F(a)microsoft.com...
> select [Headlines].[HeadlineCount]() as HeadlineCount
>
> One question from me:
> Why?
>
Should I not be doing this? Should it be in a stored proc then?


From: Andy B. on 17 Mar 2010 16:37

"ML" <ML(a)discussions.microsoft.com> wrote in message
news:E5EF8BDF-6D0A-41B7-A107-A6CF6D280941(a)microsoft.com...
>> Should I not be doing this? Should it be in a stored proc then?
>
> I don't know. :) That's why I asked.
>
> If you're going to use this function in a query, e.g.:
>
> select <column list>
> ,<scalar function>
> from <object>
>
> ...then the function will be called for every row in the result set.
>
> If, on the other hand, you were to include the logic used in the function
> inside the main query (although your function is not a good example here),
> e.g.:
>
> select <column list>
> ,count(*) as Result
> from <first table>
> <join> <second table>
> on <join predicate(s)>
>
> ...then there's a much better chance that the Query Optimizer will be able
> to come up with a more optimal execution plan, one that would not require
> calling the function for every row, but maybe just once per entire set.
>
> So, whether you should or should not do something really very much depends
> on the case at hand.
All I wanted to do was make an easy way of getting the total row count of
the table and sending it back to the client. Maybe a stored proc is
better...:)


 | 
Pages: 1
Prev: modeling a contract and effective dates
Next: SQL Server 2005 DB Roles