Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to name a stored procedure the same as a table? I have an existing table named 'buyers', and I'm trying to create a stored procedure named 'buyers', but I'm getting this error:

There is already an object named 'buyers' in the database.

share|improve this question
4  
I'm creating a stored procedure named 'buyers' -- I would strongly reconsider this name as it's not very descriptive. The need to duplicate names in this way is highly suspect. –  Jon Seigel Aug 13 '13 at 17:14
add comment

3 Answers

No. This is clearly stated in the MSDN documentation:

Procedure names must comply with the rules for identifiers and must be unique within the schema.

Since you have a table with the same name, in that schema, you cannot create the stored procedure with that name.

share|improve this answer
2  
To be fair, that statement would be slightly more clear if it ended with "unique across all objects in the schema." Currently it could be interpreted as unique within all procedures in the schema. IMHO. –  Aaron Bertrand Aug 13 '13 at 19:15
    
Are you familiar with opening Connect items? –  SQLRockstar Aug 13 '13 at 21:03
    
Connect? What's that? :-) –  Aaron Bertrand Aug 13 '13 at 21:12
add comment

I believe the rule is that "schema+name" must be unique - so you can have two tables of the same name even, as long as they are in different schemas. The default schema is set by your user properties, this is dbo more often than not so if you do not specify a schema your object called object is actually dbo.object.

This means you can have a procedure with the same name as a table as long as they are in different schemas: so you could have a table "dbo.thingy", a procedure "procs.thingy", a view "views.thingy", and so on, if you really want - but this really would not be recommended as it will cause significant confusion later on.

If you are not using (or even familiar with) schemas then the short answer is no. Object names within a schema must be unique, so if you only have the one (default) schema then object names must be unique full stop.

share|improve this answer
add comment

You will get error as there schema.object_name has to be unique in the database.

Instead when you create an SP, then suffix it with usp_SP_NAME. Naming conventions should be followed and enforced (through triggers or Policy based management), so that you don't encounter such issues.

e.g. (below is just a convention that I use, you can use a different convention as well)

usp_SP_NAME ==> user stored procedure (usp) and then Stored Procedure Name.

or

usp_sBuyers ==> user stored procedure that does select (s) from Buyers table.

usp_uBuyers ==> user stored procedure that does updates (u) to Buyers table.

share|improve this answer
1  
While I agree in general, and acknowledge that naming conventions are quite subjective, I do have a couple of issues with your suggested scheme: (1) I don't get the point of the usp_ prefix. In what context will you be confused that this object is anything but a stored procedure? You can't EXEC tables, views, functions, etc. and you can't SELECT from a stored procedure. Now imaging you are looking for a stored procedure in a list that does a certain thing - would adding LastName_ to every last name in the phone book help you find a person faster? –  Aaron Bertrand Aug 13 '13 at 17:34
1  
(2) I don't really like stuffing the verb (s or u) in front of the entity name. As above, if I am looking for stored procedures in an alphabetical list, I am probably looking for all the stored procedures involving Buyers, not all stored procedures that select or update stuff. With your scheme, they'll be grouped together by what they do at a high level, rather than what they do it to. I also see no need to be cryptic. How often are you typing a procedure name that typing out the word SELECT instead of just s is going to be painful? (3) My pref is obj_verb e.g. dbo.Buyer_Update. –  Aaron Bertrand Aug 13 '13 at 17:37
1  
These are just observations about your scheme and don't really relate to the question (except that their naming scheme - or rather, lack of one - has issues too). –  Aaron Bertrand Aug 13 '13 at 17:37
    
@AaronBertrand Good points, but just my way of naming them as we use it for our application. I was just giving an example as you said that naming conventions are subjective. –  Kin Aug 13 '13 at 18:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.