Does SQL Server validate a saved process before starting?

Question

I have checked a lot of threads now, and I can’t find the answer for this, and I need to be sure that I accept this correctly before replying to the client.

since the header indicates whether SQL Server checks the stored procedure before starting it?

IE: Even if I have an IF statement that never satisfies a certain condition, will the code in this IF statement condition be checked and checked before running?

EDIT: Here is a quick example:

 DECLARE @ParamSource VARCHAR(2) = 'V3' IF @ParamSource = 'V1' BEGIN --USE LINKED SERVER HERE WHICH THROWS AN ERROR ABOUT CONNECTIONS END IF @ParamSource = 'V3' BEGIN --DO MY ACTUAL CODE END 

I will never meet this first condition, but for some reason my saved proc tries to check at runtime and continues to fail.

Answer 1

When a stored procedure is created, it is compiled, which means that every object used in the stored procedure is checked. For all existing objects, you also need access to them. This will create an execution plan for this stored procedure, and until the procedure changes, the execution plan must remain valid. If any table object used in the stored procedure does not exist (only tables, not linked servers), an execution plan will not be created at this point, but the procedure will be created if no other errors are detected.

In your example, you need access to a related server object to create a stored procedure. After creation, if you no longer have access to the linked server, your procedure will still be executed, but it will generate an error if it needs to access the linked IF @ParamSource = 'V1' server. However, if it does not get to the linked IF @ParamSource = 'V3' server IF @ParamSource = 'V3' , there will be no error.

This basically means that the user creating this procedure must have access to the linked server.