quote:

---

Originally posted by jggtz

The next is the error message:
------------------------------------------------------------
Msg 156, Level 15, State 1, Procedure Astral_sp_ava, Line 73
Incorrect syntax near the keyword 'END'.
------------------------------------------------------------


The next is the Stored Procedure
It executes ok but send me the message so I can't save it in Stored Procedures folder
-------------------------------------------------------------------------------------
/****** Object: StoredProcedure [dbo].[Astral_sp_ava] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO
-- =============================================
-- Author: Juan Gilberto Gutierrez C
-- Create date: 04/06/2008
-- Description: Acumulados por : Vendedor - Articulo (if exist UPDATE else INSERT in 2 steps)
-- =============================================
CREATE PROCEDURE [dbo].[Astral_sp_ava]
AS
BEGIN
--
-- UPDATE the VendArt table with sum of Quantity and Amount
-- from detail tables (If Ejercicio+Vendedor+Articulo already exists)
UPDATE REPORTES.dbo.VendArt
SET T.VC1=T.VC1+D.VCJAN
,T.VC2=T.VC2+D.VCFEB
,T.VC3=T.VC3+D.VCMAR
,T.VC4=T.VC4+D.VCAPR
,T.VC5=T.VC5+D.VCMAY
,T.VC6=T.VC6+D.VCJUN
,T.VC7=T.VC7+D.VCJUL
,T.VC8=T.VC8+D.VCAGO
,T.VC9=T.VC9+D.VCSEP
,T.VC10=T.VC10+D.VCOCT
,T.VC11=T.VC11+D.VCNOV
,T.VC12=T.VC12+D.VCDEC
,T.VI1=T.VI1+D.VIJAN
,T.VI2=T.VI2+D.VIFEB
,T.VI3=T.VI3+D.VIMAR
,T.VI4=T.VI4+D.VIAPR
,T.VI5=T.VI5+D.VIMAY
,T.VI6=T.VI6+D.VIJUN
,T.VI7=T.VI7+D.VIJUL
,T.VI8=T.VI8+D.VIAGO
,T.VI9=T.VI9+D.VISEP
,T.VI10=T.VI10+D.VIOCT
,T.VI11=T.VI11+D.VINOV
,T.VI12=T.VI12+D.VIDEC
FROM REPORTES.dbo.VendArt T
INNER JOIN (SELECT ASTRAL.dbo.OINV.DocDate,
YEAR(ASTRAL.dbo.OINV.DocDate) AS EJE,
ASTRAL.dbo.OINV.CardCode,
ASTRAL.dbo.OINV.DocTotal,
ASTRAL.dbo.OINV.SlpCode,
ASTRAL.dbo.INV1.ItemCode,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=1 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCJAN,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=2 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCFEB,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=3 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCMAR,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=4 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCAPR,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=5 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCMAY,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=6 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCJUN,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=7 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCJUL,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=8 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCAGO,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=9 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCSEP,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=10 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCOCT,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=11 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCNOV,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=12 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END) AS VCDEC,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=1 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIJAN,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=2 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIFEB,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=3 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIMAR,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=4 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIAPR,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=5 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIMAY,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=6 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIJUN,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=7 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIJUL,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=8 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIAGO,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=9 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VISEP,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=10 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIOCT,
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=11 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VINOV, -- LINE 73 --
SUM(CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=12 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END) AS VIDEC
FROM ASTRAL.dbo.OINV INNER JOIN ASTRAL.dbo.INV1 ON
ASTRAL.dbo.OINV.DocEntry=ASTRAL.dbo.INV1.DocEntry
WHERE ASTRAL.dbo.OINV.Canceled='N' AND
ASTRAL.dbo.OINV.DocDate BETWEEN '20080415' AND '20080430'
GROUP BY EJE, ASTRAL.dbo.OINV.SlpCode, ASTRAL.dbo.INV1.ItemCode)D
ON <condition missing here>
END
GO
BEGIN
--
--INSERT a new record if Ejercicio+Vendedor+Articulo doesn't exists
INSERT INTO REPORTES.dbo.VendArt
(Ejercicio
,Vendedor
,Articulo
,VC1
,VC2
,VC3
,VC4
,VC5
,VC6
,VC7
,VC8
,VC9
,VC10
,VC11
,VC12
,VI1
,VI2
,VI3
,VI4
,VI5
,VI6
,VI7
,VI8
,VI9
,VI10
,VI11
,VI12)
SELECT D.EJE,
D.VEND,
D.ART,
D.VCJAN,
D.VCFEB,
D.VCMAR,
D.VCAPR,
D.VCMAY,
D.VCJUN,
D.VCJUL,
D.VCAGO,
D.VCSEP,
D.VCOCT,
D.VCNOV,
D.VCDEC,
D.VIJAN,
D.VIFEB,
D.VIMAR,
D.VIAPR,
D.VIMAY,
D.VIJUN,
D.VIJUL,
D.VIAGO,
D.VISEP,
D.VIOCT,
D.VINOV,
D.VIDEC
FROM (SELECT YEAR(ASTRAL.dbo.OINV.DocDate) AS EJE,
ASTRAL.dbo.OINV.SlpCode AS VEND,
ASTRAL.dbo.INV1.ItemCode AS ART,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=1 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCJAN,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=2 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCFEB,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=3 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCMAR,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=4 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCAPR,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=5 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCMAY,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=6 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCJUN,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=7 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCJUL,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=8 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCAGO,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=9 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCSEP,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=10 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCOCT,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=11 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCNOV,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=12 THEN ASTRAL.dbo.INV1.Quantity ELSE 0 END ) AS VCDEC,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=1 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIJAN,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=2 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIFEB,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=3 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIMAR,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=4 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIAPR,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=5 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIMAY,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=6 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIJUN,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=7 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIJUL,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=8 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIAGO,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=9 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VISEP,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=10 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIOCT,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=11 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VINOV,
SUM( CASE WHEN MONTH(ASTRAL.dbo.OINV.DocDate)=12 THEN ASTRAL.dbo.INV1.LineTotal ELSE 0 END ) AS VIDEC
FROM ASTRAL.dbo.OINV INNER JOIN ASTRAL.dbo.INV1 ON
ASTRAL.dbo.OINV.DocEntry=ASTRAL.dbo.INV1.DocEntry
WHERE ASTRAL.dbo.OINV.Canceled='N' AND
ASTRAL.dbo.OINV.DocDate BETWEEN '20080415' AND '20080430'
GROUP BY YEAR(ASTRAL.dbo.OINV.DocDate),ASTRAL.dbo.OINV.SlpCode,ASTRAL.dbo.INV1.ItemCode)D
LEFT JOIN REPORTES.dbo.VendArt
ON REPORTES.dbo.VendArt.Ejercicio=D.EJE
AND REPORTES.dbo.VendArt.Vendedor=D.VEND
AND REPORTES.dbo.VendArt.Articulo=D.ART
WHERE REPORTES.dbo.VendArt.Ejercicio IS NULL
END
GO

---

quote:

---

Originally posted by tkizer

You do not need GO unless you have other things you are doing in the same batch.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---