ca**********@gmail.com (ca**********@gmail.com) writes:
Yes, I do assign the results of the select to a local variable. These
functions are simple and I'm not having trouble with how to use
functions. The problem is they work with SQL 2000 but produce errors
with SQL 2005. Here is an example of one of the functions.
create function dbo.vfVendorName(@SubscriberID int, @ApplicationNum
int)
returns varchar(100)
as
BEGIN
declare @rtn varchar(100)
select @rtn=Vendorname from vAppVendor
where SubscriberID=@SubscriberID and ApplicationNum=@ApplicationNum
return @rtn
end
Works fine when called from SQL 2000 but gives "Select statements
included within a function cannot return data to a client." when run
from SQL 2005.
I rewrote your function as:
create function dbo.blafs(@OrderID int, @EmployeeID int)
returns varchar(100)
as
BEGIN
declare @rtn nvarchar(100)
select @rtn = CustomerID from Northwind..Orders
where OrderID = @OrderID and EmployeeID = @EmployeeID
return @rtn
end
go
SELECT dbo.blafs (10988, 3)
go
Since I only changed table and column names to a database that I have
available, this function should yield the same result as yours. However,
I seem to recall that I have seen a similar problem discussed in the
beta newsgroups, so there might be an issue somewhere.
Anyway, this is not the place where you should discuss SQL 2005.
Please see
http://go.microsoft.com/fwlink/?linkid=31765 for access
information to the beta newsgroups for SQL 2005. This is because
these groups are frequented by more SQL Server developers than
this newsgroup.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp