如何在SQL Anywhere中创建动态存储过程？ [英] How to create dynamic stored procedure in SQL Anywhere?

问题描述

我在SQL Anywhere中创建动态sql语句时遇到问题。

I'm having an issue with creating dynamic sql statement in SQL Anywhere.

CREATE PROCEDURE pplAnalysis
AS
BEGIN

  DECLARE @Sql NVARCHAR(4000)

  SELECT @Sql = "select * from cds.ppl"

  EXECUTE(@Sql)

END

当我执行这个程序时， 列'select * from cds.ppl'not found 错误。

When I execute this procedure, I get an Column 'select * from cds.ppl' not found error.

你能告诉我我在做什么错误？

Can you please tell me what am I doing wrong?

推荐答案

问题与语法和 RESULT 条款;在添加分号和 RESULT 子句后，并使用 SET 初始化Sql变量，这里是有效的任何网络服务器版本12.0.1）：

The issue had to do with syntax and the RESULT clause; after adding semicolons, RESULT clause, and used SET to initialize the Sql variable, here is what worked (tested in SQL Anywhere Network Server Version 12.0.1):

drop proc pplAnalysis;

CREATE PROCEDURE pplAnalysis()
RESULT (cnot CHAR(5), cnull CHAR(5), vnot VARCHAR(5), vnull VARCHAR(5), explanation VARCHAR(50))
BEGIN

  DECLARE @Sql NVARCHAR(4000);

  SET @Sql = 'select cnot, cnull, vnot, vnull, explanation from dbo.spaces';

  EXECUTE ( @Sql );

END;

spaces c $ c> dbo 模式，这些列与 RESULT

spaces is a table in the dbo schema and those columns are the same type specified in RESULT

测试这两种方式执行过程，并返回结果：

Tested these two ways to execute the procedure and both returned result:

call pplAnalysis();

cnot  cnull vnot  vnull explanation                                        
----- ----- ----- ----- -------------------------------------------------- 
Execution time: 0.027 seconds
Procedure completed

或

exec pplAnalysis;

cnot  cnull vnot  vnull explanation                                        
----- ----- ----- ----- -------------------------------------------------- 
Execution time: 0.018 seconds

有关详情：

从程序返回结果集

< a href =http://dcx.sybase.com/1200/en/dbreference/create-procedure-user-defined-statement.html =nofollow>创建过程语句

这篇关于如何在SQL Anywhere中创建动态存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！