April 17, 2003 at 6:28 am
in SQL Server 2000 , I tried to compile a stored procedure which refers to a non existing table.
e.g.
Create Proc Usp_Try
as
begin
select * from Tbl_Non_exists
end
the table does not exists but the procedure gets created. and at run time it throws error. i want to stop this behaviour. i do not want any procedure to refer to any non-existing table. Is there any way to do it ??
Amit Jethva
April 17, 2003 at 9:14 am
Hi Amit,
I don't think you can. Object name resolution appears to be delayed until runtime by design. I think the only caveat to this is if an missing object referenced in the sp is another sp, you'll get a warning if the dependee <?> does not exist at compile time.
This is drastically different to Oracle where sp's won't compile if dependencies are missing or become invalid if the dependency existed before compile and removed after compile.
Oracle can be an old bitch in this respect. I have been bitten on the arse once or twice with sp's becoming invalid (and therefore needing a recompile) when a dependent object has been modified. I couldn't recompile the sp cause another related object was in use. Runtime Resolution aka MSSql would have alleviated this.
I can however see arguments for and against compile time resolution of dependencies, but runtime resolution requires stricter testing before deployment which is not such a bad thing.
Rich O'Connor
Rich O'Connor
April 17, 2003 at 9:20 am
This is how SQL works and is better off then Oracle :))
April 17, 2003 at 1:39 pm
that is strange because if you specify a column that does not exist on a table that does it will not compile
hmmmmmmm!
April 17, 2003 at 1:43 pm
This is true, but remember that stored procedure creation puts entries into the system tables. It does not compile the stored procedure.
I think SQL Server allows this in case you have interrelated objects, like two stored procedures that may have need to call each other. If you were to build a stored procedure that makes a call to a stored procedure that doesn't exist, you'll get a warning, but the first stored procedure is still created.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
April 17, 2003 at 4:15 pm
This seems to be different than it was with version 6.5. It took me a while to get used to in SQL 2000.
I personally prefer the compilation failure but haven't even looked to see if it is a configuration option. One drawback to having it check at compilation time is that you cannot create and use a non-temporary table that way.
I could probably argue for either side. Just one of those things you get used to, I guess.
Guarddata-
April 18, 2003 at 10:18 pm
Hi All,
thanks for all replies. as i have previously worked on Sybase and Oracle RDBMS, this question came to my mind. Personally, i prefer the error to be thrown at compile time only, not at run time. as "bilko73" said, if you specify a column that does not exist on a table that does it will not compile. so slightly strange rules aplied by microsoft for tables and columns.
you can refer to a nonexisting table but can not refer to a nonexisting column. !!!
Amit
April 18, 2003 at 10:46 pm
Gurus,
I've encountered the compilation error referring to a non-existent table in 6.5. I'm wondering if the exclusion of this error in SQL 7.x and later isn't related to a simplistic implementation of late-binding in SQL Server's DDL.
Specifically, if you created a DDL script in 6.5 that included a sproc, it was assumed that any referenced table/view/proc/etc... had already been created previous to the script creating the sproc. In 7.x (and later), the proc would still be created with the assumption that relevant objects would be created later. Is this the crux of the issue presented in this thread? i.e. More recent versions of SQL Server allow creation of a proc object without pre-existing dependent objects... assuming that dependent objects will exist at runtime? Could this 'feature' exist for the sole purpose of allowing a 'wizard' or database transfer process to succeed despite the lack of target objects?
Shrug,
SJTerrill
April 19, 2003 at 5:42 am
I suppose that might be it. Seems just as likely someone realized you might need a circular reference someday. Proc A calls Proc B. Proc B calls Proc A.
Then what about temp tables - would you parse the proc to make sure it gets created only within that proc, or do you allow that to make it by the object check?
Andy
April 20, 2003 at 10:16 pm
Hi,
The one of the basic advantages of a stored procedure is to have the execution plan pre-compiled, stored and available for execution on server. so that same set of commands can be executed again and again without having any need to recompile the plans at server side. now if the SQL Server has to resolve object names at execution-time, this advantage would be lost. (as a new table will involve Query optimizer to generate execution plan again).
i would consider temp tables to be a special case for exclusion from the existance check.
amit jethva
April 24, 2003 at 6:09 am
friend,
First check for the existance for the table by quering "sysobjects"
system table. if the values return is true then allow the select statement to execute. Else move out from the proc.
Siby Silvester
DBA- Sql server
Dept of RADAR Comm.
General Security Eqpt Est
Ritadh, Saudi Arabai
email: sibystar@yahoo.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply