Insert Statement with IF Statement

• Meatloaf

  SSCrazy

  Points: 2564

  More actions

  January 18, 2015 at 11:38 am

  #296786

  Is it possible to have an Insert Statement with an IF?

  For example I want to check the value of a field upon insert. If the value is '' then I want to pass the value NULL

  Here is what I am currently trying to do:

  INSERT(Test1) VALUES ((case when @test1='' then @test1=NULL else @test1 end))

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  January 18, 2015 at 12:29 pm

  #1771000

  Meatloaf (1/18/2015)

  ---

  Is it possible to have an Insert Statement with an IF?

  For example I want to check the value of a field upon insert. If the value is '' then I want to pass the value NULL

  Here is what I am currently trying to do:

  INSERT(Test1) VALUES ((case when @test1='' then @test1=NULL else @test1 end))

  Yes, although (and I haven't tried it) you'll probably have to add the word "SELECT" just before the word "case".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 18, 2015 at 1:34 pm

  #1771005

  Jeff Moden (1/18/2015)

  ---

  Meatloaf (1/18/2015)

  ---

  Is it possible to have an Insert Statement with an IF?

  For example I want to check the value of a field upon insert. If the value is '' then I want to pass the value NULL

  Here is what I am currently trying to do:

  INSERT(Test1) VALUES ((case when @test1='' then @test1=NULL else @test1 end))

  Yes, although (and I haven't tried it) you'll probably have to add the word "SELECT" just before the word "case".

  I think in that case you need to remove the VALUES clause and do it like this:

  INSERT(Test1)

  SELECT CASE WHEN @test1='' THEN @test1=NULL ELSE @test1 END

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Meatloaf

  SSCrazy

  Points: 2564

  More actions

  January 18, 2015 at 2:08 pm

  #1771006

  Thanks guys! I have it working in SQL. However, when I try to run it in my .Net app, I get a message saying "Error: Must declare the scalar variable '@test1".

  Do you have any suggestions? Do you know how to declare the variable in .Net?

  Thanks again!

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 18, 2015 at 2:35 pm

  #1771007

  Meatloaf (1/18/2015)

  ---

  Thanks guys! I have it working in SQL. However, when I try to run it in my .Net app, I get a message saying "Error: Must declare the scalar variable '@test1".

  Do you have any suggestions? Do you know how to declare the variable in .Net?

  Thanks again!

  You can't declare a variable in .NET and then use it in TSQL.

  In TSQL you can declare a variable like this:

  DECLARE @test1 int

  SET @test1 = 14

  Perhaps you can give more information about exactly what you are trying to accomplish here and using what mechanism? If you are writing TSQL in .NET I wonder if you can't just create a fully dynamic SQL statement, completely eliminating the variable? Obviously you would need to guard against SQL Injection that way.

  Another potential is to look up ADO.NET parameterization and also look up sp_executesql in SQL Server Books Online.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  January 18, 2015 at 3:57 pm

  #1771010

  Meatloaf (1/18/2015)

  ---

  Thanks guys! I have it working in SQL. However, when I try to run it in my .Net app, I get a message saying "Error: Must declare the scalar variable '@test1".

  Do you have any suggestions? Do you know how to declare the variable in .Net?

  Thanks again!

  Like Kevin stated, the variable must be declared in the SQL an then a value must be passed to that parameter in SQL OR the GUI must parameterize the query in the embedded code.

  He's also correct that it would be better to remove the VALUES statement and replace it with the SELECT like he demonstrated.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• DonlSimpson

  SSCertifiable

  Points: 7043

  More actions

  January 20, 2015 at 2:09 pm

  #1771611

  INSERT(Test1)

  SELECT CASE WHEN @test1='' THEN @test1=NULL ELSE @test1 END

  Is the "@Test1=NULL" in the middle of the case statement correct? Shouldn't that just be "NULL"?

  Don Simpson
  

  ---

  
  I'm not sure about Heisenberg.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 20, 2015 at 2:52 pm

  #1771630

  DonlSimpson (1/20/2015)

  ---

  INSERT(Test1)

  SELECT CASE WHEN @test1='' THEN @test1=NULL ELSE @test1 END

  Is the "@Test1=NULL" in the middle of the case statement correct? Shouldn't that just be "NULL"?

  DUH!! Copy and paste error on my part - didn't check validity! :blink:

  INSERT(Test1)

  SELECT CASE WHEN @test1='' THEN NULL ELSE @test1 END

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service