Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Store procedure - Syntax - Why not running properly-- ???

Posted on 2014-10-12
2
Medium Priority
?
466 Views
Last Modified: 2014-10-16
I have the following the code from a book but I'm getting the following error. Not Sure why. I run this into SQL Server Management Studio and create a new store procedure... What I'm missing>sqlManagement
Code:

DROP PROCEDURE IF EXISTS customer_sales
$$
CREATE PROCEDURE customer_sales
        (in_customer_id INT)
   READS SQL DATA
BEGIN
    DECLARE total_sales NUMERIC(8,2);

    SELECT SUM(NumberOfEmployees)
      INTO total_sales
      FROM dbo.Customer
     WHERE CustomerNumber=in_customer_id;

    SELECT CONCAT('Total sales for ',in_customer_id,' is ',total_sales);
END;
$$

Open in new window


Error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'in_customer_id'.
Msg 155, Level 15, State 2, Line 7
'NUMERIC' is not a recognized CURSOR option.
Msg 195, Level 15, State 10, Line 14
'CONCAT' is not a recognized built-in function name.
0
Comment
Question by:yguyon28
2 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 40376120
This is not SQL Server sproc. try this if you want to run this in SQL Server.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('customer_sales'))
BEGIN
    DROP PROCEDURE customer_sales
END
GO

CREATE PROCEDURE customer_sales 
@in_customer_id INT
AS
    DECLARE @total_sales NUMERIC(8,2);

    SELECT @total_sales = SUM(NumberOfEmployees)
      FROM dbo.Customer
     WHERE CustomerNumber=in_customer_id;

    SELECT 'Total sales for ' + CONVERT(VARCHAR,@in_customer_id) + ' is ' + CONVERT(VARCHAR,@total_sales);

Open in new window

0
 
LVL 11

Accepted Solution

by:
HuaMinChen earned 2000 total points
ID: 40376228
Try to use Decimal(12,2) instead of numeric, and ensure that you have created the relevant tables being used by the SP.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question