Calling user-defined function from adp

[Sara4]

How can I call a user-defined function (scalar-valued sql server 2005) from
an ADP?
The function returns a string, I want to 'save' the return value of the
function in a string variable.

 

[Tom van Stiphout]

On Thu, 13 Dec 2007 05:00:02 -0800, Sara4

VBA code:
Dim sql As String
sql = "select dbo.ToUpper('AbCdEf')"
Debug.Print CurrentProject.Connection.Execute(sql)(0)

T-SQL code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION dbo.ToUpper
(
@s varchar(255)
)
RETURNS varchar(255)
AS
/*
example: select dbo.ToUpper('AbCdEf')
*/
BEGIN
DECLARE @ResultVar varchar(255)
SELECT @ResultVar = UPPER(@s)
RETURN @ResultVar
END
GO

-Tom.