none
Allow null values+INSERT INTO

    Question

  • Hi

    I have a table dbo.AlarmContacts, it looks like this:

    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Region] [nvarchar](20) NOT NULL,
    [Alarm] [int] NOT NULL,
    [Contact1Id] [int] NULL,
    [Contact2Id] [int] NULL

    I use the following Query to insert a new record:

    INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id], [Contact2Id]) 
    SELECT @Region, @Alarm, Con1.[ID], Con2.[ID]
    FROM dbo.Contacts Con1, dbo.Contacts Con2
    WHERE Con1.[Name]=@Name1 AND Con2.[Name]=@Name2

    When I pass null value or '' in either @Name1 og @Name2 I simply get the output message 0 row(s) affected.
    How can i correct my query to allow '' and null values?


    Sunday, March 03, 2013 7:20 PM

Answers

  •  INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id],
    [Contact2Id]) 
     SELECT @Region, @Alarm,
            (SELECT Con1.[ID] FROM dbo.Contacts Con1 WHERE Con1.[Name]=@Name1),
            (SELECT Con2.[ID] FROM dbo.Contacts Con2 WHERE Con1.[Name]=@Name2)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 03, 2013 7:50 PM

All replies

  •  INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id],
    [Contact2Id]) 
     SELECT @Region, @Alarm,
            (SELECT Con1.[ID] FROM dbo.Contacts Con1 WHERE Con1.[Name]=@Name1),
            (SELECT Con2.[ID] FROM dbo.Contacts Con2 WHERE Con1.[Name]=@Name2)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 03, 2013 7:50 PM
  • Something like this ?

    INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id], [Contact2Id]) 
    SELECT @Region, @Alarm, Con1.[ID], Con2.[ID]
    FROM dbo.Contacts Con1, dbo.Contacts Con2
    WHERE 
    (CASE WHEN (
    	(Con1.[Name] IS NULL AND @Name1 IS NULL 
    	OR Con1.[Name] = @Name1))
    	THEN 1 ELSE 0 END) = 1 
    	OR
    (CASE WHEN (
    	(Con2.[Name] IS NULL AND @Name2 IS NULL 
    	OR Con2.[Name] = @Name2))
    	THEN 1 ELSE 0 END) = 1 


    Narsimha

    Sunday, March 03, 2013 7:56 PM
  • What do you want to insert in case you pass nothing for Name1 or Name2 parameters? Also, did you see my last answer in your other thread?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, March 03, 2013 8:18 PM
    Moderator
  • I'm not sure I understand why this allows null and my posted INSERT didn't?
    Thursday, March 07, 2013 4:43 PM
  • Yes, and yes thank you :)

    Thursday, March 07, 2013 4:45 PM
  • Thank you for your post :)

    I couldn't get it working, but the thread is solved now.

    Thursday, March 07, 2013 4:47 PM