Invalid Column Name using Dynamic SQL

Question

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

which version are u using ? it works perfectly fine at my end. — HEEN
– HEEN, Commented Apr 20, 2016 at 9:33
Just remember to add that in - I am running SQL Server 2005 unfortunately — user3482471
– user3482471, Commented Apr 20, 2016 at 9:34
I have updated the question with the version sorry. Or do you mean something else. — user3482471
– user3482471, Commented Apr 20, 2016 at 9:36
i m telling about in question where did u updated ur question where the error is coming,because ur query is working fine at my end in sql-server-2005 — HEEN
– HEEN, Commented Apr 20, 2016 at 9:37

George Dando · Accepted Answer · 2016-04-20 09:59:58Z

7

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

edited Apr 20, 2016 at 9:59

answered Apr 20, 2016 at 9:44

George Dando

4442 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3482471 Over a year ago

Hi George, Thank you for the comment i think we may be a step closer. However using this i am now getting this message. Msg 1038, Level 15, State 5, Line 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

George Dando Over a year ago

Have amended code snippet above to provide an alias for that column.

user3482471 Over a year ago

Hi George, When i use that i think it has too many ' in. As its put the rest of my script inside it (ie in red)

George Dando Over a year ago

Oops, escaping the quotes can be a real head-case. Removed the one after [ACTIVE_NAMES]

Collectives™ on Stack Overflow

Invalid Column Name using Dynamic SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related