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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With