SQL Server 2005 UDF completes with an error

Question

question

shiftysamr asked Dec 2, '19

SQL Server 2005 UDF completes with an error

Greetings,

I am new to this and just wrote my first ever table valued UDF for SQL server 2005. When I run the query:

SELECT 
	[Tenant Id], 
	[Tenant Name], 
	[Billed], 
	[Description] 
FROM 
	DLS_tenant_bill(12345, '1-nov-2019', '1-dec-2019', 100) 
ORDER BY 
	[Tenant Id], 
	[ORDER]

it returns all rows as requested but completes with an error:

	Msg 102, Level 15, State 1, Line 9223372036854775807
	Incorrect syntax near '1'.
	Msg 102, Level 15, State 1, Line 9223372036854775807
	Incorrect syntax near '1'.
	Command completed successfully. (6125 results).

I would greatly appreciate any help in trying to figure this out. Many thanks.

functions syntax sql server 2005 udf

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Dec 02, 2019 at 09:45 AM

What's the function definition?

Judging by the errors, I'm guessing that they're related to date handling.

0 ·

shiftysamr ThomasRushton ♦♦ commented · Dec 02, 2019 at 12:01 PM

I attached the function. Thanks, Thomas.

0 ·

shiftysamr ThomasRushton ♦♦ commented · Dec 02, 2019 at 12:22 PM

@ThomasRushton

I did a bit more troubleshooting and it appears that the error is coming from this code. Oddly the same code runs without errors when not used within function.

/*
	
	Bill outbound call detail
	
*/
SELECT 
	4 AS [ORDER], 
	ISNULL(tenant_id, 0) AS [Tenant Id], 
	tenants.name AS [Tenant Name], 
	round(vcallrate * 2 * ceiling(CONVERT(DECIMAL(10,2), vbillablesec)/ 60), 3) * (@Markup / 100+1) AS [Billed], 
	CONVERT(VARCHAR, voipcalldat.vstarttime, 22) + 
		' Outbound call -- ' + 
		voipcalldat.vsource + 
		' TO ' + 
		voipcalldat.vdestination + 
		' (' + 
		CONVERT(VARCHAR, (ceiling(CONVERT(DECIMAL(10,2), vbillablesec)/ 60))) + ' minutes)' AS [Description] 
FROM 
	voipcalldat, 
	custrate, 
	(
		SELECT 
			DISTINCT tenantid, 
			name 
		FROM 
			DLS_pbx_tenant 
		WHERE 
			custid = @platid
	) tenants 
WHERE 
	tenants.tenantid = voipcalldat.tenant_id 
	AND voipcalldat.crid = custrate.crid 
	AND custrate.rgid = 539 
	AND custrate.custid = @platid 
	AND vstarttime BETWEEN @From AND @To 
	AND vsourceplatid IS NOT NULL

0 ·

JohnM commented · Dec 02, 2019 at 06:48 PM

Unless I'm crazy and just not seeing it, I don't see the function definition attached?

0 ·

Answer 1 · 2019-12-02T18:52:52Z

shiftysamr answered Dec 2, '19

Hmm... I thought it was attached to the previous message. Let me try again. It said "uploaded" but I don't see it.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2019-12-02T18:55:32Z

shiftysamr answered Dec 2, '19

@ThomasRushton Let me just cut and paste here:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION DLS_tenant_bill (@platid INT, @From DATETIME, @To DATETIME, @Markup INT)
RETURNS TABLE
AS
RETURN
(
/*
	Bill outbound call detail
*/
SELECT 
	4 AS [ORDER], 
	ISNULL(tenant_id, 0) AS [Tenant Id], 
	tenants.name AS [Tenant Name], 
	round(vcallrate * 2 * ceiling(CONVERT(DECIMAL(10,2), vbillablesec)/ 60), 3) * (@Markup / 100+1) AS [Billed], 
	CONVERT(VARCHAR, voipcalldat.vstarttime, 22) + 
		' Outbound call -- ' + 
		voipcalldat.vsource + 
		' TO ' + 
		voipcalldat.vdestination + 
		' (' + 
		CONVERT(VARCHAR, (ceiling(CONVERT(DECIMAL(10,2), vbillablesec)/ 60))) + ' minutes)' AS [Description] 
FROM 
	voipcalldat, 
	custrate, 
	(
		SELECT 
			DISTINCT tenantid, 
			name 
		FROM 
			DLS_pbx_tenant 
		WHERE 
			custid = @platid
	) tenants 
WHERE 
	tenants.tenantid = voipcalldat.tenant_id 
	AND voipcalldat.crid = custrate.crid 
	AND custrate.rgid = 539 
	AND custrate.custid = @platid 
	AND vstarttime BETWEEN @From AND @To 
	AND vsourceplatid IS NOT NULL 
)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question