Operating System - HP-UX
1748280 Members
3666 Online
108761 Solutions
New Discussion юеВ

dynamic sql for sybase 11.0

 
Catherine_3
Occasional Advisor

dynamic sql for sybase 11.0

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

4 REPLIES 4
Catherine_3
Occasional Advisor

Re: dynamic sql for sybase 11.0

Hi all,
Why nobody give me feedback ?
Mark Greene_1
Honored Contributor

Re: dynamic sql for sybase 11.0

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
the future will be a lot like now, only later
Catherine_3
Occasional Advisor

Re: dynamic sql for sybase 11.0

So how about Sybase 12?
How to write it ?

Thanks
Mark Seaman
Advisor

Re: dynamic sql for sybase 11.0

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.