在SQL中使用存储过程的TableName。

I have some problems to passing the @TableName inside a Nearby procedure to use in one StoreLocator. I need to get in 3 tables. I have tested using QUOTENAME but the problem is always here. Can someone help me to fix this problem. Thanks

我有一些问题要在一个StoreLocator中使用的邻近过程中传递@TableName。我需要三张桌子。我用QUOTENAME测试过，但问题总是在这里。有人能帮我解决这个问题吗?谢谢

ALTER PROCEDURE [dbo].[GetNearbyTable]  
    @Table sysname, 
    @CenterLatitude FLOAT, 
    @CenterLongitude FLOAT, 
    @SearchDistance FLOAT, 
    @EarthRadius FLOAT
AS 

DECLARE @CntXAxis FLOAT
DECLARE @CntYAxis FLOAT
DECLARE @CntZAxis FLOAT 

SET @Table = RTRIM(@Table)   
SET @CntXAxis = COS(RADIANS(@CenterLatitude)) * COS(RADIANS(@CenterLongitude)) 
SET @CntYAxis = COS(RADIANS(@CenterLatitude)) * SIN(RADIANS(@CenterLongitude)) 
SET @CntZAxis = SIN(RADIANS(@CenterLatitude)) 

SELECT TOP 100 *,  
       ProxDistance = @EarthRadius * ACOS( dbo.XAxis(glat, glon)*@CntXAxis + dbo.YAxis(glat, glon)*@CntYAxis + dbo.ZAxis(glat)*@CntZAxis)     
FROM  @Table  
WHERE @EarthRadius * ACOS( dbo.XAxis(glat, glon)*@CntXAxis + dbo.YAxis(glat, glon)*@CntYAxis + dbo.ZAxis(glat)*@CntZAxis) <= @SearchDistance

@Table or QUOTENAME(@Table) are not accepted. I have tested @Table as varchar(50) and similar. I'm not a SQLexpert.

不接受@Table或QUOTENAME(@Table)。我已经将@Table测试为varchar(50)和类似的。我不是一个SQLexpert。

3 个解决方案

#1

You need EXEC() to execute dynamic SQL. This should be the query you expect:

您需要EXEC()来执行动态SQL。这应该是您期望的查询:

EXEC('
SELECT TOP 100 *,  
       ProxDistance = ' + @EarthRadius  + ' * ACOS( dbo.XAxis(glat, glon)*'
       + @CntXAxis + ' + dbo.YAxis(glat, glon)*'
       + @CntYAxis + ' + dbo.ZAxis(glat)*'
       + @CntZAxis + ')     
FROM  ' + QUOTENAME(@Table) + '
WHERE ' + @EarthRadius + ' * ACOS( dbo.XAxis(glat, glon)*'
       + @CntXAxis + ' + dbo.YAxis(glat, glon)*'
       + @CntYAxis + ' + dbo.ZAxis(glat)*'
       + @CntZAxis + ') <= ' + @SearchDistance)

BTW, when generating dynamic SQL like this, watch out for SQL injection possibilities (see http://msdn.microsoft.com/en-us/library/ms161953.aspx). The statement as I wrote it is free from injection risk because it quotes the only string that it includes.

在生成这样的动态SQL时，请注意SQL注入的可能性(参见http://msdn.microsoft.com/en-us/library/ms161953.aspx)。我写的声明没有注入风险，因为它引用了它包含的唯一字符串。

#2

SQL Server doesn't allow you to do select from a dynamic table name. You'll need to build an nvarchar(max) string and either use exec() or sp_executesql. If you can, eliminate the need to pass a table name in dynamically for maintainability and performance reasons...

SQL Server不允许您从动态表名中选择。您需要构建一个nvarchar(max)字符串，或者使用exec()或sp_executesql。如果可以，为了可维护性和性能原因，消除动态传递表名的需要……

#3

try

试一试

exec sp_executesql N'SELECT TOP 100 *, ProxDistance = @EarthRadius * ACOS( dbo.XAxis(glat, glon)*@CntXAxis + dbo.YAxis(glat, glon)*@CntYAxis + dbo.ZAxis(glat)*@CntZAxis)
FROM  @Table'