user3482471 user3482471 - 7 months ago 17
SQL Question

Invalid Column Name using Dynamic SQL

I am attempting to use Dynamic SQL for the 1st time - Now i understand the gist of it to an extent however attempting to enter a parameter into a temp table using Dynamic SQL i am getting the "Invalid Column name" when i am simply try to enter this as text not intending to be a column.

On a side note its worth me mentioning i am running this on SQL Server 2005.

This is my executing query

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


Many thanks 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

If you want the dynamic SQL to treat your variable as a literal string and not a column name you'll 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