using variable as tablename in stored procedure (INTERBASE)

marconi8

i have spended all day, but i still cannot find solution for this method

stored procedure (var_tablename varchar(15))

begin

select * from :var_tablename into :some other var;
suspend;

end

why i cannot in stored procedure use input variable as table name ?

tomhath

You need to put the sql into a string and use EXEC. Here's an example from http://www.4guysfromrolla.com/webtech/020600-1.shtml

CREATE PROCEDURE MyProc
  (@TableName varchar(255),
   @FirstName varchar(50),
   @LastName varchar(50))
AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT * FROM " +
               @TableName + "WHERE FirstName = '"
               + @FirstName + "' AND LastName = '"
               + @LastName + "'"

-- Execute the SQL statement
EXEC(@SQLStatement)

tomhath

This might not work with INTERBASE, but there might be another way using EXEC or EXECUTE.

marconi8

in interbase i can use cvharacter like @

but i have this execute, but i dont now how to use this execute, maybe u can help me ???

thanks

tomhath

Try something simple first:

create procedure test as
declare @x varchar(80)
select @x = "select * from sometable"
execute @x

If you can get that to work, the rest will be easy. But INTERBASE might not allow that.

marconi8

sorry but i cannot use this symbol @, i always get error

maybe some other ideas

also thanks for helping