Multiple updates within one stored procedure. Only some are getting committed.
I have created this stored procedure which is nothing more than a crude attempt at standardizing addresses. It contains about 30 updates that corrects the string value of the addresses. The problem I have is that it just does some of them. It is always skips the same ones. When I had SET NOCOUNT OFF it would display the correct record count per update. It always comes back and says successfully completed. I wrapped each update in a BEGIN TRAN .. END TRAN. If I run the update individually it correctly updates all of them. What must I do to get it commit each of the updates?
CREATE PROCEDURE [dbo].[apd_UpdateAddresses
AS
BEGIN
SET NOCOUNT ON
DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SET @TranCount = @@TRANCOUNT
IF @TranCount = 0
BEGIN TRAN UpdateAddresses
ELSE
SAVE TRAN UpdateAddresses
BEGIN TRY
BEGIN TRAN
-- 1ST FLOOR
UPDATE MyTable
SET [Something] = CASE WHEN PATINDEX('%1ST FLOOR%',[Something]) > 1 THEN SUBSTRING([Something],1,PA
WHERE [Something] LIKE '%1ST FLOOR%'
COMMIT TRAN
-- IST FLOOR
BEGIN TRAN
UPDATE MyTable]
SET [Something] = CASE WHEN PATINDEX('%1ST FLOOR%',[Something]) > 1 THEN SUBSTRING([Something],1,PA
WHERE [Something] LIKE '%1ST FLOOR%'
WHERE [Something] LIKE '%IST FLOOR%'
COMMIT TRAN
.
. 30 more such updates
.
END TRY
BEGIN CATCH
ROLLBACK TRAN UpdateAddresses
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
-- Complete the transaction
COMMIT TRAN UpdateAddresses
RETURN 0 -- No errors
SET ANSI_NULLS ON
END
GO
CREATE PROCEDURE [dbo].[apd_UpdateAddresses
AS
BEGIN
SET NOCOUNT ON
DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SET @TranCount = @@TRANCOUNT
IF @TranCount = 0
BEGIN TRAN UpdateAddresses
ELSE
SAVE TRAN UpdateAddresses
BEGIN TRY
BEGIN TRAN
-- 1ST FLOOR
UPDATE MyTable
SET [Something] = CASE WHEN PATINDEX('%1ST FLOOR%',[Something]) > 1 THEN SUBSTRING([Something],1,PA
WHERE [Something] LIKE '%1ST FLOOR%'
COMMIT TRAN
-- IST FLOOR
BEGIN TRAN
UPDATE MyTable]
SET [Something] = CASE WHEN PATINDEX('%1ST FLOOR%',[Something]) > 1 THEN SUBSTRING([Something],1,PA
WHERE [Something] LIKE '%1ST FLOOR%'
WHERE [Something] LIKE '%IST FLOOR%'
COMMIT TRAN
.
. 30 more such updates
.
END TRY
BEGIN CATCH
ROLLBACK TRAN UpdateAddresses
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
-- Complete the transaction
COMMIT TRAN UpdateAddresses
RETURN 0 -- No errors
SET ANSI_NULLS ON
END
GO
If you want to commit some UPDATEs when others get an error, you need to remove the ROLLBACK TRAN from your CATCH block.
If you want to do that, then just log the error(s) somewhere and otherwise ignore them. If there's no tran, start a new one, otherwise just do the UPDATEs. If a tran is already active, and you issue a COMMIT TRAN, you are committing the original tran [there's no such as truly imbedded trans in SQL Server, just pseudo-inner trans]. Ultimately the original/outermost tran must be COMMITed or ROLLBACKed.
Ergo, something like this:
...
IF @TranCount = 0
BEGIN TRANSACTION
/* NOTE: NO "TRAN" statements. */
BEGIN TRY
-- 1ST FLOOR
UPDATE MyTable
SET [Something] = CASE WHEN PATINDEX('%1ST FLOOR%',[Something]) > 1 THEN SUBSTRING([Something],1,PA
WHERE [Something] LIKE '%1ST FLOOR%'
... 30 more such updates ....
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
PRINT 'Err = ' + @ErrMsg + '; ErrSev = ' + CAST(@ErrSeverity AS varchar(10))
END CATCH
IF @TranCount = 0
COMMIT TRANSACTION
RETURN 0 -- No errors
If you want to do that, then just log the error(s) somewhere and otherwise ignore them. If there's no tran, start a new one, otherwise just do the UPDATEs. If a tran is already active, and you issue a COMMIT TRAN, you are committing the original tran [there's no such as truly imbedded trans in SQL Server, just pseudo-inner trans]. Ultimately the original/outermost tran must be COMMITed or ROLLBACKed.
Ergo, something like this:
...
IF @TranCount = 0
BEGIN TRANSACTION
/* NOTE: NO "TRAN" statements. */
BEGIN TRY
-- 1ST FLOOR
UPDATE MyTable
SET [Something] = CASE WHEN PATINDEX('%1ST FLOOR%',[Something]) > 1 THEN SUBSTRING([Something],1,PA
WHERE [Something] LIKE '%1ST FLOOR%'
... 30 more such updates ....
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
PRINT 'Err = ' + @ErrMsg + '; ErrSev = ' + CAST(@ErrSeverity AS varchar(10))
END CATCH
IF @TranCount = 0
COMMIT TRANSACTION
RETURN 0 -- No errors
ASKER
Thanks to your help I have this working but I just stumbled across something. I was wondering why some records were not being updated and I believe the reason is conflicting update statements. Here is an example:
64 New Way Avenue Suite 203
Avenue needs to be updated to AV and Suite 203 goes away completely.
The is one update for the Avenue and another that strips away the Suite 203
The records that remain are ones that would be hit by multiple updates. So given that there are 30 update statements in this script is it possible to issue the update and then a commit so as it proceeds to the next update the previous update will be totally committed to the database?
64 New Way Avenue Suite 203
Avenue needs to be updated to AV and Suite 203 goes away completely.
The is one update for the Avenue and another that strips away the Suite 203
The records that remain are ones that would be hit by multiple updates. So given that there are 30 update statements in this script is it possible to issue the update and then a commit so as it proceeds to the next update the previous update will be totally committed to the database?
You really ought to consolidate the 30 UPDATE statements down to fewer statements. CASE statements should allow that to be done reasonably easily.
You don't need to COMMIT within the same task, it will always see its own UPDATEs. Yes, you can commit, you are allowed to do it, but it will commit the outermost transaction, even if that trans was already active when your proc started [there is only one "real", true active transaction in SQL Server, the outermost one, all the "sub-transactions" are pseudo-transactions only].
You don't need to COMMIT within the same task, it will always see its own UPDATEs. Yes, you can commit, you are allowed to do it, but it will commit the outermost transaction, even if that trans was already active when your proc started [there is only one "real", true active transaction in SQL Server, the outermost one, all the "sub-transactions" are pseudo-transactions only].
ASKER
Using this example how would a case statement make a difference?
I am trying to update the same field once to change AVENUE to AV and the again to remove Suite 203. Will using case apply both changes as one transaction. Great tip on the active transaction.
I am trying to update the same field once to change AVENUE to AV and the again to remove Suite 203. Will using case apply both changes as one transaction. Great tip on the active transaction.
Have no time now, but something like this;
UPDATE mt
SET [Something] = chg30.Something
FROM dbo.MyTable mt
CROSS APPLY (
SELECT [Something] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[Something]) = 0 THEN mt.[Something]
ELSE STUFF(mt.[Something],PATIN
) AS chg01
CROSS APPLY (
SELECT [Something] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[Something]) = 0 THEN chg01.[Something]
ELSE STUFF(chg01.[Something],PA
) AS chg02
CROSS APPLY (
SELECT [Something] = CASE WHEN PATINDEX('%AVENUE%', chg02.[Something]) = 0 THEN [Something]
ELSE STUFF(chg02.[Something],PA
) AS chg03
--...
CROSS APPLY (
SELECT [Something] = chg04.Something
) AS chg30
UPDATE mt
SET [Something] = chg30.Something
FROM dbo.MyTable mt
CROSS APPLY (
SELECT [Something] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[Something]) = 0 THEN mt.[Something]
ELSE STUFF(mt.[Something],PATIN
) AS chg01
CROSS APPLY (
SELECT [Something] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[Something]) = 0 THEN chg01.[Something]
ELSE STUFF(chg01.[Something],PA
) AS chg02
CROSS APPLY (
SELECT [Something] = CASE WHEN PATINDEX('%AVENUE%', chg02.[Something]) = 0 THEN [Something]
ELSE STUFF(chg02.[Something],PA
) AS chg03
--...
CROSS APPLY (
SELECT [Something] = chg04.Something
) AS chg30
ASKER
Wow, this is brilliant. You are taking the original string and then looking for every potential bad string and replacing it with blanks and then ultimately setting the entire string to the updated value. I will be setting this up first thing in the morning and will provide an update.
ASKER
Well this work however it crashed with all the changes so I broke it down into smaller and smaller chunks. If I try to do more than 5 I get this message:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to
simplify them.
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to
simplify them.
ASKER
Here is what will work. If I add one more cross apply it will fail. I do not see where I exceed 65,535 identifiers and constants. There are 250,00+ records in the table.
UPDATE mt
SET [MOD_ESRI_LOCATION] = chg30.[MOD_ESRI_LOCATION]
FROM [dbo].[cases] mt
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[MOD_ESRI_LOCATION])
ELSE STUFF(chg01.[MOD_ESRI_LOCA
) AS chg02
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FIRST FLOOR%', chg02.[MOD_ESRI_LOCATION])
ELSE STUFF(chg02.[MOD_ESRI_LOCA
) AS chg03
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2ND FLOOR%', chg03.[MOD_ESRI_LOCATION])
ELSE STUFF(chg03.[MOD_ESRI_LOCA
) AS chg04
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%SECOND FLOOR%', chg04.[MOD_ESRI_LOCATION])
ELSE STUFF(chg04.[MOD_ESRI_LOCA
) AS chg05
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%TOP FLOOR%', chg05.[MOD_ESRI_LOCATION])
ELSE STUFF(chg05.[MOD_ESRI_LOCA
) AS chg30
UPDATE mt
SET [MOD_ESRI_LOCATION] = chg30.[MOD_ESRI_LOCATION]
FROM [dbo].[cases] mt
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[MOD_ESRI_LOCATION])
ELSE STUFF(chg01.[MOD_ESRI_LOCA
) AS chg02
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FIRST FLOOR%', chg02.[MOD_ESRI_LOCATION])
ELSE STUFF(chg02.[MOD_ESRI_LOCA
) AS chg03
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2ND FLOOR%', chg03.[MOD_ESRI_LOCATION])
ELSE STUFF(chg03.[MOD_ESRI_LOCA
) AS chg04
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%SECOND FLOOR%', chg04.[MOD_ESRI_LOCATION])
ELSE STUFF(chg04.[MOD_ESRI_LOCA
) AS chg05
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%TOP FLOOR%', chg05.[MOD_ESRI_LOCATION])
ELSE STUFF(chg05.[MOD_ESRI_LOCA
) AS chg30
Make sure you name the expression in the CROSS APPLY (CA):
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
That's an interesting issue. I've used this technique for way more than 30 CAs and it's worked fine. If you keep having the problem, you may have to do only 5 or 6 changes per UPDATE, but that will still drastically reduce the number of separate UPDATEs.
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
That's an interesting issue. I've used this technique for way more than 30 CAs and it's worked fine. If you keep having the problem, you may have to do only 5 or 6 changes per UPDATE, but that will still drastically reduce the number of separate UPDATEs.
ASKER
I bet naming the expression will fix this issue. If not, I would rather have 6 or 7 update scripts versus 45. I can most likely group them based on the string to remove. Thanks again for your tremendous input on this. I will give this a try later today.
You're welcome. I really hope it helps, 30-45 UPDATEs will be huge overhead, even if you get them all to work correctly.
ASKER
Well it would help if people knew how to spell not to mention I had no idea how different variations of AVE there were. I tried to find an address correction program but they all want the data sent to them. Once I get this working it will all be wrapped up in an SSIS job.
Btw, I used "AVE" because that's the USPS's official abbreviation for "Avenue" (not "AV", just fyi).
ASKER
That make me laugh. This project is for a local law enforcement organization and they gave me a table full of standard abbreviations they supposedly use. As to be expected, the actual abbreviation they use, not to mention the misspellings, are anything but standard. How can you spell AV wrong? They found a way!
Yeah, addresses and names are horrible to try to resolve. Google has (used to have?) an interface (API, I think) you could pass an address and they would clean it up. You might want to check into that too.
ASKER
It does not like the alias at the end, 'END AS MOD_ESRI_LOCATION' or any alias.
UPDATE mt
SET [MOD_ESRI_LOCATION] = chg30.[MOD_ESRI_LOCATION]
FROM [dbo].[cases] mt
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
UPDATE mt
SET [MOD_ESRI_LOCATION] = chg30.[MOD_ESRI_LOCATION]
FROM [dbo].[cases] mt
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
ASKER
The alias is OK in a Select statement but not in an Update statement.
ASKER
Here is the code that will fail. If I try to add an alias to any of the Cross Apply sections the syntax checker fails.
UPDATE mt
SET [MOD_ESRI_LOCATION] = chg30.[MOD_ESRI_LOCATION]
FROM [dbo].[cases] mt
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[MOD_ESRI_LOCATION])
ELSE STUFF(chg01.[MOD_ESRI_LOCA
) AS chg02
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FIRST FLOOR%', chg02.[MOD_ESRI_LOCATION])
ELSE STUFF(chg02.[MOD_ESRI_LOCA
) AS chg03
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2ND FLOOR%', chg03.[MOD_ESRI_LOCATION])
ELSE STUFF(chg03.[MOD_ESRI_LOCA
) AS chg04
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%SECOND FLOOR%', chg04.[MOD_ESRI_LOCATION])
ELSE STUFF(chg04.[MOD_ESRI_LOCA
) AS chg05
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%TOP FLOOR%', chg05.[MOD_ESRI_LOCATION])
ELSE STUFF(chg05.[MOD_ESRI_LOCA
) AS chg06
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 1%', chg06.[MOD_ESRI_LOCATION])
ELSE STUFF(chg06.[MOD_ESRI_LOCA
) AS chg07
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 2%', chg07.[MOD_ESRI_LOCATION])
ELSE STUFF(chg07.[MOD_ESRI_LOCA
) AS chg08
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR%', chg08.[MOD_ESRI_LOCATION])
ELSE STUFF(chg08.[MOD_ESRI_LOCA
) AS chg09
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1A%', chg09.[MOD_ESRI_LOCATION])
ELSE STUFF(chg09.[MOD_ESRI_LOCA
) AS chg10
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1B%', chg10.[MOD_ESRI_LOCATION])
ELSE STUFF(chg10.[MOD_ESRI_LOCA
) AS chg11
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1FL%', chg11.[MOD_ESRI_LOCATION])
ELSE STUFF(chg11.[MOD_ESRI_LOCA
) AS chg12
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1 FL%', chg12.[MOD_ESRI_LOCATION])
ELSE STUFF(chg12.[MOD_ESRI_LOCA
) AS chg13
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2F%', chg13.[MOD_ESRI_LOCATION])
ELSE STUFF(chg13.[MOD_ESRI_LOCA
) AS chg14
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2FL%', chg14.[MOD_ESRI_LOCATION])
ELSE STUFF(chg14.[MOD_ESRI_LOCA
) AS chg15
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2 FL%', chg15.[MOD_ESRI_LOCATION])
ELSE STUFF(chg15.[MOD_ESRI_LOCA
) AS chg30
UPDATE mt
SET [MOD_ESRI_LOCATION] = chg30.[MOD_ESRI_LOCATION]
FROM [dbo].[cases] mt
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[MOD_ESRI_LOCATION])
ELSE STUFF(chg01.[MOD_ESRI_LOCA
) AS chg02
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FIRST FLOOR%', chg02.[MOD_ESRI_LOCATION])
ELSE STUFF(chg02.[MOD_ESRI_LOCA
) AS chg03
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2ND FLOOR%', chg03.[MOD_ESRI_LOCATION])
ELSE STUFF(chg03.[MOD_ESRI_LOCA
) AS chg04
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%SECOND FLOOR%', chg04.[MOD_ESRI_LOCATION])
ELSE STUFF(chg04.[MOD_ESRI_LOCA
) AS chg05
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%TOP FLOOR%', chg05.[MOD_ESRI_LOCATION])
ELSE STUFF(chg05.[MOD_ESRI_LOCA
) AS chg06
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 1%', chg06.[MOD_ESRI_LOCATION])
ELSE STUFF(chg06.[MOD_ESRI_LOCA
) AS chg07
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 2%', chg07.[MOD_ESRI_LOCATION])
ELSE STUFF(chg07.[MOD_ESRI_LOCA
) AS chg08
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR%', chg08.[MOD_ESRI_LOCATION])
ELSE STUFF(chg08.[MOD_ESRI_LOCA
) AS chg09
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1A%', chg09.[MOD_ESRI_LOCATION])
ELSE STUFF(chg09.[MOD_ESRI_LOCA
) AS chg10
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1B%', chg10.[MOD_ESRI_LOCATION])
ELSE STUFF(chg10.[MOD_ESRI_LOCA
) AS chg11
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1FL%', chg11.[MOD_ESRI_LOCATION])
ELSE STUFF(chg11.[MOD_ESRI_LOCA
) AS chg12
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1 FL%', chg12.[MOD_ESRI_LOCATION])
ELSE STUFF(chg12.[MOD_ESRI_LOCA
) AS chg13
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2F%', chg13.[MOD_ESRI_LOCATION])
ELSE STUFF(chg13.[MOD_ESRI_LOCA
) AS chg14
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2FL%', chg14.[MOD_ESRI_LOCATION])
ELSE STUFF(chg14.[MOD_ESRI_LOCA
) AS chg15
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2 FL%', chg15.[MOD_ESRI_LOCATION])
ELSE STUFF(chg15.[MOD_ESRI_LOCA
) AS chg30
It accepted them in the style I did it (on SQL 2016, at least):
CROSS APPLY (
SELECT [Something] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[Something]) = 0 THEN mt.[Something]
ELSE STUFF(mt.[Something],PATIN
) AS chg01
I'd need to see the exact error to know what's going on.
CROSS APPLY (
SELECT [Something] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[Something]) = 0 THEN mt.[Something]
ELSE STUFF(mt.[Something],PATIN
) AS chg01
I'd need to see the exact error to know what's going on.
ASKER
As soon as I add the alias' the message from the parser is
'Msg 156, Level 15, State 1, Procedure apd_UpdateAddressesFL02, Line 48
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure apd_UpdateAddressesFL02, Line 52
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure apd_UpdateAddressesFL02, Line 56
Incorrect syntax near the keyword 'AS'.
Remember you had suggested this to not get this message.
'Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to
simplify them. '
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
Here is what I currently have:
CREATE PROCEDURE [dbo].[apd_UpdateAddresses
AS
BEGIN
SET NOCOUNT ON
DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SET @TranCount = @@TRANCOUNT
IF @TranCount = 0
BEGIN TRAN
BEGIN TRY
UPDATE mt
SET [MOD_ESRI_LOCATION] = chg99.[MOD_ESRI_LOCATION]
FROM [dbo].[cases] mt
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 1%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 2%', chg01.[MOD_ESRI_LOCATION])
ELSE STUFF(chg01.[MOD_ESRI_LOCA
) AS chg02
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR%', chg02.[MOD_ESRI_LOCATION])
ELSE STUFF(chg02.[MOD_ESRI_LOCA
) AS chg99
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
-- Complete the transaction
IF @TranCount = 0
COMMIT TRAN
RETURN 0 -- No errors
SET ANSI_NULLS ON
END
GO
'Msg 156, Level 15, State 1, Procedure apd_UpdateAddressesFL02, Line 48
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure apd_UpdateAddressesFL02, Line 52
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure apd_UpdateAddressesFL02, Line 56
Incorrect syntax near the keyword 'AS'.
Remember you had suggested this to not get this message.
'Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to
simplify them. '
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
Here is what I currently have:
CREATE PROCEDURE [dbo].[apd_UpdateAddresses
AS
BEGIN
SET NOCOUNT ON
DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SET @TranCount = @@TRANCOUNT
IF @TranCount = 0
BEGIN TRAN
BEGIN TRY
UPDATE mt
SET [MOD_ESRI_LOCATION] = chg99.[MOD_ESRI_LOCATION]
FROM [dbo].[cases] mt
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 1%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
ELSE STUFF(mt.[MOD_ESRI_LOCATIO
) AS chg01
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 2%', chg01.[MOD_ESRI_LOCATION])
ELSE STUFF(chg01.[MOD_ESRI_LOCA
) AS chg02
CROSS APPLY (
SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR%', chg02.[MOD_ESRI_LOCATION])
ELSE STUFF(chg02.[MOD_ESRI_LOCA
) AS chg99
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
-- Complete the transaction
IF @TranCount = 0
COMMIT TRAN
RETURN 0 -- No errors
SET ANSI_NULLS ON
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I soon as I wrap up this morning's project I will get back on this. Thanks again.
ASKER
OK, looks like we have a winner. I am going to wrap all of these up into one big script and let it ride. I will provide an update shortly.
ASKER
This is a purely genius solution. Thank you so very much.
> WHERE [Something] LIKE '%IST FLOOR%'
For starters..
> BEGIN TRAN
This should be named something so it is not confused with the TRAN named UpdateAddresses. Or is that it but the developer forgot the name? Otherwise there should be a ROLLBACK TRAN somewhere in the CATCH block error handling that I don't see.