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