首页 文章

在sql server中创建存储过程时出错

提问于
浏览 次
0

当我尝试在SQL Server中创建简单的存储过程时，会出现以下错误

消息102，级别15，状态1，过程测试，第2行'varchar'附近的语法不正确 . 消息137，级别15，状态1，过程测试，第27行必须声明标量变量“@” .

编写存储过程非常新 . 请指导

USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TEST]
    @[CUSTNAME] VARCHAR(100),                   
    @[CUSTOMER_SHORT_NAME] VARCHAR(25),
    @[COUNTRY] VARCHAR(50),
    @[region] VARCHAR(25),
    @[Area] VARCHAR(25),
AS BEGIN

    SET NOCOUNT ON;

    UPDATE [dbo].[TEST]
    SET
          @[CUSTNAME]=[CUSTNAME]
        , @[CUSTOMER_SHORT_NAME]=[CUSTOMER_SHORT_NAME]
        , @[COUNTRY]=[COUNTRY]
        , @[region]=[region]
        , @[Area]=[Area]
    WHERE period=@period
        AND customer=@customer

END
sql-server stored-procedures

3 回答

  • 1

    您以相反的方式分配值 .

    Error lies here

    更新[dbo] . [TEST]设置@ [CUSTNAME] = [CUSTNAME]>，@ [CUSTOMER_SHORT_NAME] = [CUSTOMER_SHORT_NAME]，@ [COUNTRY] = [COUNTRY]>，@ [region] = [region]，@ [区域] = [区域]其中period = @ period和> customer = @ customer END

    Try this instead of above code

    update [dbo].[TEST]
   set [CUSTNAME]=@CUSTNAME
  ,[CUSTOMER_SHORT_NAME]=@CUSTOMER_SHORT_NAME
  ,[COUNTRY]=@COUNTRY
  ,[region]=@region
  ,[Area]=@Area
  where period=@period
  and customer=@customer
  END
    回复于
  • 3

    我想你需要摆脱变量中的 [] . 而且您的更新查询也是错误的：

    USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TEST]

   @CUSTNAME VARCHAR(100),                   
   @CUSTOMER_SHORT_NAME VARCHAR(25),
   @COUNTRY VARCHAR(50),
   @region VARCHAR(25),
   @Area VARCHAR(25),

AS BEGIN

   SET NOCOUNT ON;

   UPDATE [dbo].[TEST]
   SET
          [CUSTNAME] = @CUSTNAME
        , [CUSTOMER_SHORT_NAME] = @CUSTOMER_SHORT_NAME
        , [COUNTRY] = @COUNTRY
        , [region] = @region
        , [Area] = @Area
  WHERE period = @period
    AND customer = @customer

END

    您还需要为 @period 和 @customer 添加参数 .

    回复于
  • 0
    USE [my_db]

    GO
    SET ANSI_NULLS ON
    GO
   SET QUOTED_IDENTIFIER ON
   GO

   create PROCEDURE [dbo].[TEST]

   @CUSTNAME varchar(100),                   
   @CUSTOMER_SHORT_NAME varchar(25),
   @COUNTRY varchar(50),
   @region varchar(25),
   @Area varchar(25),
   @period varchar(50),
   @customer varchar(50)
   AS
   BEGIN
   SET NOCOUNT ON;

   update [dbo].[TEST]
   set @CUSTNAME=[CUSTNAME]
  ,@CUSTOMER_SHORT_NAME=[CUSTOMER_SHORT_NAME]
  ,@COUNTRY=[COUNTRY]
  ,@region=[region]
  ,@Area=[Area]
  where period=@period
  and customer=@customer
  END
GO

    删除[]对于第一个错误，添加2参数@Period和@customer

    回复于

相关问题