|
Simulating dynamic SQL through CIS This is a demo of how you can use certain CIS (Component Integration Services) features in ASE 11.5 or 11.9 to simulate dynamic SQL.
ASE 12.0 contains a new feature called execute immediate which lets you execute a text string containing dynamically created SQL queries; however, this isn't possible in ASE 11.5.x or 11.9.x. This demo shows how to achieve basically the same functionality in ASE 11.5.x / 11.9.x.
Below on this page, you can download SQL scripts which will create a stored procedure named sp_exec_dynsql. This procedure takes a text string as an argument and executes the SQL statements contained in the text string. Here's an example: the below procedure myproc takes two arguments, being a column name and a table name. It will then select the specified column from the specified table using sp_exec_dynsql:
create procedure myproc
@col_name varchar(32),
@tab_name varchar(70)
as
begin
declare @cmd varchar(255)
select @cmd = "select " + @col_name +
"from " + @tab_name
exec sp_exec_dynsql @cmd
end
You can then do things like: 1> exec myproc "pub_name", "pubs2..publishers"
2> go
pub_name
----------------------------------------
New Age Books
Binnet & Hardley
Algodata Infosystems
(3 rows affected)
Behind the scenes, this works as follows: using a CIS-related trick, you can access the local server as if it were a remote server. You can then use sp_remotesql to execute any command string in that not-so-remote server. Because sp_remotesql takes a text string as an argument, you can create any SQL statement dynamically and then execute it.
Note that you must be running ASE 11.5 or later (earlier versions do not have the CIS functionality included), and you must run set up the server for these tricks to work.
Note that there are also some other tricks to simulate dynamic SQL which will work in any ASE version, including versions 4.x, 10 and 11.0: go here to find out how to use it.
Installation instructions
- Download everything you need here.
- Now you should run two setup scripts to prepare your server for using CIS: run CISXP_SETUP1.SQL, restart your server and then run CISXP_SETUP2.SQL. Note that you should ensure your @@servername matches your servername in the interfaces file.
- Next, you can run the script SP_EXEC_DYNSQL.SQL, and then finally you're ready to execute the procedure sp_exec_dynsql.
- Lastly, you should be aware of some limitations and potential problems related to these tricks: follow this link for more info.
关闭本页
[办公软件]如何在office(PowerPoint,Word,Excel)中制作带圈的… [办公软件]批量删除Office文档(word,excle,powerpoint)中的超…
[办公软件]Office(Word,Excel)密码破解软件(Office Password… [办公软件]在powerpoint中快速插入word表格
[办公软件]快速将Word文档转换为PPT演示文档 [办公软件]Word、Excel和PowerPoint文件格式兼容包(简体中文…
[办公软件]在Office(word,excel)中输入各级钢筋符号的方法 [办公软件]Office文档(Word,PPT)的作者、备注、最后修改时间…
[办公软件]让EXCEL,WORD时间自动更新 [网络安全]激活型触发式AutoRun.inf病毒和rose病毒的清除方案
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
