SQL Server：在 EXEC 中执行动态/内联 sql 表名称并将几何图形作为几何参数传递

Question

我正在尝试在存储过程中执行内联 SQL 语句。我正在使用 SQL Server 2008。

问题是我无法执行第一个内联语句（带有 WHERE 子句）。它崩溃是因为 EXEC(...) 中的字符串是动态创建的，并且所有连接的变量必须是 varchar 类型。

调用过程时出现错误：

在上下文中指定的非布尔类型表达式，其中 预计情况会接近“订单”。

该过程如下所示：

CREATE PROCEDURE loadMyRows
  @table_name    nvarchar(50),
  @bounding_box  varchar(8000)
AS
BEGIN
  -- *********************************** COMMENT *********************************
  -- ** This two code lines are correct and will return true (1) or false (0),  **
  -- ** but they doesn't work within inline EXEC(...)                           **
  --DECLARE @bb geometry = geometry::STGeomFromText(@bounding_box, 4326);
  --select TOP(5) wkt.STWithin(@bb) AS 'bool'
  -- *********************************** COMMENT *********************************

IF @bounding_box <> ''
BEGIN
    DECLARE @bb geometry = geometry::STGeomFromText(@bounding_box, 4326);

    EXEC(   
        'SELECT TOP (' + @row_limit + ') * ' +
        'FROM ' + @real_table_name + ' ' +
        'WHERE wkt.STWithin('+@bb+') ' + -- <-- doesn't work :-(
      -- 'WHERE wkt.STWithin(geometry::STGeomFromText('''+@bounding_box+''', 4326)) ' +
      -- ^^ doesn't work, too :-(
        'ORDER BY id ASC '
    );
END
ELSE
BEGIN
    EXEC(
        'SELECT TOP (' + @row_limit + ') * ' +
        'FROM ' + @real_table_name + ' ' +
        'ORDER BY id ASC'
    );
END
END

Answer 1

我已经找到了解决这个问题的有效方法。 MSDN 向我展示的方式是 http://msdn.microsoft.com/en- US/library/ms175170.aspx。里面写着：

[...] 字符串作为其独立的批处理执行。

这让我知道，如果我想使用表变量作为字符串执行动态语句，这与我在没有 EXECUTE 命令的情况下执行查询相同，例如：

SELECT TOP(@row_limit) *
FROM @real_table_name
WHERE ...
ORDER BY id ASC;

这可能不适用于表名。

所以，如果我改为：

DECLARE @sql_statement nvarchar(MAX) = 'SELECT TOP(@limit) * 
                                        FROM ' + @real_table_name + ' 
                                        ORDER BY id ASC';

-- declaration of parameters for above sql                              
DECLARE @sql_param_def nvarchar(MAX) = '@limit int';

EXECUTE sp_executesql @sql_statement, @sql_param_def, @limit = @row_limit;

那么，这就会起作用。这是因为我将 @sql_statement 简单地定义为一个串联字符串，它将在运行时将动态表名称解析为具有实际现有表名称的字符串。 @limit 参数未受影响，仍然是一个参数。

如果我们随后执行该批处理，我们只需传递 @limit 参数的值，它就可以工作了！

对于几何参数，它以相同的方式工作：

DECLARE @bb geometry = geometry::STGeomFromText(@bounding_box, 4326);
SET @sql_statement = 'SELECT TOP(@limit) * 
                      FROM ' + @real_table_name + ' 
                      WHERE wkt.STWithin(@geobb) = 1 
                      ORDER BY id ASC';
-- NOTE: This ' = 1' must be set to avoid my above described error (STWithin doesn't return a BOOLEAN!!)

-- declaration of parameters for above sql
SET @sql_param_def = '@limit int, @geobb geometry';

EXECUTE sp_executesql @sql_statement, @sql_param_def, @limit = @row_limit, @geobb = @bb;

希望这很清楚;-)

Answer 2

create proc usp_insert_Proc_Into_temp
@tempTable nvarchar(10) output
as
begin

 set @tempTable = '##temp' 
 declare @query nvarchar(200)
 --Select statement
 set @query = 'select  1 as A,2 as B, 3 as C into'+ ' '+@tempTable+''
 exec(@query)


end

go


declare @tempTable nvarchar(10)
exec usp_insert_Proc_Into_temp @tempTable output
exec('select *  from' + ' '+ @tempTable+'')

exec ('drop table'+ ' '+@tempTable+'')