Using CASE Statement to SET variable when null value

  • gjoelson 29755

    Mr or Mrs. 500

    Points: 591

    Hi ,

    I have a SP , that does an update to add comments to the table. However when there is a Null value it errors out.

    Im trying to use a Case when to put in a default value if there comment passed are null. Ive put this together but not sure if I have the syntax correct. could you tell me if will work this way ?

     

    ALTER PROCEDURE [dbo].[APUpdateComments]
    (
    @DocID varchar (50),
    @ID varchar (50),
    @Comments [varchar](150)
    )
    AS
    BEGIN

    SET NOCOUNT ON

    Update APFinal
    Set Comments =
    Case when @Comments is null then 'No Comments Provided'
    else @Comments end
    Where DocID = @DocID and ID = @ID
    END

     

  • DesNorton

    SSC-Insane

    Points: 22811

    The syntax appears to be correct

  • drew.allen

    SSC Guru

    Points: 76632

    You want help troubleshooting an error message, but you didn't think that the text of the error message was relevant?

    Why are you using a CASE expression rather than a COALESCE().  They both do exactly the same thing, but the COALESCE() is shorter and less prone to errors.  UPDATE APFinal SET Comments = COALESCE(@Comments, 'No Comments Provided') WHERE....

    You're also better off just inserting the 'No Comments Provided' when you create the record instead of going back and updating the record later.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Phil Parkin

    SSC Guru

    Points: 243762

    If the Comments argument is sometimes not being passed at all, you can default it like this

    ALTER PROCEDURE [dbo].[APUpdateComments]
    (
    @DocID varchar (50),
    @ID varchar (50),
    @Comments [varchar](150) = NULL
    )

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • SGT_squeequal

    SSCertifiable

    Points: 7131

    you can also use isnull function

    Update APFinal

    Set Comments = isnull(@Comments,  'No Comments Provided')

    Where DocID = @DocID and ID = @ID

     

     

    ***The first step is always the hardest *******

  • drew.allen

    SSC Guru

    Points: 76632

    SGT_squeequal wrote:

    you can also use isnull function

    Update APFinal

    Set Comments = isnull(@Comments,  'No Comments Provided')

    Where DocID = @DocID and ID = @ID

    I recommend NOT using the ISNULL() function, which is why I didn't mention it.  ISNULL() uses the data type of the first argument, whereas COALESCE() uses data type with the highest precedence from all of the arguments.  ISNULL() is limited to two parameters, whereas COALESCE() takes an "unlimited" number of parameters.  At a previous employer, we were forbidden to use ISNULL(), because of the above.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • SGT_squeequal

    SSCertifiable

    Points: 7131

    in the SQL the parameter passed is varchar(150) ,so the datatype wont change, the oppo only asks if the parameter is null populate it with this value, so there wont be a 3rd or 4th. They now have all the available options available.

     

    Just because your previous employer forbid you to use isnull does not mean it cant be used.

    • This reply was modified 6 days, 9 hours ago by  SGT_squeequal.

    ***The first step is always the hardest *******

  • Chris Wooding

    SSCarpal Tunnel

    Points: 4248

    What is the error message you receive? It would help us help you, if we knew.

  • drew.allen

    SSC Guru

    Points: 76632

    SGT_squeequal wrote:

    Just because your previous employer forbid you to use isnull does not mean it cant be used.

    I never said it "can't" be used.  I said it SHOULDN'T be used.

    I also think that there is merit in consistency.  Even though none of the points that I brought up are required by this very specific situation, there are general cases where they do apply.  For consistency, it is better to use COALESCE() throughout rather than switching back and forth between ISNULL() and COALESCE().

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Chris Harshman

    SSC-Forever

    Points: 41810

    I've sometimes seen problems caused by COALESCE because it has that quirk of choosing the data type of all its parameters with the highest precedence.  Either should be used sparingly and carefully.  I don't think there is enough evidence to say ISNULL shouldn't be used.

  • ScottPletcher

    SSC Guru

    Points: 98168

    drew.allen wrote:

    You're also better off just inserting the 'No Comments Provided' when you create the record instead of going back and updating the record later.

    Drew

    True, but even better would be to leave NULL in the row, and return 'No Comments Provided' from a view or even a computed column, rather than wasting space by storing 'No Comments Provided' in row after row.  This becomes much more true if later you decide to change the literal used for NULL comments to some other string; if it's stored, you have to physically update every row, but if it's derived in one place, you simple change it in that one place.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply