t-sqlsybasesap-ase

How to do Select from a dynamic variable inside a stored procedure in sybaseASE


I am writing a stored procedure that will get some table name as a parameter and it will do

"select @TableName from @TableName"

But Sybase ASE SQL does not allow me to do that. I am getting this message

Cannot select from or insert/update variable '@TableName' because it is not a table variable.

Here is my stored procedure:

CREATE PROCEDURE Test_result 
    @TableName VARCHAR(40)
AS 
BEGIN
    CREATE TABLE #Results (TableName nvarchar(370))

    INSERT INTO #Results 
        SELECT @TableName FROM @TableName 

    SELECT * FROM #Results
END

EXEC Test_result 'sometablename'

This will simulate my actual problem. I want to insert a table name into a #Results table if it match some condition (I haven't mentioned that here because I don't want to confuse you).

Note: I want to do a quick select query from a table name which I passed to the stored procedure. I don't want to create again the table structure because that stored procedure may get another table name whose table DDL is different

Could anyone provide some alternative or any solution on it ?


Solution

  • Sorry for delay in response. I have found myself a workaround for that which I would like to share.

    INSERT INTO #Results select @TableName from @TableName 
    

    To make this working, use a variable to store this query and execute using EXEC statement in sybase.

    The workaround will be,

    BEGIN
        SET @sqlquery='INSERT INTO #Results select @TableName from @TableName '
        EXEC(@sqlquery)
    END
    

    This solved my problem as @tablename variable we can't directly used to replace the value of a table.