TableName using stored procedure in SQL

Question

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

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.

Answer 1

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...

Answer 2

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'  

Answer 3

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

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.