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:

[quoted text, click to view]

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


[quoted text, click to view]

HTH

Plamen Ratchev
http://www.SQLStudio.com

(Josue.Barrios@gmail.com) writes:
[quoted text, click to view]

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at