Dynamic SQL Giving 'Name not a valid identifier error'

Question

Post reply

Dynamic SQL Giving 'Name not a valid identifier error'

dmounday

SSC Eights!

Points: 920
More actions
July 22, 2009 at 8:44 am

#136720

I have some SQL I'm going to put in a stored procedure which will insert records into a temp table. However, when I run the following, I recieve a message that the Select statement is not a valid identifier. It looks like it is truncating the statement. But, with the PRINT statement, I know that it is populating the variable just fine. Any ideas as to what I need to do? I've copied the SQL and the output I recieve below:
SQL
DECLARE @TreatyNumber As Varchar(20)
SET @TreatyNumber = '200704.2.6'
DECLARE @SQL As Varchar(max)
SET @SQL = 'SELECT vwT.TreatyNumber, topics.PieceTitle AS topic, subsection.PieceTitle AS Subtopic, question.PieceTitle AS SurveyQuestion, (CONVERT(Varchar, topics.PieceNumber) + CONVERT(Varchar, subsection.PieceNumber)) AS PartID, SAP.ColumnName
FROM Treaty.vwTreaty AS vwT INNER JOIN
Survey.vwSurveyInstance AS vwSI ON vwT.TreatySurveyInstanceId = vwSI.SurveyInstanceId INNER JOIN
Survey.vwSurveyTemplate AS vwST ON vwSI.SurveyInstanceSurveyTemplateId = vwST.SurveyTemplateId INNER JOIN
Survey.vwSurveyPiece topics ON vwST.SurveyTemplateId = topics.SurveyPieceSurveyTemplateId AND topics.SurveyPieceTypeIdCodeName = ' + '''' + 'Topic' + '''' + ' INNER JOIN
Survey.vwSurveyPiece AS subsection ON subsection.ParentSurveyPieceId = topics.SurveyPieceId AND subsection.SurveyPieceTypeIdCodeName = ' + '''' + 'Topic Subsection' + '''' + ' INNER JOIN
Survey.vwSurveyPiece AS question ON question.ParentSurveyPieceId = subsection.SurveyPieceId AND question.SurveyPieceTypeIdCodeName = ' + '''' + 'Question' + '''' + ' INNER JOIN
Survey.SurveyAnswerPart SAP ON question.SurveyPieceId = SAP.SurveyPieceId
WHERE vwT.TreatyNumber = ' + '''' + @TreatyNumber + '''' +
' ORDER BY topics.PieceNumber, subsection.PieceNumber, question.PieceNumber'
PRINT @SQL
DECLARE @temp_provisions As Table (TreatyNum Varchar(20), topic Varchar(100), Subtopic Varchar(100), SurveyQuestion Varchar(100), SurveyPart Varchar(5), ColName Varchar(100))
INSERT INTO @temp_provisions
EXEC @SQL
OUTPUT
SELECT vwT.TreatyNumber, topics.PieceTitle AS topic, subsection.PieceTitle AS Subtopic, question.PieceTitle AS SurveyQuestion, (CONVERT(Varchar, topics.PieceNumber) + CONVERT(Varchar, subsection.PieceNumber)) AS PartID, SAP.ColumnName
FROM Treaty.vwTreaty AS vwT INNER JOIN
Survey.vwSurveyInstance AS vwSI ON vwT.TreatySurveyInstanceId = vwSI.SurveyInstanceId INNER JOIN
Survey.vwSurveyTemplate AS vwST ON vwSI.SurveyInstanceSurveyTemplateId = vwST.SurveyTemplateId INNER JOIN
Survey.vwSurveyPiece topics ON vwST.SurveyTemplateId = topics.SurveyPieceSurveyTemplateId AND topics.SurveyPieceTypeIdCodeName = 'Topic' INNER JOIN
Survey.vwSurveyPiece AS subsection ON subsection.ParentSurveyPieceId = topics.SurveyPieceId AND subsection.SurveyPieceTypeIdCodeName = 'Topic Subsection' INNER JOIN
Survey.vwSurveyPiece AS question ON question.ParentSurveyPieceId = subsection.SurveyPieceId AND question.SurveyPieceTypeIdCodeName = 'Question' INNER JOIN
Survey.SurveyAnswerPart SAP ON question.SurveyPieceId = SAP.SurveyPieceId
WHERE vwT.TreatyNumber = '200704.2.6' ORDER BY topics.PieceNumber, subsection.PieceNumber, question.PieceNumber
Msg 203, Level 16, State 2, Line 21
The name 'SELECT vwT.TreatyNumber, topics.PieceTitle AS topic, subsection.PieceTitle AS Subtopic, question.PieceTitle AS SurveyQuestion, (CONVERT(Varchar, topics.PieceNumber) + CONVERT(Varchar, subsection.PieceNumber)) AS PartID, SAP.ColumnName
FROM Treaty.vwTreaty AS vwT INNER JOIN
Survey.vwSurveyInstance AS vwSI ON vwT.TreatySurveyInstanceId = vwSI.SurveyInstanceId INNER JOIN
Survey.vwSurveyTemplate AS vwST ON vwSI.SurveyInstanceSurveyTemplateId = vwST.SurveyTemplateId INNER JOIN
Survey.vwSurveyPiece topics ON vwST.SurveyTemplateId = topics.SurveyPieceSurveyTemplateId AND topics.SurveyPieceTypeIdCodeName = 'Topic' INNER JOIN
Survey.vw' is not a valid identifier.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Try using sp_executesql and see if that fixes it.

