|
|
Posted by Erland Sommarskog on 10/01/58 11:18
carey.loomis@gmail.com (carey.loomis@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|