I want this function to retrieve the latest buyer from two databases db1 and db2.
It worked for a single db. Now I try to use UNION to query both dbs an get the latest of the two
It won't accept this function "Select statements included within a function cannot return data to a client".
What options do I have?
CREATE FUNCTION [dbo].[LastBuyer1]
(@SC1 varchar(30))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3)
SELECT @LB =
(WITH CTE AS
(SELECT TOP 1 Buyer, OrderEntryDate
FROM db1.dbo.PorMasterDetail D INNER JOINdb1.dbo.PorMasterHdr H ON H.PurchaseOrder=D.PurchaseOrder
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC
UNION
SELECT TOP 1 Buyer, OrderEntryDate
FROM db2.dbo.PorMasterDetail D INNER JOIN db2.dbo.PorMasterHdr H ON H.PurchaseOrder=D.PurchaseOrder
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC)
SELECT TOP 1 CTE.Buyer FROM CTE ORDER BY OrderEntryDate DESC)
RETURN(@LB);
END
I think you need to change your query a little bit to something more like this:
CREATE FUNCTION [dbo].[LastBuyer1]
(@SC1 varchar(30))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3);
WITH CTE AS
(SELECT TOP 1 Buyer, OrderEntryDate
FROM db1.dbo.PorMasterDetail D INNER JOINdb1.dbo.PorMasterHdr H ON H.PurchaseOrder=D.PurchaseOrder
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC
UNION
SELECT TOP 1 Buyer, OrderEntryDate
FROM db2.dbo.PorMasterDetail D INNER JOIN db2.dbo.PorMasterHdr H ON H.PurchaseOrder=D.PurchaseOrder
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC)
SELECT TOP 1 @LB = CTE.Buyer FROM CTE ORDER BY OrderEntryDate DESC;
RETuRN (@LB);
END
GO
Basically moving where you do your variable assignment.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 21, 2020 at 10:33 pm
I love it when it works the first time! Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply