[SOLVED] Using a variable in a sqsh script is not working while querying in sql server

Using a variable in a sqsh script is not working while querying in sql server

I am trying to use a sqsh variable in a sql server query where clause but not able to make it work. The following is a simplistic simulation of problem I am facing. Can someone please help me fixing this

This works as expected

select  * from information_schema.tables where table_name = 'PHONES';

but the following will not work

\set tableName=PHONES;

select * from information_schema.tables where table_name = $tableName;
     Error Message:: Invalid column name 'PHONES'

select * from information_schema.tables where table_name = '$tableName';
     No rows are returned as it searches for a table $tableName

select * from information_schema.tables where table_name = "$tableName";
     Error Message:: Invalid column name 'PHONES'.

Solution

To explain what is happening here you should take a look at the SQL buffer that is being send to the server after variable expansion. In order to do that you should skip the ';' shortcut and provide '\go -e' on the next line instead (without the quotes). Note that this will probably not reveal the SQL buffer in case of an error.

The first line will expand to:

select * from information_schema.tables where table_name = PHONES

Here PHONES is interpreted as a column name in your table, however since this column name does not exists, SQL server responds with an error message.

The second line will expand to:

select * from information_schema.tables where table_name = '$tableName'

Due to the single quotes, the variable is not expanded by sqsh and sent to the server as is, hence the empty result set.

The third line will expand to:

select * from information_schema.tables where table_name = "PHONES"

This looks more like a string search argument, but due to the fact that the QUOTED_IDENTIFIER option is probably on by default, SQL server is still looking for a column named PHONES.

In order to solve this, you should provide single quotes, but still want sqsh to expand the variable. You can do this by escaping the single quotes like:

select * from information_schema.tables where table_name = \\'$tableName\\';

Hope this helps.