February 14, 2006 at 10:44 am
Hi
SQL Server 2000 won't check stored procedure semantics anymore.
So if write a stored procedure which queries f.ex. "select foo from bar"
in any db and use query analyzer/enterprise manager to store
that procedure into db, it would go there without problems.
How to force semantics check in server level? Or should I
add some commands to the create procedure statement?
February 14, 2006 at 11:22 am
SQL Server does this by design to allow some objects to be created after other objects. What SQL Server does when created an object, if any reference object exists, the checks are made but if the object doesn't exist, it assumes you will be creating it later.
For example given:
Create table A (col1 int)
create procedure sp_A as
select col1, col2 from A
go
create procedure sp_B as
select col1, col2 from B
go
sp_A will fail to be created but sp_B will be created.
February 14, 2006 at 11:52 pm
Should SQL Server warn me, if I use osql.exe to store the procedure with unknown references in the DB?
February 15, 2006 at 12:30 am
1. SQL will warn you if you create a procedure which is refereing to "non-existing" procedure
create procedure foo
as
execute bar
=========
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'bar'. The stored procedure will still be created.
2. It will not warn if you refer a table which is not available
3. SQL will check if the object which is referred is it is existing then column should be proper (as pointed by JeffB)
February 15, 2006 at 2:47 am
Ok. Is there some easy way test if some procedures that are stored in db references to non-existing objects(tables/procs/udfs)?
February 15, 2006 at 9:24 am
It is always a good idea to test a procedure after developing it. This will catch the errors because the execution will fail.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply