Search code examples
sqlsybasesap-ase

Select variable dynamic value

(I already declared the variables)

I have the below query

select @VCOUNT = count(1) from @TMP_NAME||'TAB1'||@TIME

giving the below error

Incorrect syntax near '@TMP_NAME'

The tmp_name and time are dynamic.

The query was like this and it worked:

select @VCOUNT = count(1) from tab1

How to add dynamic to select variable?

Solution

  • Get the entire select statement into a string containing Sql e.g. @Sql

    select @Sql = 'select @VCOUNT = count(1) from ' + @TMP_NAME + 'TAB1' + @TIME

    then use EXEC for dynamic sql:

    EXEC(@Sql)

    @VCOUNT will be set inside the dynamic sql and will be available outside the dynamic statement.

    As it's dynamic you'll not have the benefit of stored query plans for this statement if this is inside an SP, but that might not be significant, it depends.