Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

Use of user defined in SELECT clause

I'm having this query:

SELECT
ss.subscription_id AS SubscriptionId,
s.id AS ScopeId,
s.[name] AS ScopeName,
s.base AS ScopeBase,
dbo.iqGetShapesByScopeAsString(s.id) AS ShapesAsString

FROM
subscription_scope ss,
scope s

WHERE
ss.subscription_id = @subscription_id
AND
ss.scope_id = s.id

ORDER BY
s.[name]

The select only returns a single row but my database (SQL Server 2005 CTP) seems to execute the "iqGetShapesByScopeAsString" function for each row in the subscription_scope and scope tables. This is a bug, right?

The function should be executed only once for each *returned* row in the SELECT, right? I believe that was the case in SQL 2k though I can't check it at the moment.

I've seen the iqGetShapesByScopeAsString is executed several times because I ran Profiler and noticed my cursor (my UDF uses a cursor) is opening and close the cursor a lot of times.

[1368 byte] By [PeterTheill] at [2008-2-7]
«« Direct Show - Configuring MPEG-2 Demultiplexer Pins
»» How to change default script generation options in SQL 2005 tools or VS 2005
# 1
The answer depends on what your function looks like. Could you post it?
GregYvkoff at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified