Hi, i'm trying to make a stored procedure and appear this error on the
code, i hope somebody can help me

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE factura_detalle_data
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tipo_documento VARCHAR
DECLARE @documento NUMERIC
DECLARE @item NUMERIC
DECLARE @especialista VARCHAR
DECLARE @detalle VARCHAR
DECLARE @actividad NUMERIC
DECLARE @cantidad NUMERIC
DECLARE @descuento NUMERIC
DECLARE @valor NUMERIC
DECLARE @doc_anterior NUMERIC
DECLARE @cont_item NUMERIC
DECLARE facturas CURSOR FOR SELECT Nro_Factura, Cod_Actividad,
Ced_Especialista, Cantidad, Porc_Descuento, Subtotal FROM
Detalle_Factura ORDER BY Nro_Factura ASC


SET @doc_anterior = 0
SET @cont_item = 1

OPEN facturas

WHILE(1=1)
BEGIN
FETCH NEXT FROM facturas INTO @documento, @actividad, @especialista,
@cantidad, @descuento, @valor
IF (@@FETCH_STATUS = -1)
BREAK
IF @doc_anterior = @documento
BEGIN
INSERT INTO FTC_FACTURA_DETALLE (TIPO_DOCUMENTO, DOCUMENTO, ITEM,
ESPECIALISTA, DETALLE, ACTIVIDAD, CANTIDAD, DESCUENTO, VALOR) VALUES
('FD', @documento, @cont_item, @especialista, ' ', @actividad,
@cantidad, @descuento, @valor)
SET @cont_item = @cont_item + 1 *(first error)
END

IF @doc_anterior <> @documento
BEGIN
@cont_item = 1
INSERT INTO FTC_FACTURA_DETALLE (TIPO_DOCUMENTO, DOCUMENTO, ITEM,
ESPECIALISTA, DETALLE, ACTIVIDAD, CANTIDAD, DESCUENTO, VALOR) VALUES
('FD', @documento, @cont_item, @especialista, ' ', @actividad,
@cantidad, @descuento, @valor)
END
SET @doc_anterior = @documento *(second error)
END
CLOSE facturas
DEALLOCATE facturas
END

and the errors are:
Msg 102, Level 15, State 1, Procedure factura_detalle_data, Line 58
Incorrect syntax near '@cont_item'.
Msg 102, Level 15, State 1, Procedure factura_detalle_data, Line 65
Incorrect syntax near 'END'.

Thanks for the help

There seems to be only one error (the second one is a consequence of that
one), see below:

<Josue.Barrios@gmail.com> wrote in message
news:1169760863.784834.307020@m58g2000cwm.googlegr oups.com...
> Hi, i'm trying to make a stored procedure and appear this error on the
> code, i hope somebody can help me
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE factura_detalle_data
> AS
> BEGIN
> SET NOCOUNT ON;
> DECLARE @tipo_documento VARCHAR
> DECLARE @documento NUMERIC
> DECLARE @item NUMERIC
> DECLARE @especialista VARCHAR
> DECLARE @detalle VARCHAR
> DECLARE @actividad NUMERIC
> DECLARE @cantidad NUMERIC
> DECLARE @descuento NUMERIC
> DECLARE @valor NUMERIC
> DECLARE @doc_anterior NUMERIC
> DECLARE @cont_item NUMERIC
> DECLARE facturas CURSOR FOR SELECT Nro_Factura, Cod_Actividad,
> Ced_Especialista, Cantidad, Porc_Descuento, Subtotal FROM
> Detalle_Factura ORDER BY Nro_Factura ASC
>
>
> SET @doc_anterior = 0
> SET @cont_item = 1
>
> OPEN facturas
>
> WHILE(1=1)
> BEGIN
> FETCH NEXT FROM facturas INTO @documento, @actividad, @especialista,
> @cantidad, @descuento, @valor
> IF (@@FETCH_STATUS = -1)
> BREAK
> IF @doc_anterior = @documento
> BEGIN
> INSERT INTO FTC_FACTURA_DETALLE (TIPO_DOCUMENTO, DOCUMENTO, ITEM,
> ESPECIALISTA, DETALLE, ACTIVIDAD, CANTIDAD, DESCUENTO, VALOR) VALUES
> ('FD', @documento, @cont_item, @especialista, ' ', @actividad,
> @cantidad, @descuento, @valor)
> SET @cont_item = @cont_item + 1 *(first error)
> END
>
> IF @doc_anterior <> @documento
> BEGIN
> @cont_item = 1

Here is the issue, you are missing the SET. The line above should be: SET
@cont_item = 1


> INSERT INTO FTC_FACTURA_DETALLE (TIPO_DOCUMENTO, DOCUMENTO, ITEM,
> ESPECIALISTA, DETALLE, ACTIVIDAD, CANTIDAD, DESCUENTO, VALOR) VALUES
> ('FD', @documento, @cont_item, @especialista, ' ', @actividad,
> @cantidad, @descuento, @valor)
> END
> SET @doc_anterior = @documento *(second error)
> END
> CLOSE facturas
> DEALLOCATE facturas
> END
>
> and the errors are:
> Msg 102, Level 15, State 1, Procedure factura_detalle_data, Line 58
> Incorrect syntax near '@cont_item'.
> Msg 102, Level 15, State 1, Procedure factura_detalle_data, Line 65
> Incorrect syntax near 'END'.
>
> Thanks for the help
>

HTH

Plamen Ratchev
http://www.SQLStudio.com

(Josue.Barrios@gmail.com) writes:
> Hi, i'm trying to make a stored procedure and appear this error on the
> code, i hope somebody can help me

Plamen showed you were the syntax error was. I will show you how you
can write the code without a cusror at all:

INSERT FTC_FACTURA_DETALLE (TIPO_DOCUMENTO, DOCUMENTO,
ITEM,
ESPECIALISTA, DETALLE, ACTIVIDAD,
CANTIDAD, DESCUENTO, VALOR)
SELECT 'FD', Nro_Factura,
row_number() over (PARTITION BY Nro_Factura ORDER BY
Nro_Factura),
Cod_Eespecialista, ' ', Cod_actividad,
Cantidad, Porc_Descuento, Subtotal
FROM Detalle_Factura

This solution only works on SQL 2005 due to the row_number() function.
On SQL 2000 you would bounce the data over a temp table with an IDENTITY
column, a more complicated solution. But still far better than a cursor.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx