question

shiftysamr avatar image
shiftysamr asked

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.

functionssyntaxsql server 2005udf
4 comments
10 |1200 characters needed characters left characters exceeded

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

What's the function definition?

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

0 Likes 0 ·
shiftysamr avatar image shiftysamr ThomasRushton ♦♦ ·

I attached the function. Thanks, Thomas.

0 Likes 0 ·
shiftysamr avatar image shiftysamr ThomasRushton ♦♦ ·

@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 Likes 0 ·

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

0 Likes 0 ·
shiftysamr avatar image
shiftysamr answered

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 characters needed characters left characters exceeded

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

shiftysamr avatar image
shiftysamr answered

@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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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