Stored procedure gives no compilation error but gives a run time error

Question

Stored procedure gives no compilation error but gives a run time error

vernalrd

SSC Veteran

Points: 262
More actions
May 16, 2010 at 1:12 pm

#146947

Consider this test procedure
-----------------------------------
/*exec usp_tester*/
/****** Object: StoredProcedure [dbo].[usp_tester] Script Date: 05/17/2010 00:21:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_tester]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @temp TABLE
(
airportid INT,
regionid INT
)
INSERT INTO @temp
SELECT
t1.airportid,
t1.regionid
FROM air_regio t1 /*here the actual table name is air_region but it did not give any compilation error*/
INNER JOIN dbo.region r ON r.regionid=ar.regionid
SELECT
ar.airportid,
ar.regionid
FROM dbo.vw_AirportRegion ar
INNER JOIN @temp T ON T.RegionID=ar.RegionID
END
--------------------------
The stored procedure compiles successfully but gave the following run time error
Msg 208, Level 16, State 1, Procedure usp_tester, Line 12
Invalid object name 'air_regio'.
My question is can this be detected at compile time.I am using sql server 2005.So when i generate a database upsize script with many stored procedures such a kind of problem goes undetected.I hope i was able to express the problem.Any help would be appreciated.I think it is something with the use of table variables.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

This is called deferred name resolution. If, while creating/altering a stored procedure, SQL encounters a table/view/procedure that does not exist, it skips over that name, assuming that the object will be created at the point that the object runs.

It's got nothing to do with table variables.

There's no way to disable this. I would venture that if you have stored procedures with this problem, they should be either fixed or dropped.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vernalrd SSC Veteran Points: 262 More actions · Answer 2

Does this apply for all versions of sql server.Would i face this problem for sql server 2000 as well.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vernalrd SSC Veteran Points: 262 More actions · Answer 4

vernalrd

SSC Veteran

Points: 262

May 16, 2010 at 3:19 pm

#1168063

Thanks a lot Gail.I really appreciate your help.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

btw, this is one of the reasons why, when doing a SQL 2000-2005 upgrade (which I assume is what you're doing), you should be doing end-to-end testing of the application, not just syntax checks in SQL or upgrade advisor checks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vernalrd SSC Veteran Points: 262 More actions · Answer 6

Certain stored procedures had to be looked into.After changes were done,used sp_helptext (with results to text) to view the code of the stored procedure.Now when i run the sp it gave errors where single line comments came on the next line and what happened was the last alphabet of the tablename came on the next line ,This compiled successfully.I dont understand why the formatting was lost.