Errors while creating stored procedure in sql server

Question

Below errors appear when i am trying to create simple stored procedure in SQL Server

Msg 102, Level 15, State 1, Procedure test, Line 2 Incorrect syntax near 'varchar'. Msg 137, Level 15, State 1, Procedure test, Line 27 Must declare the scalar variable "@".

Very much new to writing stored procedures. Kindly guide

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

Peter B · Accepted Answer · 2016-04-07 11:40:01Z

3

I think you need to get rid of [] in your variables. And also your update query is wrong:

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

And you also need to add parameters for @period and @customer.

edited Apr 7, 2016 at 11:40

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

answered Apr 7, 2016 at 11:00

Jeffrey

4122 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

krunal modi · Accepted Answer · 2016-04-07 11:07:21Z

1

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

Remove [] For 1st Error, Add 2 parameter @Period and @customer

answered Apr 7, 2016 at 11:07

krunal modi

13510 bronze badges

Comments

Abdul Hannan Ijaz · Accepted Answer · 2016-04-07 11:08:03Z

0

You are assigning values in opposite way.

Error lies here

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

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

answered Apr 7, 2016 at 11:08

Abdul Hannan Ijaz

8647 silver badges11 bronze badges

Collectives™ on Stack Overflow

Errors while creating stored procedure in sql server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related