Posted by Erland Sommarskog on 07/01/05 01:09

[posted and mailed, please reply in news]

Peter Theill (theill@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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]