stored proc...

Spacedog

I'm trying to write a stored proc that takes a select max() it the ID field in a database adds 1 to it and adds a record with the next hightst number...

CREATE PROCEDURE sp_insertclient (@num int, @pname char(100),@paddress char(500)) AS

declare @pnum

begin tran

@num = (select max(ClientNo)+1 from client)
insert into client values (@pnum, @pname, @paddress)

commit tran

GO

---

any ideas what's wrong with this, it says I didn't declare @pnum which I did above, is it calling me a liar or what, I've done this before but all my source code is on my bustred pc. any help appreciated.

kayos

Well I'm going to tell you that you didn't declare @pnum as well. And before you say you did take a close look at where you think you declare it and tell me whats missing....... yes thats right a datatype :rolleyes:

Also why are you passing @num as a input parameter when you dont use it as such?

Never use the prefix sp_ on a stored procedure, this prefix is normally kept for system stored procedures.

kayos

Talliesin

Originally posted by kayos
Never use the prefix sp_ on a stored procedure, this prefix is normally kept for system stored procedures.

I disagree. I would always use sp_ on stored procedures, precisely because this prefix is used on system stored procedures. (That said in SQL Server the majority of system procedures used outside of the master and msdb databases are named dt_*). Following the conventions of other code your code exists beside has many advantages. (Similarly I often use the same Hungarian as the Windows API for code that is tightly wrapped around that API, even though it isn't a style I generally like).

I do have a naming convention that avoids confusion by including a project name and a further underscore before anything else in the name, so that avoids confusion.

kayos

The prefix "sp_" is used in the system stored procedures. I do not recommend using the prefix "sp_" in user-created stored procedure as MS SQL Server will always look for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, followed by the stored procedure using dbo as the owner (if one is not specified).

When you have the stored procedure with the prefix "sp_" in a database other than master, the master database is always checked first. If the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

So in other words using the prefix "sp_" will effect preformance and may lead to the wrong stored procedure being called (although this is unlikely).

I have used the prefix "sp_" on stored procedure's before and will do again but I will only do it when writing things for maintance and the like which will be run from the master database. In addition to this just to make it clear that its not a system procedure I would use the following "sp_zyx_" as the prefix (where zyx is my company name).

Just my two cent worth.

kayos