Calling a scalar value sql server function from ADP?

[sara4]

How can I call a user-defined function that returns a string from ADP? I'd like to 'save' the returning value into a string variable.

[Jim Doherty]

How can I call a user-defined function that returns a string from ADP? I'd like to 'save' the returning value into a string variable.

Embed it within your SQL query if that is what are working with....for instance imaginary UDF embeded within an imaginary sql statement thus returning a column namely the bolded bits shown in the function. he return value depending on the parameter passed to it in @vehicle (passed via the value contained in vehicle field)

SELECT FirstName,Surname,Vehicle, dbo.UDF_TransportComment(Vehicle) AS Comment From MyTable

If the above dataset was a recordsource for a form for instance, then you would grab the value from the forms textbox servicing the Comment field in much the same way as done normally ie

 Dim strComment as String 
strComment=Me!Comment
 

Server side user defined function

 
CREATE FUNCTION dbo.UDF_TransportComment
( @Vehicle varchar(100) )
RETURNS varchar(255)
AS
BEGIN
RETURN CASE 
         WHEN @Vehicle IS NULL THEN 'Not known'
WHEN @Vehicle ='Cycle' THEN 'Likes to Keep fit'
WHEN @Vehicle ='Harley Davidson' THEN 'Super cool dude'
WHEN @Vehicle ='Mono cycle' THEN 'Even cooler!'
     END
END
 

Jim :)

[sara4]

What if the select was not a recordsource?

What if I want ot get the returning value in a variable?

I'd like to able to do sth like:

Dim strSomeVariable as String
strSomeVariable = "select [dbo].[fx_GetCode] (Me.ID_COL.Value)"


Here's UDF function:
ALTER FUNCTION [dbo].[fx_GetCode]
(
@Param1 varchar(8)
)
RETURNS varchar(6)
AS
BEGIN
DECLARE @Code as varchar(6)

-- Add the T-SQL statements to compute the return value here
SELECT @Code = CODE_COL
from T_TABLE
WHERE ID_COL = @Param1

-- Return the result of the function
RETURN @Code

END

[Jim Doherty]

Hi Sara,

You wouldn't need necessarily need an SQL Server user defined function to return this, an output parameter in a stored procedure could return what you require in this circumstance.

If all you are asking is for is a single return value to be assigned to a variable in the client application then passing in your @Param1 parameter via the command object to a stored procedure and returning the variable @Code as an Output parameter to your 'Access' application would give you your required result.

 
CREATE PROCEDURE dbo.usp_GetCode
(@Param1 int,@Code varchar(8) Output)
SELECT @Code = CODE_COL
     from T_TABLE
WHERE ID_COL = @Param1 
- Return the result in the output parameter 
SELECT @Code
GO
 

This is how you might then call it from your code in Access
in line with your posted variable

 
    Dim cmd As ADODB.Command
    Dim par As ADODB.Parameter    
    Dim strSomeVariable as String
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "dbo.usp_GetCode"
    cmd.CommandType = adCmdStoredProc
 
    'Define the input and output variables and append them to the collection    
    Set par = cmd.CreateParameter("@Param1", adVarChar, adParamInput, 8)
    cmd.Parameters.Append par
    Set par = cmd.CreateParameter("@Code", adInteger, adParamOutput)
    cmd.Parameters.Append par
 
'we can assign the value for the input variable to be passed to SQL Server by reference to an access screen controls currently displayed value for instance. 
 
cmd.Parameters("@Param1") = Forms!frmMyFormName!MyControlName
 
    cmd.Execute
 
'and then assign the stored procedure return value to the access variable
 
    strSomeVariable = cmd.Parameters("@Code").Value
 

As an aside ...user defined functions certainly have their place but are not visually exposed within the graphical user interface (GUI) of an Access ADP file whereas stored procedures are, you can see, and edit the relevant syntax.

Trust this helps you,

Regards

Jim :)