SQL does not recognize tablename from select statement

If I write the tablename after ‘FROM’ the query works. If I use the result of a select statement the query does not resolve. Is there a workaround?

I have tried resolving the tablename as an output from a stored procedure but I cannot seem to use exec in a stored procedure or function without receiving an error

ALTER PROCEDURE [dbo].[EDOFA_TABLE]
(
@DTCODE nvarchar(max) = null
,@DTAMPO nvarchar(max) = null
,@DTNDOS float = null
,@DTTFAC nvarchar(max) = null
,@DTCDEB nvarchar(max) = null
,@DTNDEB float = null
,@DTDAPP float = null
)
AS

BEGIN

INSERT INTO EDOFA (DTCODE
      ,DTAMPO
      ,DTNDOS
      ,DTTFAC
      ,DTCDEB
      ,DTNDEB
      ,DTDAPP)
SELECT DTCODE
      ,DTAMPO
      ,DTNDOS
      ,DTTFAC
      ,DTCDEB
      ,DTNDEB
      ,DTDAPP
FROM (SELECT max(name) FROM sys.tables WHERE name like 'EDOFA%')
WHERE DTCODE = @DTCODE
      OR DTAMPO = @DTAMPO
      OR DTNDOS = @DTNDOS
      OR DTTFAC = @DTTFAC
      OR DTCDEB = @DTCDEB
      OR DTNDEB = @DTNDEB
      OR DTDAPP = @DTDAPP
END

Incorrect syntax near keyword where

Answer

As I stated in my comment “That isn’t how SQL works. You can’t replace an object with an expression, variable name, etc. It has to be a ltieral; you need to use parametrised dynamic SQL.”. I believe this does what you’re after.

ALTER PROCEDURE [dbo].[EDOFA_TABLE] (@DTCODE nvarchar(MAX) = NULL,
                                     @DTAMPO nvarchar(MAX) = NULL,
                                     @DTNDOS float = NULL,
                                     @DTTFAC nvarchar(MAX) = NULL,
                                     @DTCDEB nvarchar(MAX) = NULL,
                                     @DTNDEB float = NULL,
                                     @DTDAPP float = NULL)
AS
BEGIN

    DECLARE @SQL nvarchar(MAX);

    SET @SQL = N'INSERT INTO dbo.EDOFA (DTCODE,' + NCHAR(13) + NCHAR(10) +
               N'                       DTAMPO,' + NCHAR(13) + NCHAR(10) +
               N'                       DTNDOS,' + NCHAR(13) + NCHAR(10) +
               N'                       DTTFAC,' + NCHAR(13) + NCHAR(10) +
               N'                       DTCDEB,' + NCHAR(13) + NCHAR(10) +
               N'                       DTNDEB,' + NCHAR(13) + NCHAR(10) +
               N'                       DTDAPP)' + NCHAR(13) + NCHAR(10) +
               N'SELECT DTCODE,' + NCHAR(13) + NCHAR(10) +
               N'       DTAMPO,' + NCHAR(13) + NCHAR(10) +
               N'       DTNDOS,' + NCHAR(13) + NCHAR(10) +
               N'       DTTFAC,' + NCHAR(13) + NCHAR(10) +
               N'       DTCDEB,' + NCHAR(13) + NCHAR(10) +
               N'       DTNDEB,' + NCHAR(13) + NCHAR(10) +
               N'       DTDAPP' + NCHAR(13) + NCHAR(10) +
               N'FROM dbo.' + QUOTENAME((SELECT MAX([name]) FROM sys.tables WHERE name LIKE 'EDOFA%')) + NCHAR(13) + NCHAR(10) +
               N'WHERE DTCODE = @DTCODE' + NCHAR(13) + NCHAR(10) +
               N'   OR DTAMPO = @DTAMPO' + NCHAR(13) + NCHAR(10) +
               N'   OR DTNDOS = @DTNDOS' + NCHAR(13) + NCHAR(10) +
               N'   OR DTTFAC = @DTTFAC' + NCHAR(13) + NCHAR(10) +
               N'   OR DTCDEB = @DTCDEB' + NCHAR(13) + NCHAR(10) +
               N'   OR DTNDEB = @DTNDEB' + NCHAR(13) + NCHAR(10) +
               N'   OR DTDAPP = @DTDAPP;';

    DECLARE @Params nvarchar(MAX) = N'@DTCODE nvarchar(MAX),' +
                                    N'@DTAMPO nvarchar(MAX),' +
                                    N'@DTNDOS float,' +
                                    N'@DTTFAC nvarchar(MAX),' +
                                    N'@DTCDEB nvarchar(MAX),' +
                                    N'@DTNDEB float,' +
                                    N'@DTDAPP float';

    --PRINT @SQL; --Your best friend

    EXEC sp_executesql @SQL, @Params, @DTCODE, @DTAMPO, @DTNDOS, @DTTFAC, @DTCDEB, @DTNDEB, @DTDAPP;

END;

I build a dynamic statement, and put that into @SQL; along with ensuring I properly quote the dynamic object name, using QUOTENAME. Then I build the parameters and pass them all to the dynamic statement us sp_executesql.

If you get stuck, uncomment your best friend (and comment out the EXEC) and debug the printed SQL.