CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Harsha
Harsha

Reputation: 1

How does the stored procedure creations works in SQL Server?

I am using SQL Server, I am confused whether we can write any random content inside the create statement or not.

For example I have a database school. Inside that I have only one table students. I am trying to create SP like the below without including the column name beside select and it is throwing error while creating the sp.

Create procedure Getstud
As
Begin
Select from Getstud
End

I am not facing error while creating the below sp. But facing error while executing the sp as teachers table is not present.

Create procedure Getstud
As
Begin
Select * from teachers
End

Can anyone please help me understand this, does SQL Server check whether the tables, views ..etc present in the SP are actually correct (I am asking about the time when we are running the create sp not the exec sp)

Upvotes: 0

Views: 79

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280479

Why do you think you can write a select without naming any columns? Can you write a select without naming any columns outside of a stored procedure? This is simply not valid T-SQL whether you are using a procedure or not and whether or not the table exists:

SELECT FROM dbo.TableName;
------^ you need columns here

As for why the second procedure gets created successfully: this is due to a thing called deferred name resolution. SQL Server is giving you the benefit of the doubt that you won't execute this procedure until the tables it references exist. For one, this allows you to create procedures before tables, or the opposite.

However, deferred name resolution does not give you the benefit of the doubt for columns that don't exist if the table already does exist. So you can say:

CREATE PROCEDURE dbo.blat
AS
  SELECT ColumnThatDoesNotExist FROM dbo.TableThatDoesNotExist;

But you cannot say:

CREATE PROCEDURE dbo.blormph
AS
  SELECT ColumnThatDoesNotExist FROM dbo.TableThatDOESExist;

Upvotes: 3

Related Questions