469,088 Members | 1,274 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,088 developers. It's quick & easy.

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.

// pt

Jul 23 '05 #1
6 1247
[posted and mailed, please reply in news]

Peter Theill (th****@gmail.com) writes:
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.


How do you know that function is executed for each row? If this is really
the case, I bet the SQL Server team would like to hear about it. Therefore
the right place to race the issue is one the
microsoft.private.sqlserver2005.* groups. See here for access information
to these groups: http://go.microsoft.com/fwlink/?linkid=31765.

Exactly which CTP are you seeing this on? The latest is the Jiune CTP,
aka CTP15. If you don't have that version, maybe you should download
and see if the problem persists.

I did an attempt to recreate the problem, but the repro below did not
exhibit the problem. You might want to use the trick I use with
xp_cmdshell to verify that the procedure is actually called once
for each row.

CREATE TABLE kilroy(OrderID int NOT NULL)
go
CREATE FUNCTION kilroy_was_here (@OrderID int) RETURNS INT AS
BEGIN
DECLARE @sql nvarchar(MAX), @sqlcmd varchar(255)
SELECT @sql = 'INSERT kilroy(OrderID) VALUES(' + str(@OrderID) + ')'
SELECT @sqlcmd = 'SQLCMD -d tempdb -Q "' + @sql + '"'
EXEC master..xp_cmdshell @sqlcmd
RETURN 8
END
go
SELECT O.OrderID, O.EmployeeID, C.CompanyName,
dbo.kilroy_was_here(O.OrderID)
FROM Northwind..Orders O, Northwind..Customers C
WHERE O.CustomerID = C.CustomerID
AND C.CustomerID = N'BERGS'
ORDER BY O.OrderID
go
SELECT * FROM kilroy
go
DROP TABLE kilroy
DROP FUNCTION kilroy_was_here


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
>>
How do you know that function is executed for each row? If this is
really
the case, I bet the SQL Server team would like to hear about it.
Therefore
the right place to race the issue is one the
microsoft.private.sqlserver2005.* groups. See here for access
information
to these groups: http://go.microsoft.com/fwlink/?linkid=31765.
<<

I ran a profiler and saw several calls relating to my cursor used in
the UDF (open cursor, fetch next, etc). I agree that I have to post it
but it takes a couple of days to get the betaId so I just wanted to
know if it was me.

Exactly which CTP are you seeing this on? The latest is the Jiune CTP,
aka CTP15. If you don't have that version, maybe you should download
and see if the problem persists.
<<

I'm using the CTP working with the Whidbey (VS.NET 2005) beta 2
release. I haven't tried newer CTP since I'm not sure if these will
work with the above version.


I did an attempt to recreate the problem, but the repro below did not
exhibit the problem. You might want to use the trick I use with
xp_cmdshell to verify that the procedure is actually called once
for each row.
<<

Thanks, I'll give it a try.

Jul 23 '05 #3
Peter Theill (th****@gmail.com) writes:
I ran a profiler and saw several calls relating to my cursor used in
the UDF (open cursor, fetch next, etc).
You didn't mention any cursor in your first post?
I agree that I have to post it but it takes a couple of days to get the
betaId so I just wanted to know if it was me.


Not really sure where the beta id comes in, but you don't need any
betaid to post to the microsoft.private.sqlserver2005.* groups.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
>>
You didn't mention any cursor in your first post?
<<

I'm using a cursor in my user defined function.


Not really sure where the beta id comes in, but you don't need any
betaid to post to the microsoft.private.sqlserver2005.* groups.
<<

Sorry, maybe a stupid question but where do I access these newsgroups?
They're not available on "news.microsoft.com".

Jul 23 '05 #5
Peter Theill (th****@gmail.com) writes:
Sorry, maybe a stupid question but where do I access these newsgroups?


See http://go.microsoft.com/fwlink/?linkid=31765. (A link that I've
already posted once in the thread.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
>>
See http://go.microsoft.com/fwlink /?linkid=31765. (A link that I've
already posted once in the thread.)
<<

Yeah, ok ;-)

I'll post my message there .. thanks.

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Sameer Deshpande | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.