i would like to request help with an update procedure. I want to update either the CPF field only or the Surname field only, currently, i can only update both together. Is there any way to do this using CASE ISNULL? Or even if you have some other way that is more clean code. I tried multiple ways but i don't do it
CREATE PROCEDURE sp_altera_pessoa (@Nome VARCHAR(15), @Sobrenome VARCHAR(15) = NULL, @CPF CHAR(11) = NULL)
AS
BEGIN TRY
BEGIN TRANSACTION
DECLARE @Command NVARCHAR(MAX) = ''
SET @Command = '
UPDATE Pessoa
SET '
\+ CASE ISNULL(@Sobrenome, '')
WHEN '' THEN ''
ELSE 'Sobrenome_p = @Sobrenome'
END
\+
'
WHERE Nome_p = @Nome
UPDATE Pessoa
SET '
\+ CASE ISNULL(@CPF, '')
WHEN '' THEN ''
ELSE 'CPF = @CPF'
END
\+
'
WHERE Nome_p = @Nome
'
PRINT @Command
EXEC sp_executesql
@Command,
N'@Nome VARCHAR(15), @Sobrenome VARCHAR(15) = NULL, @CPF CHAR(11) = NULL',
@Nome, @Sobrenome, @CPF
IF @@ERROR = 0
COMMIT
END
END CATCH
To do one or the other I would do it like this
IF @Sobrenome IS NOT NULL
BEGIN
UPDATE Pessoa
SET Sobrenome_p = @Sobrenome
WHERE Nome_p = @Nome
END
IF @CPF IS NOT NULL
BEGIN
UPDATE Pessoa
SET Nome_p = @CPF
WHERE Nome_p = @Nome
END
NOTE: If this data is coming from a web site and might be vulnerable to injection attack do this:
IF @Sobrenome IS NOT NULL
BEGIN
EXEC sp_executesql 'UPDATE Pessoa
SET Sobrenome_p = @Sobrenome
WHERE Nome_p = @Nome',
N'@Nome VARCHAR(15), @Sobrenome VARCHAR(15)', @Nome, @Sobrenome
END
Also both the try catch and the transaction are not used correctly -- in a try catch you need this format:
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
For the transaction you need a rollback if you don't commit