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)?