Invalid column name using dynamic SQL

Question

I am trying to use Dynamic SQL for the first time. Now I understand the essence of this, but I'm trying to enter a parameter into the temp table using Dynamic SQL. I get an "Invalid column name" when I just try to type this as text, not intending to be a column.

In a side note, it's worth mentioning that I'm running this on SQL Server 2005.

This is my executable request.

SELECT ARCHV_FLAG,PATNT_REFNO,ACTIVE_NAMES,AINPT_REFNO INTO #TEMP FROM ACTIVE_NAMES WHERE PATNT_REFNO = 1 AND ARCHV_FLAG = 'C' Fix SET @SQL = 'SELECT ARCHV_FLAG,PATNT_REFNO,''' +@TABLE +''' AS [ACTIVE_NAMES],'+ @PK + ' INTO #TEMP FROM ' + @TABLE + ' WHERE PATNT_REFNO = '+ CAST(@PATNT AS VARCHAR (100))+ ' AND ARCHV_FLAG = ''C''' 

Thank you very much in advance.

 DECLARE @SQL NVARCHAR(4000), @SQL1 NVARCHAR(4000), @TABLE VARCHAR (100), @PK VARCHAR (50), @PATNT INT SET @PATNT = 1 CREATE TABLE #TEMP ( ARCHV_FLAG VARCHAR (1), PATNT_REFNO INT, TABLE_NAME VARCHAR (100), PRIMARY_KEY INT ) CREATE TABLE #TABLE (NAME VARCHAR (100)) INSERT INTO #TABLE (NAME) VALUES ('ACTIVE_NAMES') SELECT * FROM #TABLE DECLARE Cur CURSOR FOR SELECT NAME FROM #TABLE OPEN Cur FETCH NEXT FROM Cur INTO @TABLE WHILE @@FETCH_STATUS = 0 BEGIN SET @PK = (SELECT C.NAME FROM SYS.COLUMNS C JOIN SYS.tables T ON C.object_id = T.object_id WHERE T.name = @TABLE AND C.is_identity = 1) SET @SQL = 'SELECT ARCHV_FLAG,PATNT_REFNO,' +@TABLE +','+ @PK + ' INTO #TEMP FROM ' + @TABLE + ' WHERE PATNT_REFNO = '+ CAST(@PATNT AS VARCHAR (100))+ ' AND ARCHV_FLAG = ''C''' EXEC SP_EXECUTESQL @SQL select @sql FETCH NEXT FROM Cur INTO @TABLE END CLOSE Cur DEALLOCATE Cur SELECT * FROM #TEMP DROP TABLE #TEMP DROP TABLE #TABLE 

Answer 1

If you want dynamic SQL to treat your variable as a literal string, rather than a column name, you will have to wrap it in single quotes:

 SET @SQL = 'SELECT ARCHV_FLAG,PATNT_REFNO,''' +@TABLE +''' AS [ACTIVE_NAMES],'+ @PK + ' INTO #TEMP FROM ' + @TABLE + ' WHERE PATNT_REFNO = '+ CAST(@PATNT AS VARCHAR (100))+ ' AND ARCHV_FLAG = ''C''' EXEC SP_EXECUTESQL @SQL