July 15, 2003 at 3:28 pm
I'm curious if some other developers would offer their opinions on best practicies for qualifing table names inside a stored proc. Of course I make a practice of qualifying the procedure with dbo. in the CREATE and EXEC statements, but do I have a good reason to write this code in the procedure itself?
select Column1
from dbo.table
I am correct in my understanding that the procedure will only reference tables owned by the procedure owner if the table is not qualified, correct?
What about the following syntax
select Column1
from .table
I find this more readable, but do I accomplish anything by adding the implied dbo reference? Specifically in avoiding a recompliation?
Thanks for all responses
July 18, 2003 at 8:00 am
This was removed by the editor as SPAM
July 18, 2003 at 10:01 am
I've made it a practice to qualify everything. I think it's better coding regardless of performance implications. I think it does affect performance still, without a table owner at any point it has to evaluate potentially twice, one using the owner, again as whoever is running the proc since they might have their own access to the object.
Andy
July 18, 2003 at 10:02 am
Although I have an opinion on your approach, it's been said many times here that the most imporant thing is to be consistent in naming conventions. If you already have a style that's been used in production then stick with it. Whoever follows will appreciate only having to adapt to one style.
P.S. My unit uses tbl, qry, spr, and fn at the beginning of our names and don't bother with dbo unless required. But remember, that's only us.
Everett
Everett Wilson
ewilson10@yahoo.com
July 18, 2003 at 10:20 am
Thank you for the replies....
Andy indicated that the proc would potentially evaluate twice if there was a table owned by dbo and the user calling the proc. From my tests the proc will only references tables owned by the proc owner itself if the table is not qualified.
***********************
For example, the database contains
someowner.table
create proc dbo.ExampleProc as
select * from table
*************************
someowner executes the proc by calling
exec dbo.ExampleProc
The proc will fail because there is not a table owned by dbo, Agree?
With this knowledge, I was then wondering if I had any reason to qualify the names in a proc for select, update, insert, delete statements.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply