|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored procedure/trigger contain reference to a non-existing objecThis question is for both SQL Server 2000 and SQL Server 2005.
Are there any problem in creating/compiling a stored procedure/trigger if it contains reference to a non-existing object such as table or stored procedure? Of course, the coding reference such object will be executed only if the object exists. Thanks. defered name resolution in sql2k/5 will allow you to do this.
e.g. create proc usp as select * from nonexistenttable go -- Show quote-oj "Peter" <Pe***@discussions.microsoft.com> wrote in message news:6B46255E-7889-470B-BF70-DC50E87A5B46@microsoft.com... > This question is for both SQL Server 2000 and SQL Server 2005. > > Are there any problem in creating/compiling a stored procedure/trigger if > it > contains reference to a non-existing object such as table or stored > procedure? > Of course, the coding reference such object will be executed only if the > object exists. > > Thanks. > Hi oj,
Are there any setting to disallow deferred name resolution? Peter Show quote "oj" wrote: > defered name resolution in sql2k/5 will allow you to do this. > > e.g. > create proc usp > as > select * from nonexistenttable > go > > > -- > -oj > > > > "Peter" <Pe***@discussions.microsoft.com> wrote in message > news:6B46255E-7889-470B-BF70-DC50E87A5B46@microsoft.com... > > This question is for both SQL Server 2000 and SQL Server 2005. > > > > Are there any problem in creating/compiling a stored procedure/trigger if > > it > > contains reference to a non-existing object such as table or stored > > procedure? > > Of course, the coding reference such object will be executed only if the > > object exists. > > > > Thanks. > > > > > Hi oj,
I just read the deferred name resolution in SQL Server 2000 BOL. It seems that it is Ok to create but not during execution time. Peter Show quote "oj" wrote: > defered name resolution in sql2k/5 will allow you to do this. > > e.g. > create proc usp > as > select * from nonexistenttable > go > > > -- > -oj > > > > "Peter" <Pe***@discussions.microsoft.com> wrote in message > news:6B46255E-7889-470B-BF70-DC50E87A5B46@microsoft.com... > > This question is for both SQL Server 2000 and SQL Server 2005. > > > > Are there any problem in creating/compiling a stored procedure/trigger if > > it > > contains reference to a non-existing object such as table or stored > > procedure? > > Of course, the coding reference such object will be executed only if the > > object exists. > > > > Thanks. > > > > > yup, the defered name resolution feature only allows you to compile/create
the sproc. -- Show quote-oj "Peter" <Pe***@discussions.microsoft.com> wrote in message news:563D0BB5-7455-4352-8BB8-28C1C42DF886@microsoft.com... > Hi oj, > > I just read the deferred name resolution in SQL Server 2000 BOL. It seems > that it is Ok to create but not during execution time. > > > Peter > > "oj" wrote: > >> defered name resolution in sql2k/5 will allow you to do this. >> >> e.g. >> create proc usp >> as >> select * from nonexistenttable >> go >> >> >> -- >> -oj >> >> >> >> "Peter" <Pe***@discussions.microsoft.com> wrote in message >> news:6B46255E-7889-470B-BF70-DC50E87A5B46@microsoft.com... >> > This question is for both SQL Server 2000 and SQL Server 2005. >> > >> > Are there any problem in creating/compiling a stored procedure/trigger >> > if >> > it >> > contains reference to a non-existing object such as table or stored >> > procedure? >> > Of course, the coding reference such object will be executed only if >> > the >> > object exists. >> > >> > Thanks. >> > >> >> >> Hi oj,
Anyway to workaround this? I need to write coding that only run if the object exists otherwise skip coding. For example, if table1 exists, select * from table1 else select * from table2 Show quote "oj" wrote: > yup, the defered name resolution feature only allows you to compile/create > the sproc. > > -- > -oj > > > > "Peter" <Pe***@discussions.microsoft.com> wrote in message > news:563D0BB5-7455-4352-8BB8-28C1C42DF886@microsoft.com... > > Hi oj, > > > > I just read the deferred name resolution in SQL Server 2000 BOL. It seems > > that it is Ok to create but not during execution time. > > > > > > Peter > > > > "oj" wrote: > > > >> defered name resolution in sql2k/5 will allow you to do this. > >> > >> e.g. > >> create proc usp > >> as > >> select * from nonexistenttable > >> go > >> > >> > >> -- > >> -oj > >> > >> > >> > >> "Peter" <Pe***@discussions.microsoft.com> wrote in message > >> news:6B46255E-7889-470B-BF70-DC50E87A5B46@microsoft.com... > >> > This question is for both SQL Server 2000 and SQL Server 2005. > >> > > >> > Are there any problem in creating/compiling a stored procedure/trigger > >> > if > >> > it > >> > contains reference to a non-existing object such as table or stored > >> > procedure? > >> > Of course, the coding reference such object will be executed only if > >> > the > >> > object exists. > >> > > >> > Thanks. > >> > > >> > >> > >> > > > You can do this with dynamic sql.
e.g. if object_id('tb1') is not null exec('select * from tb1') if object_id('tb2') is not null exec('select * from tb2') -- Show quote-oj "Peter" <Pe***@discussions.microsoft.com> wrote in message news:83F93920-F498-4633-9886-9882C5D519DF@microsoft.com... > Hi oj, > > Anyway to workaround this? I need to write coding that only run if the > object exists otherwise skip coding. > > For example, > > if table1 exists, > select * from table1 > else > select * from table2 > > > > "oj" wrote: > >> yup, the defered name resolution feature only allows you to >> compile/create >> the sproc. >> >> -- >> -oj >> >> >> >> "Peter" <Pe***@discussions.microsoft.com> wrote in message >> news:563D0BB5-7455-4352-8BB8-28C1C42DF886@microsoft.com... >> > Hi oj, >> > >> > I just read the deferred name resolution in SQL Server 2000 BOL. It >> > seems >> > that it is Ok to create but not during execution time. >> > >> > >> > Peter >> > >> > "oj" wrote: >> > >> >> defered name resolution in sql2k/5 will allow you to do this. >> >> >> >> e.g. >> >> create proc usp >> >> as >> >> select * from nonexistenttable >> >> go >> >> >> >> >> >> -- >> >> -oj >> >> >> >> >> >> >> >> "Peter" <Pe***@discussions.microsoft.com> wrote in message >> >> news:6B46255E-7889-470B-BF70-DC50E87A5B46@microsoft.com... >> >> > This question is for both SQL Server 2000 and SQL Server 2005. >> >> > >> >> > Are there any problem in creating/compiling a stored >> >> > procedure/trigger >> >> > if >> >> > it >> >> > contains reference to a non-existing object such as table or stored >> >> > procedure? >> >> > Of course, the coding reference such object will be executed only if >> >> > the >> >> > object exists. >> >> > >> >> > Thanks. >> >> > >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics