Devart Forums

Posted: **Mon 29 Dec 2008 15:16**

Can anyone share an example of calling a user defined sql function that returns a string or any data type? I have a function that I pass in the database name as an argument and it returns the physical_name or path to the mdf or ldf file. Should I be using OUTPUT parameters instead? If so does anyone have a sample they could share?

Thanks

Posted: **Tue 30 Dec 2008 12:52**

To solve this probelm you can create a stored procedure with an OUTPUT parameter or function.
To create stored procedure with OUTPUT parameter you can use the following code:

Code: Select all

CREATE PROCEDURE [Proc_Name]
  @Database_Name varchar(100),
  @Physical_Name  varchar(100) OUTPUT
AS
  SELECT @Physical_Name = ...

To create SQL function you can use the following code:

Code: Select all

CREATE FUNCTION [Func_Name] (
  @Database_Name varchar(100))
RETURNS varchar(100)
AS BEGIN
 RETURN ...
END

To get result you should use the MSStoredProc component and use the following code:

Code: Select all

  MSStoredProc.StoredProcName := proc_name;
  MSStoredProc.ParamByName('Database_Name').AsString := database_name;
  MSStoredProc.Execute;
  Result := MSStoredProc.ParamByName('Physical_Name').AsString;

or in case with function:

Code: Select all

  Result := MSStoredProc.ParamByName('RETURN_VALUE').AsString;

Posted: **Tue 30 Dec 2008 13:48**

Thank you

Devart Forums

Calling user defined SQL function that returns a string

Calling user defined SQL function that returns a string