Hehe that’s why I use spr_ :)
-----Original Message----- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 17 October 2003 11:16 To: '[EMAIL PROTECTED]' Subject: RE: [ cf-dev ] Stored Procedures Yes, here you go...it was also one of the first things explained in my SQL Server exams.. ;-) NOTE: If your stored procedure name begins with the "sp_" prefix and is not in the master database, you see SP:CacheMiss before the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules (the "preferred" location is in the master database). The names of user-created stored procedures should not begin with "sp_." http://support.microsoft.com/default.aspx?scid=http://support.microsoft. com: 80/support/kb/articles/q263/8/89.asp&NoWebContent=1 -----Original Message----- From: Snake Hollywood [mailto:[EMAIL PROTECTED] Sent: 17 October 2003 11:06 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Stored Procedures All stored procs are defined as USER or SYSTEM, you can see this in enterprise manager, USER stored procs are not referenced in the master table and it is common practice for people to prefix them with SP_ I am not aware of SQL doing a lookup in the master table for a stored proc just because it is prefixed with SP_, can you show the documentation of this ? Russ Michaels Macromedia/Allaire Certified ColdFusion Developer CFDeveloper The free resource and community for ColdFusion developer. http://www.cfdeveloper.co.uk Join the CFDeveloper discussion lists. To subscribe send an e-mail to [EMAIL PROTECTED] > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 17 October 2003 10:23 > To: '[EMAIL PROTECTED]' > Subject: RE: [ cf-dev ] Stored Procedures > > > One thing to note : you should not really, for a performance > point of view, name any user sprocs as "sp_xxx" SQL Server > will first look into the Master > database for your sp and in some cases can increase parsing > time. If you > name them as say udfsp_ as an example, the SQL server will > look in the current database for them and ignore the master look up. > > You also do not require dbo. as this associates that sp with > the current logged in user and can lead to issue when > dropping/altering etc at a later stage when importing etc. > > HTH > > N > > -----Original Message----- > From: Allan Cliff [mailto:[EMAIL PROTECTED] > Sent: 17 October 2003 10:06 > To: CF - List > Subject: [ cf-dev ] Stored Procedures > > > Any ideas why this SP doesn't work? I have only used simple > SPs before. If I remove the subquery then it returns results. > > Can't you use subqueries in SPs like this, if not what is the > solution. > > Thanks > > Allan > > -------------------------------------------------------------- > -------------- > > CREATE PROC dbo.sp_GetMenu > > @fuseaction varchar > > AS > > SELECT * > FROM Fuseactions > WHERE MenuID = (SELECT MenuID FROM Fuseactions WHERE fuseaction = > @fuseaction) > ORDER BY MenuOrder > > GO > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.525 / Virus Database: 322 - Release Date: 09/10/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.525 / Virus Database: 322 - Release Date: 09/10/2003 -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
