February 8, 2012 at 9:17 am
We have a large database with some 800+ stored procedures. Many have been tuned by making use of # tables. Recently we added a new column to one of our permanent tables and changed the stored procedure to update this new column from data in a temporary table created earlier in the stored procedure. During rollout, the new column was not added to the permanent table.
To my surprise the Stored Procedure created successfully and only "Errored" when the statement doing the update was executed.
On investigating I found that any update statement that references a temporary table does not get checked on creating.... and example is included below.
Is there any way to force creation to fail if references are made to missing / invalid columns or permanent tables OR is there a way to globally check all database stored procedures without executing them...
Here is an example (ignore the simplicity - obviously it isn't doing anything valid but it is just intended to demonstrate the problem.
-- First create a permanent table
CREATE TABLE
Test_TempTableProc
(KeyValue INT)
GO
---- This statement creates successfully despite the invalid column name
CREATE PROCEDURE Proc_TestTableProc
AS
BEGIN
SELECT 1 AS ColumnName
INTO #TempTable
UPDATE Test_TempTableProc
SET SomeColumn = 3
FROM #TempTable
END
--- This create table statement gives an error
CREATE PROCEDURE Proc_TestTableProc2
AS
BEGIN
SELECT 1 AS ColumnName
INTO #TempTable
UPDATE Test_TempTableProc
SET SomeColumn = 3
END
February 8, 2012 at 9:37 am
You might want to read:
http://msdn.microsoft.com/en-us/library/ms190686(v=sql.100).aspx
Which states:
When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the sys.sql_modules catalog view.
When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the sys.sql_modules catalog view and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because table objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.
February 8, 2012 at 10:38 am
craig-962225 (2/8/2012)
Is there any way to force creation to fail if references are made to missing / invalid columns or permanent tables OR is there a way to globally check all database stored procedures without executing them...
Logically, I don't think it could ever do that. In your example, the temp table is created in the statement before, but it could conceivably be created under a conditional branch that is never reached, or created outside of the procedure entirely and it can't know until runtime what condition it's going to find that temp table in.
February 8, 2012 at 11:12 am
I agree that any references to fields in the # table cannot be checked - my problem is with fields in the Permanent Table. The UPDATE statement is updating a permanent table and the SET field in the update statement MUST be on the Permanent table for it to work. However it doesn't seem to check this at all. I was just hoping there was some way to verify that all the Permanent table fields etc are valid.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply