CODEWITHSUNDEEP
sqlsql-server
Hitokiri
Hitokiri

Reputation: 13

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

Upvotes: 0

Views: 1550

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You cannot dynamically include identifiers in SQL, but you can include constant values. To do so, use sp_executesql like this:

BEGIN
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = '
    INSERT INTO EDOFA (DTCODE, DTAMPO, DTNDOS, DTTFAC, DTCDEB, DTNDEB, DTDAPP)
    SELECT DTCODE, DTAMPO, DTNDOS, DTTFAC, DTCDEB, DTNDEB, DTDAPP
    FROM [table]
    WHERE DTCODE = @DTCODE OR
          DTAMPO = @DTAMPO OR
          DTNDOS = @DTNDOS OR
          DTTFAC = @DTTFAC OR
          DTCDEB = @DTCDEB OR
          DTNDEB = @DTNDEB OR
          DTDAPP = @DTDAPP';

    -- Replace the table name because, alas, we cannot parameterize that
    SET @SQL = REPLACE(@SQL,
                      '[table]',
                       (SELECT max(name) FROM sys.tables WHERE name like 'EDOFA%')
                      );

    -- Pass everything else as parameters
    EXEC sp_executesql @SQL,
                       N'
@DTCODE nvarchar(max),
@DTAMPO nvarchar(max),
@DTNDOS float,
@DTTFAC nvarchar(max),
@DTCDEB nvarchar(max),
@DTNDEB float,
@DTDAPP float',
                      @DTCODE=@DTCODE, @DTAMPO=@DTAMPO, @DTNDOS=@DTNDOS, @DTTFAC=@DTTFAC,
                      @DTCDEB=@DTCDEB, @DTNDEB=@DTNDEB, @DTDAPP=@DTDAPP;
END;

The code may be a bit more cumbersome to write, but using parameters is definitely a best practice when passing values into SQL queries.

Upvotes: 0

Thom A
Thom A

Reputation: 95561

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.

Upvotes: 2

user1443098
user1443098

Reputation: 7645

Your from clause returns a scalar, and doesn't name it. Also the subquery in the from clause needs an alias. That's causing the error you get.

Once you add an alias, you'll get other errors. e.g. DTAMPO is not a column returned by the FROM clause

Upvotes: 0

Related Questions