Here is a sample that should work.

DECLARE @SQL NVARCHAR(MAX)

DECLARE @TBL TABLE (i int)

SET @SQL = 'SELECT TOP 10 message_id FROM sys.messages'

INSERT INTO @TBL

EXEC sp_executesql @SQL

SELECT * FROM @TBL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Replace EXEC @SQL with EXEC (@SQL)

Without the brackets, the assumption is that the content of the variable is a stored procedure name

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

The good thing about using sp_executesql is that you can pass the parameter value instead of completely building the string on the fly like this...

DECLARE @SQL NVARCHAR(MAX)

DECLARE @TBL TABLE (i int)

SET @SQL = 'SELECT message_id FROM sys.messages WHERE message_id < @ID'

INSERT INTO @TBL

EXEC sp_executesql @SQL, N'@ID INT' , 1000

SELECT * FROM @TBL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Indeed. However looking I can't see any reason why this is dynamic SQL at all.

This should work just fine

DECLARE @TreatyNumber As Varchar(20)

SET @TreatyNumber = '200704.2.6'

DECLARE @temp_provisions As Table (

TreatyNum Varchar(20),

topic Varchar(100),

Subtopic Varchar(100),

SurveyQuestion Varchar(100),

SurveyPart Varchar(5),

ColName Varchar(100)

)

INSERT INTO @temp_provisions (TreatyNum, topic, Subtopic, SurveyQuestion, SurveyPart, ColName)

SELECT vwT.TreatyNumber, topics.PieceTitle AS topic, subsection.PieceTitle AS Subtopic, question.PieceTitle AS SurveyQuestion,

(CONVERT(Varchar, topics.PieceNumber) + CONVERT(Varchar, subsection.PieceNumber)) AS PartID,

SAP.ColumnName

FROM Treaty.vwTreaty AS vwT

INNER JOIN Survey.vwSurveyInstance AS vwSI ON vwT.TreatySurveyInstanceId = vwSI.SurveyInstanceId

INNER JOIN Survey.vwSurveyTemplate AS vwST ON vwSI.SurveyInstanceSurveyTemplateId = vwST.SurveyTemplateId

INNER JOIN Survey.vwSurveyPiece topics ON vwST.SurveyTemplateId = topics.SurveyPieceSurveyTemplateId

AND topics.SurveyPieceTypeIdCodeName = 'Topic'

INNER JOIN Survey.vwSurveyPiece AS subsection ON subsection.ParentSurveyPieceId = topics.SurveyPieceId

AND subsection.SurveyPieceTypeIdCodeName = 'Topic Subsection'

INNER JOIN Survey.vwSurveyPiece AS question ON question.ParentSurveyPieceId = subsection.SurveyPieceId

AND question.SurveyPieceTypeIdCodeName = 'Question'

INNER JOIN Survey.SurveyAnswerPart SAP ON question.SurveyPieceId = SAP.SurveyPieceId

WHERE vwT.TreatyNumber = @TreatyNumber

I've removed the order by as, unless there's an identity column on the table, the order of rows inserted is meaningless. Order by should be used on the select that reads from the table variable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dmounday SSC Eights! Points: 920 More actions · Answer 5

Actually, I pulled only that query out of my larger script. Other parts are using dynamic SQL. This one isn't. So you're right. But, I finally got it to work. I copied and pasted line-by-line into another query window. I had copied the core sql statement from a dataset I had previously built in SSRS. We think some rogue character might have gotten in there.

But, thanks for the posts. I appreciate seeing and learning from what others share.

Terence Keys Ten Centuries Points: 1140 More actions · Answer 6

Terence Keys

Ten Centuries

Points: 1140

October 4, 2012 at 12:46 am

#1545339

BRACKETS! 🙂

Thank you!

softtesting2012 Valued Member Points: 59 More actions · Answer 7

softtesting2012

Valued Member

Points: 59

June 5, 2013 at 5:21 am

#1621294

Invalid: exec @sqlquery

Valid: exec (@sqlQuery)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

softtesting2012 (6/5/2013)
Invalid: exec @sqlquery
Valid: exec (@sqlQuery)

Not necessarily so. Read Gail's post above.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Please note: 4 year old answered thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

GilaMonster (6/5/2013)
Please note: 4 year old answered thread.

Outstanding work for one so young 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

iaminyourhead SSC Eights! Points: 810 More actions · Answer 11

iaminyourhead

SSC Eights!

Points: 810

September 25, 2013 at 3:23 pm

#1653362

Ken... You are a genius!!! Thank You very much!!!