Friday, May 20, 2005


Dynamic SQL

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


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

Sybase isql prompt:

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


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

Simulating dynamic SQL through CIS

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

declare @sqlStatement varchar(2048)
select @sqlStatement='select PecEventNames.EventName, PecEvent.EventID, PecEvent.EventType, PecEvent.FirmID into #tmpTable
from PecEventNames,PecEvent where PecEvent.EventType =10000
and EventTime <= "11/29/2009" and PecEventNames.EventType=PecEvent.EventType
order by EventTime, FirmID, UserName'

print @sqlStatement
exec @sqlStatement

the above statement fails,,, can u let me know why ?? is this becoz of 2 tables,,, the message is as follows

The name 'FirmID into #tmpTable
from PecEventNames,PecEvent where PecEvent' is too long. Maximum length is 30.

Can you please give me the code for dynamic sql which accepts 10 values in one parameter
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?