SybaseBlog

I got the following question this past week from a colleague.

QUESTION:

Is there a way to query a field by having it as a variable?

Example
Sybase isql prompt:

declare @var varchar(20)
@var="param_0"
select @var from MyTable where @var <> NULL

ANSWER:

Yes, it is possible. Incidentally, this is called "Dynamic SQL." That basically means any SQL code that is generated on the fly (ie. dynamically), as opposed to being previously prepared.

Here is how to achieve your result:

declare @c varchar(100)
declare @var varchar(20)
select @c = "select " + @var + " from MyTable where " + @var + " <> NULL"
exec (@c)

This only works in Sybase ASE 12.0 or greater.

There is also a way to do dynamic SQL in 11.x and earlier as well, using sp_remote_sql, but it is more complex.

I don't want to re-invent the wheel because this matter has been very well addressed by Rob Verschoor. Here is where to find his articles.

Simulating dynamic SQL in ASE
http://www.sypron.nl/dynsql.html

Simulating dynamic SQL through CIS
http://www.sypron.nl/dynsqlcis.html

There are plenty of other articles about "Dynamic SQL" in general. I just googled it and found a pretty reasonable treatment of the subject, which also covers SQL Injection, one of the security risks of Dynamic SQL.

The Curse and Blessings of Dynamic SQL
Erland Sommarskog
http://www.sommarskog.se/dynamic_sql.html