sql-server - 语法不正确，需要 EXTERNAL

Question

我创建了一个存储过程。首先我检查一个表，如果该表在我的数据库中，然后我删除它并创建新表，其次我在 SP 中创建一个存储过程，我在表中插入值。我的问题是，当我在存储过程中添加存储过程部分时，我收到错误 in Correct Syntax near begin waiting EXTERNAL 。以下是我的商店程序，您可以帮我解决我做错的地方吗？

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

谢谢。

Answer 1

关键字“AS”在由“GO”或“;”标记的批处理结束之前出现两次。

将 SP 的创建封装在字符串(动态 SQL)中，如果要使用另一个 SP 创建 SP，则使用 E​​XEC(@SQL) 调用该字符串的执行。