CODEWITHSUNDEEP
t-sqlsybasesap-ase
Yash
Yash

Reputation: 75

How to do Select from a dynamic variable inside a store proc in sybaseASE

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

"select @TableName from @TableName"

But Sybase ASE SQL is not allowing 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 tablename into a Results table if it match some condition(I haven't mention that here because I don't want to confuse you).

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

Could anyone provide some alternative or any solution on it ?

Upvotes: 1

Views: 448

Answers (2)

Yash
Yash

Reputation: 75

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.

Upvotes: 3

markp-fuso
markp-fuso

Reputation: 35256

If the objective is to insert the value of @TableName into #Results then either of the following should suffice:

INSERT INTO #Results select @TableName
INSERT INTO #Results values (@TableName)

If the intent is to insert @TableName into #Results but only if there's a user table with this name in the current database then try:

INSERT INTO #Results select name from sysobjects where type = 'U' and name = @TableName

If this doesn't answer the question then please update the question with more details as well as some examples of @TableName values that do and do not work.

Upvotes: 1

Related Questions