How to run an INSERT Procedure with the GETDATE field? SQL Server

Asked

Viewed 449 times

0

Next, I created a precedent that has two parameters Dt_inclusion and Dt_alteration, I would like to know how to insert(or if it is possible to use) a getdate to pick up the current date, instead of always having to manually type the as for example('2020-05-07')

Below is the run of the past.

EXEC [dbo].[sp_CRUDLogin]

    @ID = 10,
    @Usuario = N'Teste',
    @Senha = N'123456',
    @Nome = N'Teste',
    @Email = N'[email protected]',
    @Telefone = N'8002-4922',
    @Dt_inclusao =  '2020-05-07' <-- esse campo gostaria de utilizar um GETDATE no lugar da data especificada.
    @Dt_alteracao = NULL,
    @StatementType = N'Insert'

My previous : ALTER PROCEDURE [dbo].[sp_CRUDLogin] ( @ID integer, @Usuario varchar(60), @Senha varchar(60), @Nome varchar(60), @Email varchar(60), @Telefone varchar(15), @Dt_inclusao datetime, @Dt_alteracao datetime, @StatementType nvarchar(20)='') AS BEGIN IF @StatementType='Insert' BEGIN INSERT INTO TbCADLogin (ID, Usuario, Senha, Nome, Email, Telefone, Dt_inclusao, Dt_alteracao) VALUES (@ID, @Usuario, @Senha, @Nome, @Email, @Telefone, @Dt_inclusao, @Dt_alteracao) END IF @StatementType='Select' BEGIN SELECT * FROM TbCADLogin END IF @StatementType = 'Update' BEGIN UPDATE TbCADLogin SET Usuario = @Usuario, Senha = @Senha, Nome = @Nome, Email = @Email, Telefone = @Telefone, Dt_inclusao = @Dt_inclusao, Dt_alteracao = @Dt_alteracao WHERE ID = @ID END ELSE IF @StatementType='Delete' begin delete from TbCADLogin where ID = @ID END END

Attempt to execute with GETDATE:

EXEC [dbo].[sp_CRUDLogin]

        @ID = 10,
        @Usuario = N'Teste',
        @Senha = N'123456',
        @Nome = N'Teste',
        @Email = N'[email protected]',
        @Telefone = N'8002-4922',
        @Dt_inclusao =  GETDATE, <--- GETDATE definido em vez do '2020-05-07'
        @Dt_alteracao = NULL,
        @StatementType = N'Insert'

When trying to use GETDATE I get the following result 

"Msg 8114, Level 16, State 5, Procedure dbo.sp_CRUDLogin, Line 0 [Batch Start Line 0]
    Erro ao converter tipo de dados nvarchar em date.

    Completion time: 2020-05-07T15:22:07.2551684-03:00"

What should I do to make this project work, so that with only GETDATE I can get the current date and time when using the Internet?

Thanks in advance!

sql-server datetime date get stored-procedures

1 answer

0

I managed to solve, I put in place the Dt_inclusion in the Process GETDATE this way

BEGIN
                INSERT INTO TbCADLogin
                            (ID,
                             Usuario,
                             Senha,
                             Nome,
                             Email,
                             Telefone,
                             Dt_inclusao,
                             Dt_alteracao)
                VALUES        (@ID,
                             @Usuario,
                             @Senha,
                             @Nome,
                             @Email,
                             @Telefone,
                             GETDATE(),
                             @Dt_alteracao)

And you can do it the same way in UPDATE:

UPDATE TbCADLogin
        SET    Usuario = @Usuario,
            Senha = @Senha,
            Nome = @Nome,
            Email = @Email,
            Telefone = @Telefone,
            Dt_inclusao = GETDATE(),
            Dt_alteracao = @Dt_alteracao
        WHERE ID = @ID

When running the precedent only leave the Dt_inclusions field blank:

EXEC    @return_value = [dbo].[sp_CRUDLogin]
        @ID = 12,
        @Usuario = N'Riptde',
        @Senha = N'123456',
        @Nome = N'Ronnan',
        @Email = N'[email protected]',
        @Telefone = N'212354632',
        @Dt_inclusao = N'',
        @Dt_alteracao = NULL,
        @StatementType = N'Insert'

With that, it worked :)

Browser other questions tagged sql-server datetime date get stored-procedures

You are not signed in. Login or sign up in order to post.