473,322 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

sql server error

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

Jan 25 '07 #1
2 4131
There seems to be only one error (the second one is a consequence of that
one), see below:

<Jo***********@gmail.comwrote in message
news:11**********************@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
Jan 25 '07 #2
(Jo***********@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, es****@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
Jan 25 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ken Lindner | last post by:
I have a need to become familiar with SQL Server 2000 for work. Needless to say I am new to SQL Server any version, but not IT in general. My employer has provided me with the SQL Server 2000...
2
by: Amanda | last post by:
From a guy in Microsoft newsgroups: | In *comp.databases.ibm-db2* there are always IBM guys | from the Toronto labs on line.Post with the | -for the love of god please help- | line...
8
by: Rod | last post by:
I have been working with ASP.NET 1.1 for quite a while now. For some reason, opening some ASP.NET applications we wrote is producing the following error message: "The Web server reported...
5
by: Bruce Schechter | last post by:
I just started to develop an ASP.NET application in vs.net 2003 . But each time I try to execute the application (which is basically empty so far), I get a dialog box titled "Microsoft Development...
17
by: Jon B | last post by:
Hi All! I have a ASP.NET 2.0 site that works on the Windows 2000 Server. However, when I tried to view this site on my local Windows XP machine, I get "Server Unavailable". If I switch the...
0
by: Suresh | last post by:
Hi Guys I have Db2 server installed on remote server. i am connecting to that remote server by using VPN. I want to connect that remote DB2 server instance using my local machine DB2...
14
by: Marcus | last post by:
I have a function that simply returns TRUE if it can connect to a particular Sql Server 2005 express, or FALSE if it cannot. I am getting some strange error codes returned when the computer that...
10
by: sara | last post by:
Hi All, I was able to connect to MS SQL Server 2005 on my computer but after a while I can not. When I want to connect to it using MS SQL Server Management Studio I got this error: An error...
5
by: James | last post by:
Hello, I have written a simple logon page that redirects to another page when successful. All works fine on my computer but when I upload it I get the error message below. I have written it...
0
by: mvsguy | last post by:
Haw anyone seen the following error under the z/OS DB2 Administration Server? BPXF024I (DASUSER) CEE0454S The message number 9512 could not be 102 found for facility ID CLB. (It appears "102" in...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.