Create SP...without a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create SP...without a table

Author

Topic

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-18 : 12:38:23

Here's something I stumbled upon and I'm not sure why it works (SQL2005). If you run the following CREATE statement, it compiles and creates the SP. Of course when you run the SP you get an error because the table doesn't exist. I would have thought the SP wouldn't even be created since the table doesn't exist. I understand that the SCHEMA_BINDING option would probably make it not be able to be created, but it seems like in the past (SQL2000) I would get an error if the table didn't exist (without using a SCHEMA_BINDING option). Is there maybe a db option that isn't set?

create procedure test_sp
as
select * from some_table_that_does_not_exist

That creates the SP fine...of course the following gives an error.

exec test_sp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-18 : 12:41:05

which is expected. When you create the proc SQL Server only checks for syntax errors. It doesnt check if the objects being used actually exist or not. Only when you run/exec the proc it runs each of the T-SQL commands and then it complains of the missing table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-18 : 12:43:22

Just seems like in the past I would always get an error if the table didn't exist when compiling a sp...but maybe not.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 12:49:58

2k and 2k5 both behave the same on this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-18 : 13:50:23

Acrually, if the table exists, it will verify that the table columns are correct, but if they do not exist, it won't.

The following code will run fine in tempdb, but produce an error in the pubs database.


create proc  test_x
as
select x from authors
go
drop proc test_x

CODO ERGO SUM

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-18 : 15:02:03

That's what it was...the column name...not table. Now I remember.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources