How to update a field from a Database table using a Stored Procedure?

Asked

Viewed 626 times

0

I basically have a database table, which has a field called DESCRIÇÃO and the table is called Tabela123

I have 10 records in this table, and in the DESCRIPTION field I have the sentences: Hello, you are well? repeating for four times, goodbye and thank you four times and good morning twice.

I want to change these phrases using a stored Procedure.

In the first sentence I want to change to Example: Hello, you’re fine? all four records, in the second sentence I want to change to Hello and thank you in the four records.

I basically want to create a Stored Procedure that changes a part or word of the sentence in the description field.

CREATE PROCEDURE [dbo].[UpdateTable] @XKey INT, @XDescription NVARCHAR(MAX) AS BEGIN BEGIN TRANSACTION BEGIN try
SET nocount ON
UPDATE TABLE
SET Description = @VMDescription
WHERE ID = @VMKey
  COMMIT; END try BEGIN catch
ROLLBACK TRANSACTION; DECLARE @errorMessage nvarchar(4000); DECLARE @errorSeverity int; DECLARE @errorState int;
SELECT @errorMessage = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE(),
       @errorSeverity = ERROR_SEVERITY(),
       @errorState = ERROR_STATE(); RAISERROR(@errorMessage,@errorSeverity,@errorState); END catch END

As I have now I think it is wrong, since I have parameters and I think I will not want anything like this, I want to run the script and do what is on top.

sql database sql-server stored-procedures

  • update Tabela123 set Descricao = 'Olá, você está bem?' where Descricao = 'Olá, você está bem?'...

  • @Marconi edited the answer and I put as I had, try to add and change my code in a reply please, that goes against what I need, I’m half lost

  • In my view your procedure is correct.

1 answer

0


There are two situations, if you want to use the same procedure for this, you will need to have one more parameter and one condition inside, or, do two procedures:

1-Add text before current text:

CREATE PROCEDURE [dbo].[UpdateTable] @XKey INT, @XDescription NVARCHAR(MAX) 

AS BEGIN BEGIN TRANSACTION BEGIN try
SET nocount ON
UPDATE TABLE
SET Description = Concat('Por Exemplo: ',Description)
WHERE Description = 'Olá, você está bem?'
  COMMIT; END try BEGIN catch
ROLLBACK TRANSACTION; DECLARE @errorMessage nvarchar(4000); DECLARE @errorSeverity int; DECLARE @errorState int;
SELECT @errorMessage = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE(),
       @errorSeverity = ERROR_SEVERITY(),
       @errorState = ERROR_STATE(); RAISERROR(@errorMessage,@errorSeverity,@errorState); END catch END

2- Replace the current text:

    CREATE PROCEDURE [dbo].[UpdateTable] @XKey INT, @XDescription NVARCHAR(MAX) 

AS BEGIN BEGIN TRANSACTION BEGIN try
SET nocount ON
UPDATE TABLE
SET Description = Replace(Description, 'adeus', 'olá' )
WHERE Description = 'adeus e obrigado'
  COMMIT; END try BEGIN catch
ROLLBACK TRANSACTION; DECLARE @errorMessage nvarchar(4000); DECLARE @errorSeverity int; DECLARE @errorState int;
SELECT @errorMessage = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE(),
       @errorSeverity = ERROR_SEVERITY(),
       @errorState = ERROR_STATE(); RAISERROR(@errorMessage,@errorSeverity,@errorState); END catch END

Documentation:

https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql

I hope I’ve helped.

Browser other questions tagged sql database sql-server stored-procedures

You are not signed in. Login or sign up in order to post.