Developing Data Layer: Creating Stored Procedures (MS SQL 2000)
March 14th, 2008 admin Posted in development, sql |
Starting creating an application that is going to access a database the first thing I do is I am creating stored procedures. The sprocs have to be as simple as possible as I am not gonna implement any business logic within them. So all they should do is to insert, update, retrieve and delete information. To simplify the process of the creating that sprocs I have created a simple sql script wich creates them automatically. All I have to do now is to set the parameters of the script, press F5 in my Query Analyzer or SQL Management Studio, copy results of the execution from the message window, paste them into a new window, then probably modify the pasted script (if I need to change any specific settings) and finally press F5 again to create the sprocs.
The parameters I need to modify are:
SET @ProcedurePrefix = 'usp_' -- prefix of the sproc (kinda usp_GetEmployee)
SET @TablePrefix = '' -- table prefix, if a table has a prefix (dt_)
SET @TableName = 'dt_Employee'
SET @IncludeTabulation = 1 -- are we gonna generate paginating and sorting staff (see previous post)
SET @CreateSaveAndAddTogether = 0 -- there is an option to generate one sproc for inserting and updating
This script has its limitation though. Currently a table for wich we are going to generate sprocs has to have an integer primary key.
files: Generate Sprocs
Leave a Reply