SQL Server: executing dynamic/inline sql table name within EXEC and passing geometry as geometry parameter

Question

I'm trying to execute an inline SQL statement within a stored procedure. I'm working with SQL Server 2008.

The problem is that I can't execute the first inline statement (with WHERE clause). It crashes because the string within EXEC(...) is dynamically created and all concatenated variables must be of type varchar.

Error that appears when calling procedure:

An expression of non-boolean type specified in a context where a condition is expected, near 'ORDER'.

The procedure looks like:

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

Patrick Hillert · Accepted Answer · 2012-01-24T09:54:38.723

I've found a working solution for this problem. The way the MSDN showed me was http://msdn.microsoft.com/en-US/library/ms175170.aspx. There's written:

[...] the string is executed as its own self-contained batch.

That let me know, if I want to execute a dynamic statement with a table variable as string, it's the same as I would execute the query without the EXECUTE command, like:

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

And this would probably not work for the table name.

So, if I write instead:

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;

Then, this would work. This is because I define the @sql_statement simply as a concatenated string which will just resolve the dynamic table name at runtime to a string with the name of the real existing table. The @limit parameter is untouched and is still a parameter.

If we then execute the batch we only must pass a value for the @limit parameter and it works!

For the geometry parameter it works in the same way:

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;

Hope this was clear ;-)

score 0 · Answer 2 · edited Apr 05 '17 at 06:57

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+'')

SQL Server: executing dynamic/inline sql table name within EXEC and passing geometry as geometry parameter

2 Answers2