Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does SQL Server Validate a stored proc before running it?

I have checked many threads now and I cant seem to find an answer for this, and I need to be fairly certain / confident that I am correct in assuming this before replying to a client.

so, as the heading states, Does SQL Server validate a stored procedure before running it?

IE: Even if i have an IF statement that will never meet a certain condition, will the code in that IF statement condition be checked and validated 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 that first condition, but for some reason, my stored proc is trying to validate on run time and keeps erroring.

like image 824
Mike Avatar asked Sep 17 '15 13:09

Mike


1 Answers

When a stored procedure is created, it is compiled, which means that each object used in a stored procedure is validated. For all existing objects you also need to have access to them. This will create an execution plan for this stored procedure, and as long as the procedure doesn't change, the execution plan should remain valid. If any table object used in the stored procedure does not exist (table only, not linked servers), the execution plan will not be created at this point, but the procedure will be created if no other errors are found.

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

Basically, it means that the user that creates the procedure needs to have access to the linked server.

like image 67
Philippe Avatar answered Nov 15 '22 09:11

Philippe