sam.dahl (8/26/2012)
Firstly, "I have two stored procedures" suggested to me that the scripts we were executing were the actual stored procedures not necessarily the create scripts depicted.This may well be a terminology deficiency on my part, I resorted to flipping a coin as to which way I took the question.
I agree that this is not entirely linguistically correct, but I tend to disregard linguistic errors if the intent is obvious. In this case, after reading the actual question ("If I execute both scripts seperately") and the answer options, the intent of the question was obvious.
Secondly, I would have been wrong in any case because I tested this in SQL Server 2012 (since no version was specified I considered this valid):
Both create scripts execute successfully and both stored procedure scripts fail when actually executed (obviously).
After reading this, I tested it for myself on my SQL 2012. I ensured no table named table1 or table2 exists, then executed this batch:
create table table2 (col1 int);
go
create proc sp_test1
as
begin
select col1, col2
from table1
end
go
create proc sp_test2
as
begin
select col1, col2
from table2
end
go
The result was as I expected - sp_test1 was created and the attempt to create sp_test2 failed with the "invalid column name" error message.
Are you sure you didn't overlook something when testing this on your database?
Finally, a remark for everyone - prefixing names of stored procedures with sp_ is a very bad habit. This prefix is reserved for system stored procedures; the name resolution works slightly differently for these proces. This makes them a bit slower. More important - if Microsoft decides to include a stored proc with the same name in the next release (or even service pack!), your code will stop working, because you will now invoke the Microsoft version of the stored proc!
If you really must prefix your stored procedures (a habit which I personally find annoying and pointless), then use a different prefix. I believe usp_ is quite common. (But better is not to prefix stored procedures at all.)