Sql Server Procedure

Question

I have the following description

 PROCEDURE [dbo].[spAVL_Ignition]
        @DeviceIMEI VARCHAR(50),
        @TimeStamp SMALLDATETIME,
        @Value INT,
        @Tag VARCHAR(50)
    AS
    BEGIN
        IF NOT EXISTS (SELECT TOP (1) TimeStamp FROM AVL_Ignition)
            INSERT INTO  AVL_Ignition (DeviceIMEI, TimeStamp, Value)
            VALUES ('@DeviceIMEI', '@TimeStamp', '@Value')
        ELSE        
            DECLARE @LastValue INT = (SELECT CONVERT (int,Value)
                                      FROM AVL_Ignition
                                      WHERE TimeStamp = (SELECT MAX(TimeStamp) FROM AVL_Ignition))
            IF(@LastValue != @Value)
                    INSERT INTO  AVL_Ignition (DeviceIMEI, TimeStamp, Value)
                    VALUES ('@DeviceIMEI', '@TimeStamp', '@Value')
    END

When I try to run the process by passing the parameters.

execute spAVL_Ignition 123, 2019-07-29 14:35:00, 1, null

SQL Server points to the following error.

Incorrect syntax close to '-'.

I also tried to do it this way.

execute spAVL_Ignition '123', '2019-07-29 14:35:00', '1', null

but pointed out another mistake.

Conversion failed to convert a string to type smalldatetime data.

What would be the correct way to pass the date by parameter ?

Answer 1

Good night Gabriel.

The error does not occur because of the values incorrectly passed in the parameters because the second example is right, but because it is writing the variables as string.

Incorrect

VALUES ('@DeviceIMEI', '@TimeStamp', '@Value')

Correct

VALUES (@DeviceIMEI, @TimeStamp, @Value)

Here is a brief correction in the code:

 PROCEDURE [dbo].[spAVL_Ignition]
        @DeviceIMEI VARCHAR(50),
        @TimeStamp SMALLDATETIME,
        @Value INT,
        @Tag VARCHAR(50)
    AS
    BEGIN
        IF NOT EXISTS (SELECT TOP (1) TimeStamp FROM AVL_Ignition)
            INSERT INTO  AVL_Ignition (DeviceIMEI, TimeStamp, Value)
            VALUES (@DeviceIMEI, @TimeStamp, @Value)
        ELSE        
            DECLARE @LastValue INT = (SELECT CONVERT (int,Value)
                                      FROM AVL_Ignition
                                      WHERE TimeStamp = (SELECT MAX(TimeStamp) FROM AVL_Ignition))
            IF(@LastValue != @Value)
                    INSERT INTO  AVL_Ignition (DeviceIMEI, TimeStamp, Value)
                    VALUES (@DeviceIMEI, @TimeStamp, @Value)
    END