• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

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,PATINDEX('%1ST FLOOR%',[Something])-1) ELSE [Something] END
      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,PATINDEX('%1ST FLOOR%',[Something])-1) ELSE [Something] END
      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
0
rwheeler23
Asked:
rwheeler23
  • 15
  • 9
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>      WHERE [Something] LIKE '%1ST FLOOR%'
>      WHERE [Something] LIKE '%IST FLOOR%'

For starters..
  • The above query would throw a compile error with two WHERE statments
  • Is the second one the letter I (eye) ST FLOOR?

>  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.
0
 
Scott PletcherSenior DBACommented:
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,PATINDEX('%1ST FLOOR%',[Something])-1) ELSE [Something] END
      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
0
 
rwheeler23Author Commented:
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?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Scott PletcherSenior DBACommented:
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].
0
 
rwheeler23Author Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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],PATINDEX('%1ST FLOOR%', mt.[Something]), 9, '') END
) AS chg01
CROSS APPLY (
    SELECT [Something] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[Something]) = 0 THEN chg01.[Something]
        ELSE STUFF(chg01.[Something],PATINDEX('%IST FLOOR%', chg01.[Something]), 9, '') END
) AS chg02
CROSS APPLY (
    SELECT [Something] = CASE WHEN PATINDEX('%AVENUE%', chg02.[Something]) = 0 THEN [Something]
        ELSE STUFF(chg02.[Something],PATINDEX('%AVENUE%', chg02.[Something]), 6, 'AVE') END
) AS chg03
--...
CROSS APPLY (
    SELECT [Something] = chg04.Something
) AS chg30
0
 
rwheeler23Author Commented:
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.
0
 
rwheeler23Author Commented:
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.
0
 
rwheeler23Author Commented:
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_LOCATION],PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]), 9, '') END
 ) AS chg01
 CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[MOD_ESRI_LOCATION]) = 0 THEN chg01.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg01.[MOD_ESRI_LOCATION],PATINDEX('%IST FLOOR%', chg01.[MOD_ESRI_LOCATION]), 9, '') END
 ) AS chg02
 CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FIRST FLOOR%', chg02.[MOD_ESRI_LOCATION]) = 0 THEN chg02.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg02.[MOD_ESRI_LOCATION],PATINDEX('%FIRST FLOOR%', chg02.[MOD_ESRI_LOCATION]), 11, '') END
 ) AS chg03
 CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2ND FLOOR%', chg03.[MOD_ESRI_LOCATION]) = 0 THEN chg03.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg03.[MOD_ESRI_LOCATION],PATINDEX('%2ND FLOOR%', chg03.[MOD_ESRI_LOCATION]), 8, '') END
 ) AS chg04
  CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%SECOND FLOOR%', chg04.[MOD_ESRI_LOCATION]) = 0 THEN chg04.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg04.[MOD_ESRI_LOCATION],PATINDEX('%SECOND FLOOR%', chg04.[MOD_ESRI_LOCATION]), 12, '') END
 ) AS chg05
   CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%TOP FLOOR%', chg05.[MOD_ESRI_LOCATION]) = 0 THEN chg05.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg05.[MOD_ESRI_LOCATION],PATINDEX('%TOP FLOOR%', chg05.[MOD_ESRI_LOCATION]), 9, '') END
 ) AS chg30
0
 
Scott PletcherSenior DBACommented:
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_LOCATION],PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]), 9, '') END AS MOD_ESRI_LOCATION
 ) 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.
0
 
rwheeler23Author Commented:
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.
0
 
Scott PletcherSenior DBACommented:
You're welcome.  I really hope it helps, 30-45 UPDATEs will be huge overhead, even if you get them all to work correctly.
0
 
rwheeler23Author Commented:
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.
0
 
Scott PletcherSenior DBACommented:
Btw, I used "AVE" because that's the USPS's official abbreviation for "Avenue" (not "AV", just fyi).
0
 
rwheeler23Author Commented:
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!
0
 
Scott PletcherSenior DBACommented:
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.
0
 
rwheeler23Author Commented:
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_LOCATION],PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]), 9, '') END AS MOD_ESRI_LOCATION
 ) AS chg01
0
 
rwheeler23Author Commented:
The alias is OK in a Select statement but not in an Update statement.
0
 
