Best Stored Procedure Solution

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

SQL Server 2000 Forums

SQL Server Development (2000)

Best Stored Procedure Solution

Author

Topic

timmoser
Starting Member

8 Posts

Posted - 2004-11-18 : 22:10:53

We have an existing SQL2000 database that hardly has a consistent pattern in design. So we are debating the best way to design our database going forward. I’d like to get you opinion on the following ideas.

1.) SQL should NEVER be in the application.
2.) A separate stored procedure should be used for every transaction so…This:

CREATE PROCEDURE s_usermanager
@action VARCHAR(20)='SELECT',
@username VARCHAR(50) = NULL,
@userid NUMERIC(18) = NULL,
@userpassword VARCHAR(10) = NULL,
…
AS

IF @action='SELECTONE'
	SELECT userid, firstname, lastname, email, username, userpassword, createdate, addr_1, addr_2, city, state, zip, phone, website 
	FROM ggg_users 
	WHERE (username=@username OR email=@username) 
		AND userpassword = @userpassword
		AND accountclosed = 'F'

IF @action='INSERT'
INSERT INTO ggg_users(username, userpassword, firstname, lastname, addr_1, addr_2, city, state, zip, phone, email, website)
VALUES     (@username, @userpassword, @firstname, @lastname, @addr_1, @addr_2, @city, @state, @zip, @phone, @email, @website)

… Continues Update, Delete others…

Would be:
Broken into a bunch of stored procedures one for each process.
3.) Syntax:
Choice A)

Database Names: employee 
SP Names: s_update_employees or s_manage_employees
Table Names: employees
Fields: emp_id
Variables: @emp_id
Views: employees
SQL: SELECT, INSERT, DELETE…

Choice B)

Database Names: EMPLOYEE 
SP Names: UpdateEmployees or ManageEmployees
Table Names: Employees
Fields: EmpId
Variables: @EmpId
Views: vEmployees
SQL: SELECT, INSERT, DELETE…

If you have a reference and good reasons to choose one over the other please include them in your post.

Thanks for your thoughts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources