Solved

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

Posted on 2014-10-12
2
439 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 40

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 10

Accepted Solution

by:
HuaMinChen earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now