nameing convention ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

nameing convention ?

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-13 : 16:46:41

I have a database "mtsnew", now I create a stored proc on it.

I didn't switch database with "use mtsnew", but I can't use full name like "mtsnew.dbo.CountCase" , why ?

and I can use "mtsnew..mts_case" , why can't use "mtsnew..dbo.mts_case " ?

what is the guideline of naming convention ?

Thanks


if(OBJECT_ID('mtsnew.dbo.CountCase') IS NOT NULL)
	drop PROCEDURE dbo.CountCase	
GO

CREATE PROCEDURE CountCase # can't use mts.dbo.CountCase
@id VARCHAR(50)  
AS  
BEGIN 
print 'running';
SELECT COUNT(*) FROM mtsnew..mts_case c WHERE c.caseId=CONVERT(INT, @id) 
END 

EXECUTE mtsnew.dbo.CountCase '1234'; #wrong, only dbo.CountCase
GO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-13 : 18:14:32

Check the schema in information_schema.tables.
I suspect you aren't sa and are creating it in another schema.

In the SP you are calling the SP recusively so it has to delat resolution. Adding the database name probably makes the optimiser try to check when the SP is created and it finds it's not there.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-14 : 11:28:32

It's part of the 4-part naming convention: Server.Database.Schema.Object

When you do: "mtsnew..mts_case" you are omitting the Schema and presumably, SQL figures it out for you.

When you do: "mtsnew..dbo.mts_case " you are omitting the Database Name (and supplying the Server Name), when obviously doesn't exist.

http://msdn.microsoft.com/en-us/library/ms177563.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources