请教各位高手：
“把一些sql语句片断（如表名，字段名，where条件等）作为字段放在数据表中，
在一个存储过程中，取出这些字段，并组成sql语句执行，
应如何实现？
“
听说12.0以下11.5以上，用remote_sql这个系统过程模拟实现 ，能给我个例子吗？
:!:
bluetune 回复于：2004-05-26 08:40:10
declare @table_name varchar(40)
declare @sql varchar(30)
declare @where_clause varchar(50)
select @table_name = 'sysdatabases'
select @where_clause = 'where id = 1'
select @sql = 'select * from ' + @table_name + @where_clause
exec(sql)
azhe 回复于：2004-05-26 10:28:56
我的sybase版本11。5的，不能用exec这个命令啊。
chenfeng825 回复于：2004-05-26 11:20:41
11.5和1192还没有支持动态sql,不过可以通过cis来模拟执行。具体做法如下:
执行CISXP_SETUP1.SQL并重启后执行CISXP_SETUP2.SQL，这两个脚本里面的server_name需要将自己的server_name替代既可以。
具体调用
参考附件
chenfeng825 回复于：2004-05-26 11:30:21
附件没贴上去，具体看看下面的链接就好
http://www.sypron.nl/dynsqlcis.html
azhe 回复于：2004-05-28 16:14:13
问题已经解决了！非常感谢！！
zhangyh123 回复于：2004-05-29 00:42:48
:D
不过 ，sp_remotesql的用法有 如下说法：
* Firstly define your local server to be a remote server using
sp_addserver LOCALSRV,sql_server[,INTERFACENAME]
go
* Enable CIS
sp_configure "enable cis",1
go
* Finally, use sp_remotesql, sending the sql to the server defined in point
1.
declare @sqlstring varchar(255)
select @sqlstring = "select count(*) from master..sysobjects"
sp_remotesql LOCALSRV,@sqlstring
go
----------------------------------------------------------------------------
/*
* CISXP_SETUP1.SQL
*
* Some server setups for CIS & XP. This will work only in ASE version
* 11.5 or later.
*
* Note that you should first change this script: do a global change of
* the string "YOUR_SERVER_NAME" to your actual SQL server name; ensure
* this is identical to the change in script CISXP_SETUP2.SQL.
*
* Note that there's a server shutdown at the end.
* After restarting the server, run script CISXP_SETUP2.SQL.
*
*
* Copyright note & Disclaimer :
* =============================
* This software is provided "as is" -- no warranty.
* This software is for demonstration purposes only. It may not work correctly
* and/or reliably in a production environment.
* You can use this software free of charge for your own professional,
* non-commercial purposes.
* You are not allowed to sell this software or use it for any commercial
* purpose. You may (re)distribute only unaltered copies of this software, which
* must include this copyright note.
*
* Please send any comments, bugs, suggestions etc. to the below email address.
*
* (c) 1999 Copyright Rob Verschoor
* Sypron B.V.
* P.O.Box 10695
* 2501 HR Den Haag
* The Netherlands
*
* Email: rob@sypron.nl
* WWW : http://www.euronet.nl/~syp_rob
*-----------------------------------------------------------------------------
*/
use master
go
/*
* Check we're on ASE 11.5 at least
*/
if substring(@@version, 1, 26) != "Adaptive Server Enterprise"
begin
print "***"
print "***"
print "*** You can only use CIS and/or XP features on ASE version 11.5 or later."
print "*** These features do not exist in your current version of ASE, sorry..."
print "***"
print "***"
end
go
/*
* Fix bug in definition of @@servername on ASE 11.5
*
* (If you created your server with the default Sybase
* 'srvbuild' or 'srvbuildres' tools, this will have
* been set up wrong. For an alternative, check out
* the free tool "sybinit4ever" at
* http://www.euronet.nl/~syp_rob/si4evr.html)
*/
if exists( select * from master..sysservers
where srvname="local" )
exec sp_dropserver local
go
if not exists( select * from master..sysservers
where srvid=0 )
exec sp_addserver YOUR_SERVER_NAME, local
go
/*
* add XP servername if not there yet
* Note that the interfaces file should also contain an entry
* for this XP server
*/
if not exists( select * from master..sysservers
where srvname="YOUR_SERVER_NAME_XP" )
exec sp_addserver YOUR_SERVER_NAME_XP, null, YOUR_SERVER_NAME_XP
go
/*
* some config options to set up CIS & XP
*/
sp_configure "enable cis", 1
go
sp_configure "max cis remote connections", 5
go
/*
* now restart the server for these changes to take effect:
*/
shutdown
go
/*
* restart the server now, and run the script "CISXP_SETUP2.SQL"
*/
===============================================================================
/*
* CISXP_SETUP2.SQL
*
* Some server setups for CIS & XP. This will work only in ASE version
* 11.5 or later.
*
* First run script SETUP1.SQL and restart the server.
* Then, run this script.
*
* Note that you should first change this script: do a global change of
* the string "YOUR_SERVER_NAME" to your actual SQL server name; ensure
* this is identical to the change in script CISXP_SETUP1.SQL.
*
*
[1] [2] [3] 下一页 |
