Andrey Shiryaev Andrey Shiryaev - 1 year ago 166
SQL Question

Use variable inside "from" in firebird

I try use variable inside "from"

SELECT COUNT(*) FROM :T_NAME
, but is not work. How can i fix this? This my code:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE Myfunction
RETURNS(
T_NAME varchar(100),
NUM_RECORDS integer
)
AS
BEGIN
FOR SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE (RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL) AND RDB$VIEW_BLR IS NULL
ORDER BY RDB$RELATION_NAME
INTO :T_NAME
DO
BEGIN
SELECT COUNT(*) FROM :T_NAME
INTO :NUM_RECORDS;
SUSPEND;
END
END^
SET TERM ; ^

Answer Source

You can't parametrize object names. You will need to dynamically build the query (which if you aren't careful may leave you open to SQL injection).

Specifically you need to use:

...
BEGIN
    EXECUTE STATEMENT 'select count(*) from "' || T_NAME || '"'
        INTO :NUM_RECORDS;
    SUSPEND;
END

This should be safe except for table names that contain double quotes. I haven't explicitly checked the behavior with single quotes in an object name, which technically is possible. You may need to take extra steps to protect against these forms of SQL injection if you use this in real production code.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download