Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamic SQL Giving 'Name not a valid identifier error' Expand / Collapse
Author
Message
Posted Wednesday, July 22, 2009 8:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 26, 2010 1:11 PM
Points: 32, Visits: 128
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.
Post #757493
Posted Wednesday, July 22, 2009 9:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
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



Ken Simmons
http://twitter.com/KenSimmons
Post #757517
Posted Wednesday, July 22, 2009 9:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #757549
Posted Wednesday, July 22, 2009 10:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
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



Ken Simmons
http://twitter.com/KenSimmons
Post #757612
Posted Wednesday, July 22, 2009 10:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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 2008, MVP
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

Post #757629
Posted Thursday, July 23, 2009 7:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 26, 2010 1:11 PM
Points: 32, Visits: 128
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.
Post #758220
Posted Thursday, October 4, 2012 12:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:19 AM
Points: 19, Visits: 334
BRACKETS!

Thank you!
Post #1368181
Posted Wednesday, June 5, 2013 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 11:15 PM
Points: 1, Visits: 24
Invalid: exec @sqlQuery

Valid: exec (@sqlQuery)
Post #1460171
Posted Wednesday, June 5, 2013 5:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,234, Visits: 13,719
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1460178
Posted Wednesday, June 5, 2013 5:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Please note: 4 year old answered thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1460184
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse