incorrect syntax near begin expecting EXTERNAL

Question

I create a store procedure . In which first i check a table if the table is in my DB then i drop it and create new table, Second i create a store procedure there within the SP where i am inserting value in the table . My problem is that when i add store procedure part in the store procedure i am getting error incorrect syntax near begin expecting EXTERNAL . Below is my store procedure can you please help me where i am doing wrong .

ALTER PROCEDURE myProcedure
AS 
BEGIN

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ATSROUTES]') AND type in (N'U'))
DROP TABLE [dbo].[ATSROUTES]


    CREATE TABLE [ATSROUTES](
    [zip] [varchar](255) NULL,
    [route] [varchar](255) NULL,
    [drivernum] [varchar](255) NULL,
    [altserviceid] [varchar](255) NULL,
    [localorldrvnum] [varchar](255) NULL,
    [pickupzone] [varchar](255) NULL,
    [distcenter] [varchar](255) NULL,
    [altdispid] [varchar](255) NULL,
    [id] [int] NULL);

    BULK INSERT ATSROUTES FROM  'C:\Routes\my1.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[updateroute_sp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].updateroute_sp

CREATE procedure updateroute_sp 
AS
BEGIN

    DECLARE @route varchar(255)
    DECLARE @zip varchar(255)
    DECLARE @routeid varchar(255)
    DECLARE @distcenter varchar(255)
    DECLARE @altdispid varchar(255)



    DECLARE @ERR_NOTFOUND varchar(2000)

    DECLARE db_Cursor CURSOR FOR SELECT zip, [route] from ATSROUTES 

OPEN db_Cursor 

    FETCH NEXT FROM db_Cursor INTO @zip,@route
    WHILE @@FETCH_STATUS = 0

     BEGIN
        IF (@route is not null and @route <> '') 
        BEGIN
        IF NOT EXISTS (SELECT * FROM routenames WHERE routename = @ROUTE)
         BEGIN
            EXEC GETNEWRECID2 'ROUTENAMES','ROUTEID','ROUTENAMES',@ROUTEID
            insert into routenames (routeid,routename) values (@routeid,@ROUTE);
         END

    END

 FETCH NEXT FROM db_Cursor INTO @zip,@route

   END
        CLOSE db_Cursor
        DEALLOCATE db_Cursor
   END


   EXEC updateroute_sp 

   DROP PROCEDURE updateroute_sp

   UPDATE ATSROUTES set id = (select routeid from routenames where routename = [route]);
   UPDATE ATSROUTES set drivernum =LTRIM(RTRIM(drivernum));

   END

Thanks .

Answer 1

The keyword 'AS' is present two times before the end of the batch marked by 'GO' or ';'.

Encapsulate the creation of the SP in a string (dynamic SQL), then call the execution of that string with EXEC(@SQL) if you want to create an SP withing another SP.