Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

dynamic sql for sybase 11.0

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 where deal_id =' + convert (varchar(12),@deal_id)
select @buffer
exec (@buffer)
select @old_data

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.

the future will be a lot like now, only later
Occasional Advisor

Re: dynamic sql for sybase 11.0

So how about Sybase 12?
How to write it ?

Mark Seaman

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.