topic Re: dynamic sql for sybase 11.0 in Operating System - HP-UX

dynamic sql for sybase 11.0

Catherine_3 — Fri, 28 Jun 2002 12:32:50 GMT

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

Re: dynamic sql for sybase 11.0

Catherine_3 — Fri, 28 Jun 2002 13:43:53 GMT

Hi all,
Why nobody give me feedback ?

Re: dynamic sql for sybase 11.0

Mark Greene_1 — Fri, 28 Jun 2002 15:38:38 GMT

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

Re: dynamic sql for sybase 11.0

Catherine_3 — Fri, 28 Jun 2002 15:44:11 GMT

So how about Sybase 12?
How to write it ?

Thanks

Re: dynamic sql for sybase 11.0

Mark Seaman — Sat, 29 Jun 2002 04:29:04 GMT

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.