November 6, 2019 at 5:07 pm
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
November 6, 2019 at 5:18 pm
The syntax appears to be correct
November 6, 2019 at 5:44 pm
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
November 6, 2019 at 6:31 pm
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
)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 6, 2019 at 9:08 pm
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 *******
November 6, 2019 at 9:17 pm
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
November 7, 2019 at 9:12 am
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.
***The first step is always the hardest *******
November 8, 2019 at 1:56 pm
What is the error message you receive? It would help us help you, if we knew.
November 8, 2019 at 5:29 pm
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
November 8, 2019 at 8:14 pm
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.
November 11, 2019 at 5:10 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply