When a row of data is read by an application and presented to the user for possible modification, most applications do not hold a lock on the row. We will use it whenever a table has an identity column. SQL Server 2000 makes this easier than it was in previous versions by adding the SCOPE_IDENTITY() function.
If the table has an identity column, the Create stored procedure is responsible for returning it to the application program. Whether you prefer the use of natural keys or create synthetic keys, such as identity columns, for every table, they are necessary in some circumstances, such as the one mentioned above and our Create procedure should return it to the caller.
Many tables use identity columns as the primary key. Without it our stored procedures will not work. In these cases, an identity column must be added to the table to serve as the primary key. For example, some tables used for historical reporting do not have any unique combinations of fields because they record non-unique situations. Of course, most tables have primary keys anyway but sometimes there are a few tables that do not really need a primary key. Primary Keysįor the CRUD operations to work, every table must have a primary key or at least one unique index. You will find a similar table in the Northwind sample database and in many other databases that store product information. I have kept the Product table deliberately short each column is there to illustrate a particular point. , CreatedByUSERID AppUSERID DEFAULT user_name() , CreatedDT datetime NOT NULL DEFAULT getdate() , Inventor圜ost as UnitCost * UnitsInStock - Computed column , UnitsInStock int NOT NULL - units in the inventory , UnitCost money NOT NULL- Cost of making it , ProductName varchar(100) DEFAULT 'Product unnamed' ProductID int NOT NULL identity (1,1) PRIMARY KEY It is difficult to discuss them in a vacuum so to illustrate the issues involved in making the CRUD procedures let's use the following theoretical product table:ĮXEC sp_addtype N'AppUSERID', N'varchar (20)', N'not null'
CRUD STORED PROCEDURE GENERATOR SQL SERVER CODE
There are a number of issues to discuss before actually creating the code for the stored procedures.
0 kommentar(er)
