Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Allow null values+INSERT INTO

Answered Allow null values+INSERT INTO

  • Sunday, March 03, 2013 7:20 PM
     
     
    Sign In to Vote
    0
    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?


     

All Replies

  • Sunday, March 03, 2013 7:50 PM
     
     Answered
    Sign In to Vote
    0

     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
    • Proposed As Answer by Satheesh Variath Monday, March 04, 2013 5:08 AM
    • Marked As Answer by quarck2 Thursday, March 07, 2013 4:47 PM
    •  
     
  • Sunday, March 03, 2013 7:56 PM
     
      Has Code
    Sign In to Vote
    0

    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 8:18 PM
    Moderator
     
     
    Sign In to Vote
    0
    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

     
  • Thursday, March 07, 2013 4:43 PM
     
     
    Sign In to Vote
    0
    I'm not sure I understand why this allows null and my posted INSERT didn't?
     
  • Thursday, March 07, 2013 4:45 PM
     
     
    Sign In to Vote
    0

    Yes, and yes thank you :)

     
  • Thursday, March 07, 2013 4:47 PM
     
     
    Sign In to Vote
    0

    Thank you for your post :)

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