By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Calling a scalar value sql server function from ADP?

P: 9
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.
Dec 13 '07 #1
Share this Question
Share on Google+
3 Replies


Jim Doherty
Expert 100+
P: 897
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)

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1.  Dim strComment as String 
  2. strComment=Me!Comment
  3.  

Server side user defined function

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE FUNCTION dbo.UDF_TransportComment
  3. ( @Vehicle varchar(100) )
  4. RETURNS varchar(255)
  5. AS
  6. BEGIN
  7. RETURN CASE 
  8.          WHEN @Vehicle IS NULL THEN 'Not known'
  9. WHEN @Vehicle ='Cycle' THEN 'Likes to Keep fit'
  10. WHEN @Vehicle ='Harley Davidson' THEN 'Super cool dude'
  11. WHEN @Vehicle ='Mono cycle' THEN 'Even cooler!'
  12.      END
  13. END
  14.  

Jim :)
Dec 13 '07 #2

P: 9
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
Feb 14 '08 #3

Jim Doherty
Expert 100+
P: 897
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE PROCEDURE dbo.usp_GetCode
  3. (@Param1 int,@Code varchar(8) Output)
  4. SELECT @Code = CODE_COL
  5.      from T_TABLE
  6. WHERE ID_COL = @Param1 
  7. - Return the result in the output parameter 
  8. SELECT @Code
  9. GO
  10.  
This is how you might then call it from your code in Access
in line with your posted variable

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim cmd As ADODB.Command
  3.     Dim par As ADODB.Parameter    
  4.     Dim strSomeVariable as String
  5.     Set cmd = New ADODB.Command
  6.     cmd.ActiveConnection = CurrentProject.Connection
  7.     cmd.CommandText = "dbo.usp_GetCode"
  8.     cmd.CommandType = adCmdStoredProc
  9.  
  10.     'Define the input and output variables and append them to the collection    
  11.     Set par = cmd.CreateParameter("@Param1", adVarChar, adParamInput, 8)
  12.     cmd.Parameters.Append par
  13.     Set par = cmd.CreateParameter("@Code", adInteger, adParamOutput)
  14.     cmd.Parameters.Append par
  15.  
  16. '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. 
  17.  
  18. cmd.Parameters("@Param1") = Forms!frmMyFormName!MyControlName
  19.  
  20.     cmd.Execute
  21.  
  22. 'and then assign the stored procedure return value to the access variable
  23.  
  24.     strSomeVariable = cmd.Parameters("@Code").Value
  25.  
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 :)
Feb 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.