how not insert double records with null values

Question

I'm trying to find a way to insert a new record only if it doesn't exist in the table.

Here's the stored procedure:

USE [ABSTest]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertBudgetCommessaInPreventivo]    Script     Date: 01/07/2013 09:12:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Alex Ziani>
-- Create date: <04/01/2013>
-- Description: <Insert di valori con inserimento di parametri>
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertBudgetCommessaInPreventivo] 
@Progressivo                    nvarchar(50),
@Durata                         float,
@Costo                          money,
@CostoStandard                  money,
@Tariffa                        money,
@SpeseStandard                  money,
@Codice_Tipo                    nvarchar(50),
@Codice_FiguraProfessionale     nvarchar(10),
@Codice_Risorsa                 nvarchar(50),
@Codice_Commessa                nvarchar(20),
@Codice_ODL                     nvarchar(30),
@Task                           nvarchar(100)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF NOT EXISTS   (SELECT * FROM Preventivo WHERE Progressivo = @Progressivo 
            AND Quantita = @Durata 
            AND Costo = @Costo  
            AND CostoStandard = @CostoStandard 
            AND Tariffa = @Tariffa 
            AND SpeseStandard = @SpeseStandard 
            AND FkCodiceEnumTipoPreventivo = @Codice_Tipo 
            AND FkCodiceEnumFiguraProfessionale = @Codice_FiguraProfessionale
            AND FKCodiceRisorsa = @Codice_Risorsa 
            AND FkCodiceCommessa = @Codice_Commessa 
            AND FkCodiceOrdineDiLavoro = @Codice_ODL
            AND Task = @Task)               
BEGIN           
    INSERT INTO [ABSTest].[dbo].[Preventivo]
           ([Progressivo] 
           ,[Quantita] 
           ,[Costo]
           ,[CostoStandard]
           ,[Tariffa]
           ,[SpeseStandard]
           ,[FkCodiceEnumTipoPreventivo]
           ,[FkCodiceEnumFiguraProfessionale]
           ,[FKCodiceRisorsa]
           ,[FkCodiceCommessa]
           ,[FkCodiceOrdineDiLavoro]
           ,[Task])
     VALUES
           (@Progressivo
           ,@Durata
           ,@Costo
           ,@CostoStandard 
           ,@Tariffa
           ,@SpeseStandard 
           ,@Codice_Tipo 
           ,@Codice_FiguraProfessionale
           ,@Codice_Risorsa 
           ,@Codice_Commessa 
           ,@Codice_ODL
           ,@Task)
 END
 ELSE
 BEGIN
    RAISERROR 50001 'ERRORE: RECORD GIà PRESENTE'
 END
END

This works fine, but all the values (except the primary key) are Nullable, so if i put as parameters Null values, it doesn't work. How can I engineer it work with Nulls aswell?

mornaner · Accepted Answer · 2013-01-07 12:34:32Z

2

You can use below code for checking it:

IF NOT EXISTS   (SELECT * FROM Preventivo WHERE ISNULL(Progressivo,'') = ISNULL(@Progressivo,'') 
        AND ISNULL(Quantita,0) = ISNULL(@Durata,0) 
        AND ISNULL(Costo,0) = ISNULL(@Costo,0)
        AND ISNULL(CostoStandard,0) = ISNULL(@CostoStandard,0)
        AND ISNULL(Tariffa,0) = ISNULL(@Tariffa,0)
        AND ISNULL(SpeseStandard,0) = ISNULL(@SpeseStandard,0)
        AND ISNULL(FkCodiceEnumTipoPreventivo,'') = ISNULL(@Codice_Tipo,'')
        AND ISNULL(FkCodiceEnumFiguraProfessionale,'') = ISNULL(@Codice_FiguraProfessionale,'')
        AND ISNULL(FKCodiceRisorsa,'') = ISNULL(@Codice_Risorsa,'') 
        AND ISNULL(FkCodiceCommessa,'') = ISNULL(@Codice_Commessa,'')
        AND ISNULL(FkCodiceOrdineDiLavoro,'') = ISNULL(@Codice_ODL,'')
        AND ISNULL(Task,'') = ISNULL(@Task,'') )

edited Jan 7, 2013 at 12:34

mornaner

2,4242 gold badges28 silver badges39 bronze badges

answered Jan 7, 2013 at 12:13

Raju M

312 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

erichste · Accepted Answer · 2013-01-07 11:06:21Z

1

Use ISNULL as the comparison operator. Might slow down a bit

SELECT * FROM Preventivo WHERE ISNULL(Progressivo, '') = ISNULL(@Progressivo, '')

answered Jan 7, 2013 at 11:06

erichste

7795 silver badges17 bronze badges

Comments

Prasanth V J · Accepted Answer · 2013-01-07 11:16:16Z

1

You can achieve this using Case statement

For eg

AND Quantita = CASE @Durata
WHEN null THEN Quantita
ELSE @Durata

So when @Durata==null case will return column Quantita itself so the result will be true (Quantita =Quantita )

answered Jan 7, 2013 at 11:16

Prasanth V J

1,20615 silver badges33 bronze badges

Comments

Pranay Rana · Accepted Answer · 2013-01-07 11:04:52Z

0

for null you need to put conditon

where column_name  is null

in your cause you can make use of IsNull() function available in sql server like this

 where IsNull(column_name,-1) = Isnull(@variable,-1)

compare value of variable with column as given above resolve the issue easily

answered Jan 7, 2013 at 11:04

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

Comments

Raj · Accepted Answer · 2013-01-07 11:10:54Z

0

Your stored procedure is trying to do

NULL = NULL

Change the first statement of the Stored Procedure and it should work

SET ANSI_NULLS OFF;

If you are using SQL 2008 R2 or above, please consider using MERGE statements.

Raj

answered Jan 7, 2013 at 11:10

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Collectives™ on Stack Overflow

how not insert double records with null values

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related