incorrect syntax near begin expecting EXTERNAL
sql-server, sql-server-2008-r2, stored-procedures
Solution
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.
Problem
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 .