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

Question

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

Navigation

#1 by (0 votes)

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, are you ok? 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, are you ok? All four records, in the second sentence I want to switch to Hello and thank you in the four logs.

I basically want to create a Stored Procedure that changes a part or a word of the phrase 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 that I will not want anything like this, I want to run the script and do what is on top.

sql sql-server stored-procedures

asked by anonymous 08.08.2017 / 13:00

1 answer

Error Compile C in Gcc - Accentuation Encrypt images and videos via node.js

score 0 · Accepted Answer

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

1- Add text before 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 = 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 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:

link

I hope I have helped.