Incorrect syntax near the keyword...T_SQL error when incorporated to stored procedure.

Hello Experts,
Been banging around to figure out what's causing the syntax error ...i have the a working Insert statement (See attached file)  but once incorporated to stored procedure it is somehow causing syntax error ...Is my Insert statement (Multiple sub-query; Joins) going to work in a variable SQL (@SQL) inside a SP below...
CREATE PROCEDURE [dbo].[0000_Proc]
AS
DECLARE @tab table (i int identity, CN_Schema VARCHAR(10))
DECLARE @rows INT
DECLARE @i INT
DECLARE @counter INT
DECLARE @CN_Schema VARCHAR(10)

TRUNCATE TABLE Mon_DB.dbo.AUD_MON

INSERT INTO @tab
SELECT name as CN_Schema FROM Bus_DB.sys.schemas
WHERE SCHEMA_ID BETWEEN 14 AND 200 AND name NOT IN ('TestBus','DevBus')

SET @rows = @@Rowcount

SELECT @i = 0, @counter=0
WHILE (@counter < @rows)
-- of just WHILE (1=1)
BEGIN
      SELECT TOP 1 @CN_Schema = CN_Schema, 
                   @i=i
      FROM @tab WHERE i > @i
      ORDER BY i
      IF @@Rowcount =  0    BREAK


DECLARE @SQL VARCHAR(5000)
SET @SQL = '
INSERT INTO Mon_DB.dbo.AUD_MON (Col_1,Col_2,Col_3,Col_4,Col_5,Col_6,Col_7,Col_8,Col_9,Col_10,Col_11,Col_12,Col_13,Col_14,Col_15,Col_16,Col_17,Col_18,Col_19,Col_20)
(
SELECT UPPER(c.IT_School_CODE) as Sch_Schema, a.* 
FROM 
(
SELECT a.Col_1
      ,a.Col_2
      ,a.Col_3
      ,CAST(a.Col_4 AS INT) AS Col_4
      ,a.UPN AS STUDENT_UPN
      ,CASE WHEN a.Col_5 IS NULL THEN '''' 
            WHEN a.Col_5 = '''' THEN '''' ELSE LEFT(a.Col_5,LEN(a.Col_5)-14)END  AS Col_5
      ,NULL AS Col_A
      ,a.Col_6
      ,a.Col_6+'' ''+ a.Col_7 AS Col_7
      ,CASE WHEN a.Col_8 = ''2'' THEN ''M'' 
            WHEN a.Col_8 = ''3'' THEN ''F'' ELSE '''' END AS Col_8
      ,CASE WHEN b.Col_2 = ''Not Specified'' THEN ''n/s''
            WHEN b.Col_2 = ''Not Stated''  THEN ''ns''
            WHEN b.Col_2 = ''Missing''  THEN ''M''
            WHEN b.Col_2 = ''Existing''  THEN ''E'' END AS Col_9
      ,CASE WHEN c.Col_2 = ''True'' THEN 1 ELSE 0 END AS Col_10
      ,NULL AS Col_11
      ,''My Note'' AS Col_12
      ,e.Col52 AS Col_13
      ,f.Col62 AS Col_14
      ,f1.Col62 AS Col_15
      ,g.Col72 AS Col_16
      ,h.Col82 AS Col_17
      ,l.Col102 AS Col_18
      ,h.Col112 AS Col_19
      ,a.DATE_MODIFIED AS Col_20
  FROM (SELECT *
			  ,CASE WHEN Col_X IS NULL THEN 1
					WHEN Col_X = '''' THEN 1 ELSE Col_X END AS Col_X_ID
			  ,CASE WHEN Col_Y IS NULL THEN 1
					WHEN Col_Y = '''' THEN 1 ELSE Col_Y END AS Col_Y_ID
			  ,CASE WHEN Col_Z IS NULL THEN 1
					WHEN Col_Z = '''' THEN 1 ELSE Col_Z END AS Col_Z_ID
          FROM Bus_DB.'+@CN_Schema+'.Table1) a INNER JOIN (SELECT *
																  ,CASE WHEN Col_XX IS NULL THEN 174 
																		WHEN Col_XX = '''' THEN 174 ELSE Col_XX END AS Col_XX_ID
																  ,CASE WHEN Col_YX IS NULL THEN 469 
																		WHEN Col_YX = '''' THEN 469 ELSE Col_YX END AS Col_YX_ID
															FROM Bus_DB.'+@CN_Schema+'.Table2) b ON a.Col11 = b.Col21 
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table3 c  ON a.Col11 = c.Col31  AND c.ID_TYPE = 1
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table4 d  ON c.Col19 = d.Col49 
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table5 e ON a.Col_X_ID = e.Col51 
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table6 f ON a.Col_Y_ID = f.Col61 
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table6 f1 ON a.Col_Z_ID = f1.Col61 
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table7 g ON b.Col_XX_ID = g.Col71 
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table8 h ON b.Col_YX_ID = h.Col81  
											   INNER JOIN (SELECT *
																  ,CASE WHEN Col_AA IS NULL THEN 1 WHEN Col_AA = '''' THEN 1 ELSE Col_AA END AS Col_AA_ID
																  ,CASE WHEN Col_AB IS NULL THEN 1 WHEN Col_AB = '''' THEN 1 ELSE Col_AB END AS Col_AB_ID
															FROM Bus_DB.'+@CN_Schema+'.Table9) i ON a.Col11 = i.Col91 
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table10 l ON i.Col_AA_ID = l.Col101
											   INNER JOIN Bus_DB.'+@CN_Schema+'.Table11 m ON i.Col_AB_ID = m.Col111 
  WHERE a.Bus_Role <> ''A''
 ) a,
 Bus_DB.'+@CN_Schema+'.SCHOOL b, Mon_DB.dbo.AUD_BUS c 
 WHERE (b.District= c.District AND b.Period = c.Period)
 )
'
EXECUTE(@SQL)

SET @counter = @counter + 1

END

GO

Open in new window

ExpExc-WorkingInsertT-SQL.sql
ExpExc-SyntaxErrorSP-SQL.sql
jsuanqueAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
at this point all I can suggest is you need to capture the generated string then examine it for syntax error(s)

so instead of

EXECUTE(@SQL)

you need "select @SQL"

then try parsing that as a query
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Few considerations:

1. You are selecting 21 columns to insert in 20 columns

2. You are using VARCHAR(10) for schema names. You're sure that is enough length?

3. By the error you my facing an empty @CN_Schema

0
jsuanqueAuthor Commented:
Thank you and appreciate the input guys.

Vitor -  yup you're right the mock-up query i provided missed one column. while for schema name (datatype length) defined between the schema id's is sufficient as those represents abbreviated business geography


...but found the problem...and it was due to ...

DECLARE @SQL VARCHAR(5000)

...changing it to ...

DECLARE @SQL VARCHAR(MAX) solves my issue.
0
jsuanqueAuthor Commented:
As mentioned original query changes was the one stated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.