0

I have a table called Sales.Customers with the following columns:custid, companyname, contactname, contacttitle, address, city, postalcode, country, phone, fax. I need to create a stored procedure called 'Sales.InsertCustomers' that inserts data into the Customers table. The stored procedure must meet the following requirements:

  • Data changes occur as a single unit of work.
  • Data modifications that are successful are committed and a value of O is returned.
  • Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is returned.
  • The stored procedure uses a built-it scalar function to evaluate the current condition of data modifications.
  • The entire unit of work is terminated and rolled back if a run-time error occurs during execution of the stored procedure.

Here is the code below:

CREATE PROCEDURE Sales.InsertCustomers
   @Companyname nvarchar(100),
   @ContactName nvarchar(100),
   @Contacttitle nvarchar(100),
   @Address nvarchar(100),
   @City nvarchar(80),
   @PostalCode nvarchar(20),
   @country nvarchar(50),
   @phone nvarchar(20),
   @fax nvarchar(20)

AS
BEGIN
  SET NOCOUNT ON 
       SET XACT_ABORT ON 

BEGIN TRY
   BEGIN TRANSACTION 
       INSERT INTO Sales.Customers(companyname, contactname, contacttitle, address, city, postalcode, country, phone, fax)

       VALUES(@Companyname, @ContactName, @Contacttitle, @Address, @City, @PostalCode, @country, @phone, @fax)

      COMMIT TRANSACTION
END TRY
 BEGIN CATCH
      IF XACT_STATE() <> 0 ROLLBACK TRANSACTION
      PRINT 'Unable to create a customer record.'
      THROW
      RETURN -1
 END CATCH
RETURN 0
END;

When I run the code, I get this error:

Msg 102, Level 15, State 1, Procedure InsertCustomers, Line 30 Incorrect syntax near 'THROW'.

How can I fix this?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Lanceleazol
  • 53
  • 1
  • 6
  • Possible duplicate of [Incorrect syntax near 'THROW'](https://stackoverflow.com/questions/35003153/incorrect-syntax-near-throw) – philipxy Aug 14 '18 at 08:44
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. – philipxy Aug 14 '18 at 08:45

1 Answers1

1

See this link.

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

So your PRINT statement should be ending with semicolon.

PRINT 'Unable to create a customer record.';

OR you can do.

;THROW
AB_87
  • 1,126
  • 8
  • 18
  • Oh my, You're such a great helper. Thanks a lot. – Lanceleazol Aug 13 '18 at 07:36
  • No worries. Please upvote/mark as answer if it solved your problem to close the question. – AB_87 Aug 13 '18 at 08:10
  • I marked it. It didn't know that I am supposed to mark it. Now I know that if, the response solves the problem, to close the question you are supposed to upvote/mark. – Lanceleazol Aug 13 '18 at 09:01
  • @resursibanda it's not mandatory but simply helps other people who may find this question with similar issue. – AB_87 Aug 14 '18 at 00:17
  • Ok. I've heard you, but the challenge is I'm still studying and I can't help on stuff that I've not yet covered. I see some of the questions, willing to answer, but because I have shallow knowledge I just leave the question unattended. – Lanceleazol Aug 14 '18 at 06:58
  • Please don't answer duplicates, flag that they are duplicates & when you have rep vote to close. – philipxy Aug 14 '18 at 08:43