I need to create a Stored Procedure in my SQL Server database. The purpose of this Stored Procedure is to write data to a Log table, which has three fields: date, time, login ID.
Date and time would be the time of login, while the Login ID comes from a table of logins. The site will have the time to call this Stored Procedure at the moment of login.
The big question is what code I put in the Stored Procedure, since I hardly work with this in SQL Server. Who can help with this?
Basically you only need the following.
CREATE Proc [dbo].[sp_LogUsuario_Update]
@IdUsuario int,
@DtInclusao int
-- se precisa de mais campo adicione aqui.
as
begin
Set Xact_Abort on
Set Nocount on
Begin Tran
begin
-- informe os campos que você que inseri na sua tabela
Insert into tb_LogUsuario( IdUsuario, DtInclusao) values
(
--- aqui você passa seus campos caso a data seja a hora local user o GETDATE() , o tipo de campo da tabela tem
-- que ser DAteTime
@IdUsuario, @DtInclusao
)
end
Commit
end
GO
Change what is needed according to your need.
CREATE PROCEDURE [dbo].[sp_LogUsuario]
(
@id int,
@data int
)
AS
BEGIN
---------
-- LOG --
---------
DECLARE @txtransacao VARCHAR(1000) = '@data=' + CONVERT(VARCHAR(200), ISNULL(GETDATE(), '')) + ',@id=' + CONVERT(VARCHAR(100), ISNULL(@id, 0))
INSERT INTO log_acaoUsuario (log_txmensagem)
VALUES (@txtransacao) -- TENS QUE CRIAR ESSA TABELA COM ESSA COLUNA (qualquer nome)
---------------
-- VARIÁVEIS --
---------------
------------------
-- PROCEDIMENTO --
------------------
Now just do it
SELECT * FROM log_acaoUsuario
And there's the log