--- In firebird-support@yahoogroups.com, "sasidhardoc"
<madhusasidhar@...> wrote:

>
> I am trying to port a MS SQL Database to Firebird (2.0). I have a

fair

> number of stored procedures (MS SQL) that look like this:
> ALTER PROCEDURE [dbo].[SYS_LKUPCustomsProc]
> (
> @CurrentCount Integer,
> @TableName varchar(30),
> @SearchField varchar(30) = NULL,
> @SearchString varchar(255) = NULL)
> AS
> BEGIN
>
> Declare @N as int
> DECLARE @SQLString as nvarchar(500);
>
> -- Set Top N records
> SET @N= @CurrentCount + 10;
>
> -- Build Query
> if @SearchString is NULL
> BEGIN
> SET @SQLString=N'SELECT TOP ' + cast(@N as

nvarchar(5)) + ' * FROM

> ' + @TableName + ' WHERE ' +
> Right(@TableName,CharIndex('_',Reverse(@TableName))-1)

+ '_WasDeleted

> Is NULL'
> END
> else
> BEGIN
> SET @SQLString=N'SELECT TOP ' + cast(@N as

nvarchar(5)) + ' * FROM

> ' + @TableName + ' WHERE ' + @SearchField + ' like ''' +

@SearchString

> + '%''' + + ' AND ' +
> Right(@TableName,CharIndex('_',Reverse(@TableName))-1)

+ '_WasDeleted

> Is NULL'
> END
>
> -- Execute Retreive
> EXECUTE sp_executesql @SQLString
> END
> The problem that I am facing is that in Firebird, I cannot create

the

> procedure without OUTPUT parameters. However, since I do not know
> which table the SP will be executed against, I cannot create output
> parameters. Is there any other way - other that reworking the
> application itself (to use Query)?
>

You can use output parameters, simple example:

CREATE OR ALTER PROCEDURE BLAH
(
INPARAM1 INTEGER,
INPARAM2 VARCHAR(10)
)
RETURNS
(
OUTPARAM1 INTEGER,
OUTPARAM2 VARCHAR(10);
)
AS
BEGIN
OUTPARAM1 = :INPARAM1 + 1;
OUTPARAM2 = UPPER(:INPARAM2);
SUSPEND;
END
^

You can use the EXECUTE STATEMENT directive as documented in the
release notes of Firebird 1.5 to dynamically build a query based on
particular input parameters.

As someone new to Firebird, you should not use Firebird 2 unless you
need a feature not implemented in Firebird 1.5 (Firebird 2 is RC!)

As you are dynamically building your query at runtime, it is not very
efficient. The engine can not reuse the plan, so your prepare time
will be just as high on subsequent operation. Also, as you can query
from an arbitrary table, your dependencies will not be very clear.

Adam