rwheeler23Author Commented:
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_LOCATION],PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]), 9, '') END
 ) AS chg01
 CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%IST FLOOR%', chg01.[MOD_ESRI_LOCATION]) = 0 THEN chg01.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg01.[MOD_ESRI_LOCATION],PATINDEX('%IST FLOOR%', chg01.[MOD_ESRI_LOCATION]), 9, '') END
 ) AS chg02
 CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FIRST FLOOR%', chg02.[MOD_ESRI_LOCATION]) = 0 THEN chg02.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg02.[MOD_ESRI_LOCATION],PATINDEX('%FIRST FLOOR%', chg02.[MOD_ESRI_LOCATION]), 11, '') END
 ) AS chg03
 CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2ND FLOOR%', chg03.[MOD_ESRI_LOCATION]) = 0 THEN chg03.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg03.[MOD_ESRI_LOCATION],PATINDEX('%2ND FLOOR%', chg03.[MOD_ESRI_LOCATION]), 8, '') END
 ) AS chg04
  CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%SECOND FLOOR%', chg04.[MOD_ESRI_LOCATION]) = 0 THEN chg04.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg04.[MOD_ESRI_LOCATION],PATINDEX('%SECOND FLOOR%', chg04.[MOD_ESRI_LOCATION]), 12, '') END
 ) AS chg05
   CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%TOP FLOOR%', chg05.[MOD_ESRI_LOCATION]) = 0 THEN chg05.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg05.[MOD_ESRI_LOCATION],PATINDEX('%TOP FLOOR%', chg05.[MOD_ESRI_LOCATION]), 9, '') END
 ) AS chg06
    CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 1%', chg06.[MOD_ESRI_LOCATION]) = 0 THEN chg06.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg06.[MOD_ESRI_LOCATION],PATINDEX('%FLOOR 1%', chg06.[MOD_ESRI_LOCATION]), 7, '') END
 ) AS chg07
     CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 2%', chg07.[MOD_ESRI_LOCATION]) = 0 THEN chg07.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg07.[MOD_ESRI_LOCATION],PATINDEX('%FLOOR 2%', chg07.[MOD_ESRI_LOCATION]), 7, '') END
 ) AS chg08
      CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR%', chg08.[MOD_ESRI_LOCATION]) = 0 THEN chg08.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg08.[MOD_ESRI_LOCATION],PATINDEX('%FLOOR%', chg08.[MOD_ESRI_LOCATION]), 5, '') END
 ) AS chg09
       CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1A%', chg09.[MOD_ESRI_LOCATION]) = 0 THEN chg09.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg09.[MOD_ESRI_LOCATION],PATINDEX('%1A%', chg09.[MOD_ESRI_LOCATION]), 2, '') END
 ) AS chg10
        CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1B%', chg10.[MOD_ESRI_LOCATION]) = 0 THEN chg10.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg10.[MOD_ESRI_LOCATION],PATINDEX('%1B%', chg10.[MOD_ESRI_LOCATION]), 2, '') END
 ) AS chg11
         CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1FL%', chg11.[MOD_ESRI_LOCATION]) = 0 THEN chg11.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg11.[MOD_ESRI_LOCATION],PATINDEX('%1FL%', chg11.[MOD_ESRI_LOCATION]), 3, '') END
 ) AS chg12
          CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%1 FL%', chg12.[MOD_ESRI_LOCATION]) = 0 THEN chg12.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg12.[MOD_ESRI_LOCATION],PATINDEX('%1 FL%', chg12.[MOD_ESRI_LOCATION]), 4, '') END
 ) AS chg13
           CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2F%', chg13.[MOD_ESRI_LOCATION]) = 0 THEN chg13.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg13.[MOD_ESRI_LOCATION],PATINDEX('%2F%', chg13.[MOD_ESRI_LOCATION]), 2, '') END
 ) AS chg14
            CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2FL%', chg14.[MOD_ESRI_LOCATION]) = 0 THEN chg14.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg14.[MOD_ESRI_LOCATION],PATINDEX('%2FL%', chg14.[MOD_ESRI_LOCATION]), 3, '') END
 ) AS chg15
             CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%2 FL%', chg15.[MOD_ESRI_LOCATION]) = 0 THEN chg15.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg15.[MOD_ESRI_LOCATION],PATINDEX('%2 FL%', chg15.[MOD_ESRI_LOCATION]), 4, '') END
 ) AS chg30
0
 
Scott PletcherSenior DBACommented:
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],PATINDEX('%1ST FLOOR%', mt.[Something]), 9, '') END
) AS chg01

I'd need to see the exact error to know what's going on.
0
 
rwheeler23Author Commented:
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_LOCATION],PATINDEX('%1ST FLOOR%', mt.[MOD_ESRI_LOCATION]), 9, '') END AS MOD_ESRI_LOCATION
  ) AS chg01

Here is what I currently have:

CREATE PROCEDURE [dbo].[apd_UpdateAddressesFL02]

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_LOCATION],PATINDEX('%FLOOR 1%', mt.[MOD_ESRI_LOCATION]), 9, '') END AS MOD_ESRI_LOCATION
 ) AS chg01
 CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR 2%', chg01.[MOD_ESRI_LOCATION]) = 0 THEN chg01.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg01.[MOD_ESRI_LOCATION],PATINDEX('%FLOOR 2%', chg01.[MOD_ESRI_LOCATION]), 9, '') END AS MOD_ESRI_LOCATION
 ) AS chg02
 CROSS APPLY (
     SELECT [MOD_ESRI_LOCATION] = CASE WHEN PATINDEX('%FLOOR%', chg02.[MOD_ESRI_LOCATION]) = 0 THEN chg02.[MOD_ESRI_LOCATION]
         ELSE STUFF(chg02.[MOD_ESRI_LOCATION],PATINDEX('%FLOOR%', chg02.[MOD_ESRI_LOCATION]), 11, '') END AS MOD_ESRI_LOCATION
 ) 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
0
 
Scott PletcherSenior DBACommented:
I messed that up.  When you use the [name] = syntax, you can't have the "AS ".  The bolded part assigns the name instead.

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_LOCATION],PATINDEX('%FLOOR 1%', mt.[MOD_ESRI_LOCATION]), 9, '') END
 ) AS chg01

Or we can do:

CROSS APPLY (
     SELECT CASE WHEN PATINDEX('%FLOOR 1%', mt.[MOD_ESRI_LOCATION]) = 0 THEN mt.[MOD_ESRI_LOCATION]
         ELSE STUFF(mt.[MOD_ESRI_LOCATION],PATINDEX('%FLOOR 1%', mt.[MOD_ESRI_LOCATION]), 9, '') END AS [MOD_ESRI_LOCATION]
 ) AS chg01

We just can't combine them, and use [name] = along with AS [name], that's what's not allowed.
0
 
rwheeler23Author Commented:
Ok, I soon as I wrap up this morning's project I will get back on this. Thanks again.
0
 
rwheeler23Author Commented:
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.
0
 
rwheeler23Author Commented:
This is a purely genius solution. Thank you so very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 15
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now