Calling user defined SQL function that returns a string

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Ken McClain
Posts: 12
Joined: Fri 20 Jan 2006 17:44

Calling user defined SQL function that returns a string

Post by Ken McClain » 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

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » 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;

Ken McClain
Posts: 12
Joined: Fri 20 Jan 2006 17:44

Post by Ken McClain » Tue 30 Dec 2008 13:48

Thank you

Post Reply