September 17, 2011 at 6:05 am
Hi All,
Im making an application which as 7 stages, first stage is where the user raise an issue, the next is for it to be authorised by there team leader etc but when the team leader selects the department priority which will be a number from 1-10 and saves it it updates the table which is fine,
the problem i have is the next stage after that and so forth, where it goes to the business analyst where he/she enters information and when they update the call the field intDeptPriority (which the authorises) has chosen a value gets turned to NULL because it hasnt been passed in because there no longed on the authoriser page.....
how can i stop SQL entering a null Value in to the parameters that are passed in? i have one Update statement which is here....is there a way i can check to see if a parameter is null and if it is leave the value that is already in the table column? other wise update it, is that makes sense? here the SQL Update Procedure
UPDATE wrp_Work
SET chrWorkDesc = @spDescription
,chrWorkTitle = @spTitle
,intStatus = @spStatusID
,intAssignTo = @spAssignUserID
,chrAssignToType = @spAssignUserType
,intModBy = @spUserID
,datModified = @CurrentDate
,chrJustification = @spJustification
,chrUserRef = @spUserRef
, chrEstimateHigh = @spEstimateHigh
, chrEstimateLow = @spEstimateLow
, chrActualWork = @spActualWork
, intCategoryID = @spCategoryID
, intPriority = @spPriorityID
, datRequired = convert(datetime,@spRequiredBy, 103)
, intRaisedFor = @spUserID
, intBusinessArea = @spBusinessAreaID
, intSignedOffByID = @spintSignedOffByID
, intTableChange = @spTableChange
, intRamping = @spRamping
, intPapyrus = @spPapyrus
, intWeb = @spWeb
, intDataWarehouse = @spDataWarehouse
, intDeptPriority = @spDeptPriority
, intITValLvEst = @spITLevelEstimate
, intDevelopmentLvEst = @spDevelopmentLevelEstimate
, chrAldonTaskNum = @spAldonTaskNumber
, intBALvEst = @spBATestEstimate
, intBAUserTestEst = @spBAUserTestEstimate
, intReleaseNum = @spReleaseNumber
WHERE intWorkID = @spWorkID
September 17, 2011 at 6:09 am
SET
chrWorkDesc = ISNULL(@spDescription, chrWorkDesc ),
chrWorkTitle = ....
Whether that's the best solution is another matter, but it will do what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2011 at 6:15 am
Hi Thanks for the response
am i missing a quote or something because im getting incorrect syntax near the keyword 'Set'
UPDATE wrp_Work
SET chrWorkDesc = ISNULL(@spDescription, chrWorkDesc),
SET chrWorkTitle = ISNULL(@spTitle, chrWorkTitle),
SET intStatus = ISNULL(@spStatus, intStatus),
intAssignTo = @spAssignUserID
,chrAssignToType = @spAssignUserType
,intModBy = @spUserID
,datModified = @CurrentDate
,chrJustification = @spJustification
,chrUserRef = @spUserRef
, chrEstimateHigh = @spEstimateHigh
, chrEstimateLow = @spEstimateLow
, chrActualWork = @spActualWork
, intCategoryID = @spCategoryID
, intPriority = @spPriorityID
, datRequired = convert(datetime,@spRequiredBy, 103)
, intRaisedFor = @spUserID
, intBusinessArea = @spBusinessAreaID
, intSignedOffByID = @spintSignedOffByID
, intTableChange = @spTableChange
, intRamping = @spRamping
, intPapyrus = @spPapyrus
, intWeb = @spWeb
, intDataWarehouse = @spDataWarehouse
, intDeptPriority = @spDeptPriority
, intITValLvEst = @spITLevelEstimate
, intDevelopmentLvEst = @spDevelopmentLevelEstimate
, chrAldonTaskNum = @spAldonTaskNumber
, intBALvEst = @spBATestEstimate
, intBAUserTestEst = @spBAUserTestEstimate
, intReleaseNum = @spReleaseNumber
WHERE intWorkID = @spWorkID
on the second set statement
September 17, 2011 at 6:23 am
One SET in an update, the way your first update was.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2011 at 8:04 am
oo i see, Sorry SQL isnt my Strong point as you can tell,
I have just been informed that the call loggin system im currently making will be giving the user the option to pass back to the previous stage for additional information if needs be,
if i implement what you have given me, will this effect the pass back for example
if i had a column called username
and i placed my username in there Joe Bloggs for example and pressed update and the username got stored in SQL
and in the next section of the lifecycle of the task the person passed it back to me saying wrong name and i then changed it from Joe BLoggs to Mary BLoggs will it still update even though that field isnt null?
September 17, 2011 at 8:25 am
Nothing in the suggestion I gave you checks or cares whether the field in the table is null. Open up Books Online (the SQL help files) and read up on ISNULL and you'll see what it does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2011 at 10:44 am
I See just had a look, Seems ideal but i think i may have the syntax wrong can you check for me? as you stated you should only have one set in the method which i have, but maybe i have the braket in the wrong place?
UPDATE wrp_Work
SET chrWorkDesc = ISNULL(@spDescription, chrWorkDesc,
chrWorkTitle, @spTitle,
intStatus, @spStatus,
intAssignTo, @spAssignUserID,
chrAssignToType, @spAssignUserType,
intModBy, @spUserID,
datModified, @CurrentDate,
chrJustification, @spJustification,
chrUserRef, @spUserRef,
chrEstimateHigh, @spEstimateHigh,
chrEstimateLow, @spEstimateLow,
chrActualWork, @spActualWork,
intCategoryID, @spCategoryID,
intPriority, @spPriorityID,
datRequired, convert(datetime,@spRequiredBy, 103),
intRaisedFor, @spUserID,
intBusinessArea, @spBusinessAreaID,
intSignedOffByID, @spintSignedOffByID,
intTableChange, @spTableChange,
intRamping, @spRamping,
intPapyrus, @spPapyrus,
intWeb, @spWeb,
intDataWarehouse, @spDataWarehouse,
intDeptPriority, @spDeptPriority,
intITValLvEst, @spITLevelEstimate,
intDevelopmentLvEst, @spDevelopmentLevelEstimate,
chrAldonTaskNum, @spAldonTaskNumber,
intBALvEst, @spBATestEstimate,
intBAUserTestEst, @spBAUserTestEstimate,
intReleaseNum, @spReleaseNumber)
WHERE intWorkID = @spWorkID
It says incorrect syntax near chrWorkTitle :-/
September 17, 2011 at 10:54 am
You're missing a close bracket. Look at your code, look at mine (just that one line)
There's also a stray bracket just before the where that would throw a syntax error if SQL got that far, not to mention there are commas where there need to be equals all the way down.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2011 at 11:28 am
Yeah i know i put that there because i thought ISNULL( your conditions go here) must of read some information wrong 🙁
September 17, 2011 at 11:35 am
I'm going to be blunt. Stop guessing.
Syntax of an Update: http://msdn.microsoft.com/en-us/library/ms177523.aspx
Syntax of ISNULL: http://msdn.microsoft.com/en-us/library/ms184325.aspx
Being new is all well and good, but the docs are available and detailed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply