What kind of checks should I perform while dealing with stored procedures? CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin

IF (SELECT is_activated from USERS where mat_emp = @mat_emp) = 1
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end
else
print 1
end


or

    CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end


Which one of these queries is considered better (from different aspects) and why? sometimes I feel like doing some check before making the update is not necessary, like in this case for example.

First one will check If the account is unlocked, If yes, it locks it.

While the second one will lock it regardless of the current state of the account.

So, for the user who is using the application that execute this procedure, does it really matter?
You should perform checks to guarantee code correctness, follow the coding standards of your organization, and to make your code understandable to other programmers. I will give you my initial thoughts on your code as an outsider who doesn't know anything about your application. Starting with this one:

CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin

IF (SELECT is_activated from USERS where mat_emp = @mat_emp) = 1
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end
else
print 1
end


What happens if the value of is_activated changes after your SELECT query is run but before your UPDATE query runs? The code pattern that you have here isn't safe under concurrency. It's also not clear what print 1 means here and if it's okay to mix that information with information about row counts (SET NOCOUNT ON isn't present). Perhaps you're looking for an output parameter?

For this code:

CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end


In most code around locking I see some kind of error handling or information returned to the caller if a lock can't be taken. For example, if you're taking a lock in order to perform some type of operation that requires exclusive access to the user then allowing many concurrent processes to take locks could cause data integrity issues. Does the caller need to know if a row exists for the @mat_emp input parameter? Should the stored procedure time out if it can't get a lock quickly enough on the USERS table?

It's true that updating a column in a row to the same value does have some subtle effects, but I think that's the wrong way to look at it except for extremely high performance applications. If you want to develop a habit I think that it's best to avoid non-updating updates (when practical) just to avoid surprises. However, the code (including comments) of the stored procedure needs to make the expected outcome of the operation clear. As someone without any knowledge of your application, I can't say if either approach is safe.