CODEWITHSUNDEEP
sql-serverstored-procedures
ashok poudel
ashok poudel

Reputation: 723

SQL Server data not being shown while executing with stored procedure

I have a stored procedure as follows:

CREATE PROCEDURE [dbo].[pa_AnadeCine3]
    @Nombre AS      VARCHAR(50) = '',
    @Empresa AS     VARCHAR(50) = '',
    @CIF AS         VARCHAR(12) = '',
    @Direccion AS   VARCHAR(50) = '',
    @Localidad AS   VARCHAR(50) = '',
    @Provincia AS   VARCHAR(20) = '',
    @CP AS          VARCHAR(5) = '',
    @Tlf1 AS        VARCHAR(12) = '',
    @NumeroSalas AS SMALLINT = 0,
    @Metro AS       VARCHAR(50) = '',
    @Bus AS         VARCHAR(50) = '',
    @Email AS       VARCHAR(100) = '',
    @GPS AS         VARCHAR(100) = ''
AS
    DECLARE @Fecha AS VARCHAR(23)
    DECLARE @Error AS INT

    SET @Fecha = CONVERT(VARCHAR(23), GETDATE(), 121)

    BEGIN TRANSACTION
        INSERT INTO dbo.SiveCines (Nombre, Empresa, CIF, Direccion, Localidad, Provincia, CP, Tlf1, NumeroSalas, Metro, Bus, Email, GPS, FechaActualizacion)
        VALUES (@Nombre, @Empresa, @CIF, @Direccion, @Localidad, @Provincia, @CP, @Tlf1, @NumeroSalas, @Metro, @Bus, @Email, @GPS, @Fecha)

        SELECT
            @@ERROR    AS N'Error',
            @@ROWCOUNT AS N'Fila',
            @@IDENTITY as N'ID_Cine'

      /* CONTROL DE ERRORES. */
      IF @Error = 0
      BEGIN
          COMMIT TRANSACTION
      END
      ELSE
      BEGIN
          ROLLBACK TRANSACTION
      END

The problem is when I execute this stored procedure, no error occurs, and a new cine_id is returned for example 2. But the data doesn't show up in the database when I do select * from sivecines where sivecines.ID_Cine=2 and the data is also not inserted when I view all the data. But when I insert the new data manually then Id_cine of 2 is escaped and new Id_cine will become 3. I don't know where the data with ID_Cine=2 went.

Can anyone help me with this problem? Thanks in advance

Upvotes: 0

Views: 81

Answers (1)

dbajtr
dbajtr

Reputation: 2044

It looks like you'll never commit the transaction as you are not setting @Error

you declare it then use:

  IF @Error = 0
    BEGIN
      COMMIT TRANSACTION
    END

Declaring a variable and not declaring it defaults to NULL:

DECLARE @Error INT
SELECT @Error

Would return NULL

NULL <> 0 :

IF 0 = NULL
    BEGIN
        PRINT 1;
END;
    ELSE
    BEGIN
        PRINT 2;
END;

Returns 2

Check to see if there are any open transactions when you run the proc or add a WITH (NOLOCK) hint when you query the table to see if the data is there

Upvotes: 2

Related Questions