Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help Needed with function

Author  Topic 

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-04-04 : 10:43:34
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-04 : 11:03:39
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

181 Posts

Posted - 2013-04-04 : 13:02:21
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-04 : 14:00:51
good. Thanks for reporting back.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -