sybase 11.5 & 11.9.2 使用动态sql

azhe

请教各位高手：  
“把一些sql语句片断（如表名，字段名，where条件等）作为字段放在数据表中，  
   在一个存储过程中，取出这些字段，并组成sql语句执行，  
   应如何实现？  
“  
听说12.0以下11.5以上，用remote_sql这个系统过程模拟实现 ，能给我个例子吗？

bluetune

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

我的sybase版本11。5的，不能用exec这个命令啊。

chenfeng825

11.5和1192还没有支持动态sql,不过可以通过cis来模拟执行。具体做法如下:
执行CISXP_SETUP1.SQL并重启后执行CISXP_SETUP2.SQL，这两个脚本里面的server_name需要将自己的server_name替代既可以。
具体调用
参考附件

chenfeng825

附件没贴上去，具体看看下面的链接就好
http://www.sypron.nl/dynsqlcis.html

azhe

问题已经解决了！非常感谢！！

zhangyh123

不过 ，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.
*
*
* 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
*-----------------------------------------------------------------------------
*/
sp_configure "xp_cmdshell context", 0
go

/*
* define a remote server which is actually pointing to yourself
*/
if not exists (select * from master.dbo.sysservers
               where srvname = "YOUR_SERVER_NAME_MYSELF"
   exec sp_addserver YOUR_SERVER_NAME_MYSELF, null, YOUR_SERVER_NAME
go

/*
* set up remote access authorisation
* There's various ways of doing this. The proper one would be to
* do "sp_addexternlogin YOUR_SERVER_NAME_MYSELF, sa, sa, <sa-password>;".
* Easier would be "sp_addremotelogin YOUR_SERVER_NAME_MYSELF", but there's
* a built-in check that doesn't allow this for local servers. So either
* remove that check, or manually insert a row in master..sysremotelogins
* as happens below. In the below case, this will allows all logins to
* do remote access. If you want only a specific login (say 'zzz') to
* be enabled, insert the values (0,'zzz',suser_id('zzz'),0).
*/
sp_configure 'allow updates', 1
go

if not exists (select * from master.dbo.sysremotelogins
               where remoteserverid = 0
                 and remoteusername = NULL
                 and suid = -1
                 and status = 0)
    insert master.dbo.sysremotelogins values (0,null,-1,0)
go

sp_configure 'allow updates', 0
go

/*
* end
*/
============================================================================================
/*
* This script creates a procedure sp_exec_dynsql which will execute
* a string containg SQL statements.
* This makes use of a CIS-related trick, to access the server as if it
* were a remote server. This allows the use of dynamically
* generated SQL through sp_remotesql.
*
* The purpose of this procedure is to demonstrate some of the
* CIS capabilities.
*
* Installation:
* 1. First perform some server-level setups for CIS to work correctly.
*    These setups are in two scripts that can be downloaded from
*    http://www.euronet.nl/~syp_rob/cisxp_setup.html;
*
* 2. Next, run this script;
*
* 3. Finally, execute "sp_exec_dynsql <string-with-SQL-cmds>;"
*
*
* Example:
* ========
* The below procedure 'myproc' will 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 now do things like:
*
* 1>; exec myproc "pub_name", "pubs2..publishers"
* 2>; go
*  pub_name
*  ----------------------------------------
*  New Age Books
*  Binnet & Hardley
*  Algodata Infosystems
*
*  (3 rows affected)
*
*
* 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 sybsystemprocs
go

/*
* create sp_exec_dynsql
*/
if object_id("sp_exec_dynsql" <>; NULL
begin
    drop procedure sp_exec_dynsql
end
go

create procedure sp_exec_dynsql
/* Copyright (c) 1999 Rob Verschoor/Sypron B.V. */
  @p_cmd varchar(255) = "%"
as
begin
declare @remoteserver varchar(32)

   /*
    * suppress rubbish
    */
   set nocount on

   /*
    * make sure this is 11.5 or later
    */
   if substring(@@version, 1, 26) != "Adaptive Server Enterprise"
   begin
      print "This procedure only works for ASE version 11.5 or later."
      return (-1)
   end

   /*
    * check server name is defined
    */
   if @@servername = NULL
   begin
      print "The @@servername must be defined."
      print "Run ""sp_addserver SERVERNAME, local"", and restart the server"
      return (-1)
   end

   /*
    * Check a remote server is set up, pointing to the local server
    * (this is the basic trick that makes this whole procedure work)
    */
   select @remoteserver = srvname
   from master..sysservers
   where srvnetname = @@servername
     and srvname    != @@servername

   if (@@rowcount = 0) or (@remoteserver = ""
   begin
      print "A remote server must be set up in master..sysservers,"
      print "pointing to this local server."
      print "First run the setup scripts CISXP_SETUP1.SQL & CISXP_SETUP2.SQL"
      print "to fix this. These can be downloaded from"
      print "http://www.euronet.nl/~syp_rob/cisxp_setup.html"

      return (-1)
   end

   /*
    * execute the specified command
        */
   exec sp_remotesql @remoteserver, @p_cmd

end
go
grant execute on sp_exec_dynsql to public
go

/*
* end
*/