如何在SQL中创建动态存储过程?

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

我在sql Anywhere中创建动态sql语句有一个问题。

CREATE PROCEDURE pplAnalysis
AS
BEGIN

  DECLARE @Sql NVARCHAR(4000)

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

  EXECUTE(@Sql)

END

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

当我执行这个过程时，我从cd中得到一个列的select *。ppl的没有发现错误。

Can you please tell me what am I doing wrong?

你能告诉我我做错了什么吗?

4 个解决方案

#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):

这个问题与句法和结果子句有关;在添加分号、RESULT子句和使用SET来初始化Sql变量之后，这里是工作(在sqlanywhere网络服务器版本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 is a table in the dbo schema and those columns are the same type specified in RESULT

空间是dbo模式中的一个表，这些列是在结果中指定的相同类型。

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

For more details:

更多细节:

Returning result sets from procedures

从过程返回结果集。

Create procedure statement

创建程序语句

#2

Use single quotes.

使用单引号。

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

#3

Try first saving the result of the query in a temporal table, and then do a SELECT from the temporal table:

首先尝试将查询结果保存在时态表中，然后从时态表中进行选择:

SELECT @Sql = "select into #temp * from cds.ppl"

EXECUTE(@Sql)

SELECT * FROM #temp

#4

-1

After some research, I have edited my answer.

经过一番研究，我已经编辑了我的答案。

Regarding the EXECUTE ( string-expression ) statement, yes you have to use single quotes instead of double quotes for the string expression. This page mentions:

对于EXECUTE (string-expression)语句，您必须为字符串表达式使用单引号而不是双引号。这一页提到:

It lets you execute dynamically prepared statements, such as statements that are constructed using the parameters passed in to a procedure. Literal strings in the statement must be enclosed in single quotes, and the statement must be on a single line.

它允许您执行动态准备好的语句，例如使用传递给过程的参数构造的语句。语句中的文字字符串必须用单引号括起来，语句必须在一行上。

Which will eliminate the column not found error but the procedure will return this other error:

这将消除未发现的列错误，但过程将返回另一个错误:

Result set not permitted in '<batch statement>'

Same error returned when trying to execute this statement alone:

当试图单独执行此语句时返回的错误如下:

execute ('select * from sysusers')

With probable cause:

可能的原因:

You attempted to execute a SELECT statement in a context where a result set is not permitted.

您试图在不允许结果集的上下文中执行SELECT语句。

See my most recent answer for the solution.

请参阅我最近的解决方案。

And regarding schemas, here's how to refer to objects:

关于图式，以下是如何指代对象:

It is always good practice to refer to database objects by a schema name and the object name, separated by a period (.). For a complete example, to SELECT records from the Employee table in the HumanResources schema of the current database would look like:

使用模式名和对象名(由句点(.)分隔)引用数据库对象总是很好的做法。对于一个完整的示例，在当前数据库的HumanResources模式中从Employee表中选择记录如下:

SELECT * FROM HumanResources.Employee

To reference an object located in a remote database, the fully qualified object name includes the server name and the database name. For example, to SELECT records from the Employee table in the HumanResources schema in the AdventureWorks database on MyServer would look like:

要引用位于远程数据库中的对象，完全限定的对象名包括服务器名和数据库名。例如，要在MyServer上的AdventureWorks数据库的HumanResources模式中从Employee表中选择记录，如下所示:

SELECT * FROM MyServer.AdventureWorks.HumanResources.Employee

I tested that in SQL Anywhere 12 and it works the same. And even though I was not familiar with schemas, what I'm suggesting you below is actually using schemas, dbowner would be the schema name:

我在SQL Anywhere 12中测试了它，它的工作原理是一样的。虽然我不熟悉模式，但我建议你下面使用模式，dbowner应该是模式名:

1) select * from dbname.dbowner.tablename

1)从dbname.dbowner.tablename中选择*

2) select * from dbowner.tablename

2)从dbowner.tablename中选择*

3) select * from dbname..tablename (assumes table exists in the dbo schema)

3)从dbname中选择*。tablename(假设dbo模式中存在表)

Bottom line.... In your select statement cds.ppl has to be a table named ppl created in the cds schema.

底线....在select语句cd中。ppl必须是在cds模式中创建的名为ppl的表。

Or if cds is your database name and ppl your table name created in the dbo schema, you are missing a dot:

或者，如果cd是您的数据库名，而ppl是您在dbo模式中创建的表名，那么您就缺少了一个点:

select * from cds..ppl

#1