dynamic sql for sybase 11.0

[Catherine_3]

Hi All,
I met a problem
I have a stored procedure that need to use dynamic sql:
I want to put the output to a variable.
I tried many ways, it doesn't work
it told me @old_data isnot defined.

Thanks advanced

(@deal_id int)
AS begin
create table #dealdiff
(tablename varchar(30),
fieldname varchar(30),
old_data varchar(30),
new_data varchar(30))


declare @name varchar(30),
@buffer varchar(200),
@new_data varchar(30),
@old_data varchar(20)
select @name='deal_id'
select @name

select @buffer = 'select @old_data='+'convert(varchar(12),' + @name +') from trading..deal where deal_id =' + convert (varchar(12),@deal_id)
select @buffer
exec (@buffer)
select @old_data
end

[Catherine_3]

Hi all,
Why nobody give me feedback ?

[Mark Greene_1]

11.0 is rather old, perhaps no participants on the list are using it. Have you check Sybase's web site to see if 11.0 supports dynamic sql? You may need to upgrade to 11.5 or 12.

HTH
mark

[Catherine_3]

So how about Sybase 12?
How to write it ?

Thanks

[Mark Seaman]

Sybase did not support dynamic SQL until 12.0. There are still some limitations. I quit being directly involved in Sybase at 11.9.2 or so. There are a few work-arounds you can use with any version of Sybase:

1. Use one stored procedure to generate the SQL you need to exeucte, save that in a temporary file, and execute the file. This is easy to do with isql.

2. Any client-side application can create and execute dynamic SQL. It's static by the time the server sees it. The least painful way is probably in Perl using the sybperl module. You can also use C/C++ with CtLib or Java and JDBC.

If you really have to do this in a stored procedure, check out the 12.0 documentation.