SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help Needed with function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

176 Posts

Posted - 04/04/2013 :  10:43:34  Show Profile  Reply with Quote
I am trying to write a function to look up details from a different server. The two servers are linked.


Function
ALTER FUNCTION [dbo].[func_CRMgetInputHeaders]
(@ClearedFundsDate SMALLDATETIME,
@ProcessingDate SMALLDATETIME,
@CreditNumberOf DECIMAL(11,0),
@CrecitValueOf DECIMAL(11,2),
@DebititNumberOf DECIMAL(11,0),
@DebitValueOf DECIMAL(11,2),
@USERID varchar(6)
)
RETURNS @InputHdrTable Table
(Licence Varchar(6) NULL,
FileSerial varchar(6) NULL ,
Transdetid varchar(50) NULL )
AS
BEGIN

Declare @t_items decimal(11,0)
Declare @t_value decimal(11,2)
DECLARE @Licence varchar(6)
DECLARE @Serial varchar(6)
DECLARE @Transsetid uniqueidentifier
SET @t_items = @CreditNumberOf + @DebititNumberOf
SET @t_Value = @CrecitValueOf + @DebitValueOf

SELECT @Licence = Licence,
@Serial = SerialNumber,
@Transsetid = LedgerKey
FROM Server1.DB1.dbo.TranSet
WHERE SubmissionValue = @t_Value
AND TransactionCount = @t_items
AND DATEDIFF(d, PaymentDate , @ClearedFundsDate) = 0

INSERT INTO @InputHdrTable
SELECT @Licence, @Serial, @Transsetid

RETURN
END

SQL
select * from [dbo].[func_CRMgetInputHeaders] ((SELECT TOP 1 [ClearedFundsDate], [UFIuhl1ProcessingDate],
[DSRCnumberof],
[DSRCvalueof],
[DSRDnumberof],
[DSRDvalueof],
[UFIuserNumber]
[dbo].[TempINPUT]))

Error messages
Msg 116, Level 16, State 1, Line 8
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.func_CRMgetInputHeaders.

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/04/2013 :  11:03:39  Show Profile  Reply with Quote
I probably wouldn't choose to create a function with a linked server call. I would either create a stored procedure on the remote server and execute that from your local server OR replicate that [TranSet] table to your local server and use the local version.

Having said that the error seems to be from your SQL which doesn't include a FROM keyword in front of [dbo].[TempINPUT].

perhaps one of these would work:

select * 
from   [dbo].[func_CRMgetInputHeaders] (
       (SELECT TOP 1 [ClearedFundsDate],
              [UFIuhl1ProcessingDate],
              [DSRCnumberof],
              [DSRCvalueof],
              [DSRDnumberof],
              [DSRDvalueof],
              [UFIuserNumber]
              FROM [dbo].[TempINPUT]))


select f.*
from   [dbo].[TempINPUT] t
cross apply dbo.func_CRMgetInputHeaders
       (t.[ClearedFundsDate],
        t.[UFIuhl1ProcessingDate],
        t.[DSRCnumberof],
        t.[DSRCvalueof],
        t.[DSRDnumberof],
        t.[DSRDvalueof],
        t.[UFIuserNumber]) f


Be One with the Optimizer
TG
Go to Top of Page

Pete_N
Posting Yak Master

176 Posts

Posted - 04/04/2013 :  13:02:21  Show Profile  Reply with Quote
Hi,

Thanks for the reply. The first query still returns the same errors, however the second query works great .

thank you
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/04/2013 :  14:00:51  Show Profile  Reply with Quote
good. Thanks for reporting back.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000