Writing a condition in a stored procedure

Question

Post reply

Writing a condition in a stored procedure

Jackson89

Ten Centuries

Points: 1141
More actions
February 2, 2016 at 4:10 pm

#315612

Hi All,
I have two tables,
1. Lookuptable
2.ErrorTable
Lookup table and ErrorTable has a column named (validkey) . It is a varchar.
I am updating the error table with correct data from lookup table and that update is working fine.
But i need to write one condition before the update and the condition is
if (the validkey in error table is present in the lookup table)
then
update the record
else
exit
Please help me how to write that condition .
Thanks in advance !!!

Viewing 5 posts - 1 through 4 (of 4 total)

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

pietlinden SSC Guru Points: 63483 More actions · Answer 1

if (the validkey in error table is present in the lookup table)

then

update the record

else

exit

UPDATE Table1

SET Field = <something>

WHERE Table1.Field1 = Table2.JoinField

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

This is a CLASSIC data processing problem known as an UPSERT. There are a BUNCH of issues with it related to concurrency. Almost every client I have seen this type of code at has messed it up - because it is very difficult to get right and still allow for a lot of concurrent activity without getting bad data.

If you do a web search for the words sql server upsert concurrency you will find several relevant articles, including bugs with the supposed fix MERGE.

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

Jackson89 Ten Centuries Points: 1141 More actions · Answer 3

Hi pietlinden, SQLGuru

Thanks for your advise,

below is the query which i use for updating the table

declare cGetSicErrorData cursor for

select distinct PolicyNine, ErrorSicFour, ErrorSicFifth

from #tmpSic02

open cGetSicErrorData

fetch cGetSicErrorData into @PolicyNine,@SicFour,@SicFifth

--Start

while @@FETCH_STATUS = 0

begin

Select @CorrectSicFifth = NULL

select top 1 @CorrectSicFifth=SicCodeFifth

from SicCodeLib

where SicCodeFour = @SicFour

order by SicCodeFifth

/*

select top 1 @CorrectSicFifth=substring(FifthSic,5,1)

from FifthSicCode

where SUBSTRING(FifthSic,1,4) = @SicFour

*/

select * from PESCodingDataOut

if(@CorrectSicFifth is not NULL)

begin

update PESCodingDataOut

set CodingData = SUBSTRING(CodingData,1,61)+@CorrectSicFifth+SUBSTRING(CodingData,63,26)

where SUBSTRING(CodingData,11,9) = @PolicyNine and

SUBSTRING(CodingData,26,4) = @SicFour and

SUBSTRING(CodingData,1,1) = 'A'

end

fetch cGetSicErrorData into @PolicyNine,@SicFour,@SicFifth

end

Here you can see at the start im fetching

@PolicyNine,@SicFour,@SicFifth from the cursor,

Right next to that statement i want the condition to be checked, only if it is true all the highlighted

statements should execute below, if it fails it should directly go to next element in the cursor.

This is what im trying to achieve, please tell me how can i achieve this

Thanks in advance !!!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

You can read the top posts when you search for that string of words I gave you. You are doing one of the two usual ways of doing this, and you have absolutely nothing in place to prevent the bad data that will come eventually from concurrency problems (unless nothing else EVER touches the same data at the same time anyway). Remember - when you ask for data with a SELECT a shared lock is taken but then RELEASED (unless you do something about it) - and in the short interval between that statement and the conditional and data modification something ELSE can mess with the data too. This is the FUNDAMENTAL problem with UPSERTs.

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