SP Design Model
Most Web-based applications use the back-end DB in a quite straightforward way. Consider this use case: a user submits some data (name, address, phone number, etc.) that is processed by some business logic and then stored in the database. Later, someone asks for a report on the user's details and the data is retrieved and presented. The user may come back and change the details or sign out, resulting in the deletion of the user records altogether.
Look at the sample Dog Registration Database shown in Figure 1. Get all DDL for the DB here.
The initial set of stored procedures for the tAnimalOfficer table may look like this:
CREATE PROCEDURE spNewtAnimalOfficer (@OfficerID nvarchar(255), @Name nvarchar(255), @StationName nvarchar(255), @TerritorialAuthorityID int)
INSERT INTO tAnimalOfficer (OfficerID, Name, StationName, TerritorialAuthorityID)
VALUES (@OfficerID, @Name, @StationName, @TerritorialAuthorityID)
CREATE PROCEDURE spUpdatetAnimalOfficer (@OfficerID nvarchar(255), @Name nvarchar(255), @StationName nvarchar(255), @TerritorialAuthorityID int)
IF @Name IS NOT NULL UPDATE tAnimalOfficer SET Name=@Name WHERE OfficerID=@OfficerID
IF @StationName IS NOT NULL UPDATE tAnimalOfficer SET StationName=@StationName WHERE OfficerID=@OfficerID
IF @TerritorialAuthorityID IS NOT NULL UPDATE tAnimalOfficer SET TerritorialAuthorityID=@TerritorialAuthorityID WHERE OfficerID=@OfficerID
CREATE PROCEDURE [dbo].[spDeletetAnimalOfficer] (@OfficerID nvarchar(255))
DELETE FROM tAnimalOfficer WHERE OfficerID=@OfficerID
Your other SPs for this DB are not going to be much different because they all share the same underlying idea: all are plain insert, update, or delete statements.
SQL Server Metadata Views
The next step is to get the list of tables and their columns from SQL Server and then somehow generate similar SPs. SQL server has a number of views and tables for this. To cut a long story short, try this statement:
select * from sys.objects
Anything with type='U' is a user table. User tables that have is_ms_shipped=1 are in fact system tables that the server itself generates and those with is_ms_shipped=0 are the ones you created. However, I noticed that the is_ms_shipped flag is not always set correctly.
The following is the SQL query to get a list of user tables for SP generation:
select * from sys.objects where type='U' and is_ms_shipped=0
The list of columns sits in the information_schema.columns view. Use this SQL statement to retrieve the basic information you need to generate the rest of the SPs:
select * from information_schema.columns where table_name='tAnimalOfficer' order by ordinal_position