What´s the error in my SP

[marcela24]

I have this stored procedure but it is wrong:
I got this error message:
Line 30: Incorrect syntax near '@id_ponto'.
How can i fix that?

CREATE PROC proc_grava_dados_sti
@id int,
@identificador varchar(200),
@data datetime,
@latitude varchar(100),
@longitude varchar(100),
@velocidade int,
@direcao int,
@s1 bit,
@s2 bit,
@s3 bit,
@s4 bit,
@s5 bit,
@s6 bit,
@s7 bit,
@s8 bit,
@texto nvarchar(100)

AS
BEGIN
SET NOCOUNT ON

DECLARE @id_unidade varchar(200), @id_ponto int

SET @id_unidade = ( SELECT id_veiculo FROM tveiculos WHERE codigoTransmissor2 = @identificador )

INSERT INTO tLog_Mensagem ( id_mensagem, id_veiculo, data, mensagem ) VALUES
(@id, @id_unidade, @data, @texto)

@id_ponto = INSERT INTO tpontos (id_veiculo, latitude, longitude, dtPonto) VALUES (@id_unidade, @latitude, @longitude, @data) SELECT @@IDENTITY

INSERT INTO Status (id_ponto, id_sensor, valor) VALUES (@id_ponto, 1, @s1)
INSERT INTO Status (id_ponto, id_sensor, valor) VALUES (@id_ponto, 2, @s2)
INSERT INTO Status (id_ponto, id_sensor, valor) VALUES (@id_ponto, 3, @s3)
INSERT INTO Status (id_ponto, id_sensor, valor) VALUES (@id_ponto, 4, @s4)
INSERT INTO Status (id_ponto, id_sensor, valor) VALUES (@id_ponto, 5, @s5)
INSERT INTO Status (id_ponto, id_sensor, valor) VALUES (@id_ponto, 6, @s6)
INSERT INTO Status (id_ponto, id_sensor, valor) VALUES (@id_ponto, 7, @s7)
INSERT INTO Status (id_ponto, id_sensor, valor) VALUES (@id_ponto, 8, @s8)

RETURN @@error

END

 

[JamesLean]

Add the SET keyword:

SET @id_ponto = INSERT INTO tpontos...

--James

 

[JamesLean]

Sorry, mis-read it! Change to this:

INSERT INTO tpontos (id_veiculo, latitude, longitude, dtPonto)
VALUES (@id_unidade, @latitude, @longitude, @data)

SELECT @id_ponto = @@IDENTITY

--James

 

[SQLSister]

If you want to be sure that you get the identity value just inserted, then do not use @@Identity use SCOPE_IDENTITY( ) instead as it limits it to just the last identity used in the current scope. @@Identity will give the last value inserted even if it was from someone else running a differnt procedure.

Also if this statement could return more than one record, your process won't work.
SELECT id_veiculo FROM tveiculos WHERE codigoTransmissor2 = @identificador

 

[marcela24]

How can i make a transaction inside my procedure, because when it happens any error at the end of my code it still does the begining and insert in my data base. I want it to do nothing if something fails. How can i do this?

 

[SQLSister]

Look up BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION in Books Online