Error In Dynamic Sql Statement

[MarvinJean]

I am trying to create and run a dynamic Sql statement in a stored procedure.

The part of the proc that creates the @Sql is
"SET @SQL =
'SELECT IDENTITY(int, 1, 1) AS Testid, rtblCertRollTest.SapLotNumber,
rtblCertRollTest.SapRoll, rtblCertRollTest.Ply, '
+ @tstTable + '.SapLotNumber AS tstSapLotNumber, '
+ @tstTable + '.SapRoll AS tstSapRoll, '
+ @tstTable + '.SapPly AS tstSapPly INTO rtblTest '
+ ' FROM rtblCertRollTest LEFT JOIN ' + @tstTable + ' ON
(rtblCertRollTest.Ply = ' + @tstTable + '.SapPly)
AND (rtblCertRollTest.SapRoll = ' + @tstTable + '.SapRoll)
AND (rtblCertRollTest.SapLotNumber = ' + @tstTable + '.SapLotNumber)
Order By rtblCertRollTest.SapLotNumber, rtblCertRollTest.SapRoll,
rtblCertRollTest.Ply'
PRINT @SQL
Exec @r_Code = @SQL"


The stored proc gives an error Server: "Msg 203, Level 16, State 2, Procedure procTestedRolls, Line 43" - followed by the text of the statement eg
The name 'SELECT IDENTITY(int, 1, 1) AS Testid, rtblCertRollTest.SapLotNumber,
rtblCertRollTest.SapRoll, rtblCertRollTest.Ply, tblSapPinhole.SapLotNumber AS tstSapLotNumber, tblSapPinhole.SapRoll AS tstSapRoll, tblSapPinhole.SapPly AS tstSapPly INTO rtblTest FROM rtblCertRollTest LEFT JOIN tblSapPinhole ON
(rtblCertRollTest.Ply = tblSapPinhole.SapPly)
AND (rtblCertRollTest.SapRoll = tblS... (it cuts off after about 560 characters but the contents of @Sql is the complete statement. Note that the translation of the table name parameter (tblSapPinhole) is processed properly.

The proc PRINTS the @Sql variable before it tries (and fails) to run statement in @Sql.

I copy the contents of the @Sql variable and paste it into a Query Analyser window, it runs and processes the statement correctly.

I have tried to run this proc in the DEBUG window and get the same result.

Any suggestions where to look.

Thanks,

Marvin Jean

 

[SQLBill]

Try changing this:

Exec @r_Code = @SQL"

to:

Exec(@SQL)"

-SQLBill