use Northwind
go
exec P_PTC_SQLGEN_MSSQL2000 'Employees'

=================================================================================================================================

Insert SQL                                                                                                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------
INSERT Employees (
   LastName,
   FirstName,
   Title,
   TitleOfCourtesy,
   BirthDate,
   HireDate,
   Address,
   City,
   Region,
   PostalCode,
   Country,
   HomePhone,
   Extension,
   Photo,
   Notes,
   ReportsTo,
   PhotoPath)
VALUES (
   @LastName,
   @FirstName,
   @Title,
   @TitleOfCourtesy,
   @BirthDate,
   @HireDate,
   @Address,
   @City,
   @Region,
   @PostalCode,
   @Country,
   @HomePhone,
   @Extension,
   @Photo,
   @Notes,
   @ReportsTo,
   @PhotoPath)

Select SQL                                                                                                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------
SELECT
   A.EmployeeID,
   A.LastName,
   A.FirstName,
   A.Title,
   A.TitleOfCourtesy,
   A.BirthDate,
   A.HireDate,
   A.Address,
   A.City,
   A.Region,
   A.PostalCode,
   A.Country,
   A.HomePhone,
   A.Extension,
   A.Photo,
   A.Notes,
   A.ReportsTo,
   A.PhotoPath
FROM
   Employees AS A
WHERE
   A.EmployeeID = @EmployeeID

Update SQL                                                                                                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------
UPDATE Employees SET
   LastName = @LastName,
   FirstName = @FirstName,
   Title = @Title,
   TitleOfCourtesy = @TitleOfCourtesy,
   BirthDate = @BirthDate,
   HireDate = @HireDate,
   Address = @Address,
   City = @City,
   Region = @Region,
   PostalCode = @PostalCode,
   Country = @Country,
   HomePhone = @HomePhone,
   Extension = @Extension,
   Photo = @Photo,
   Notes = @Notes,
   ReportsTo = @ReportsTo,
   PhotoPath = @PhotoPath
WHERE
   EmployeeID = @EmployeeID

Delete SQL                                                                                                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------
DELETE
   Employees
WHERE
   EmployeeID = @EmployeeID

Stored Procedure SQL                                                                                                                                                                                     
---------------------------------------------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.[P_Employees_I1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[P_Employees_I1]
GO
CREATE PROCEDURE dbo.P_Employees_I1 (
   @LastName nvarchar(20),
   @FirstName nvarchar(10),
   @Title nvarchar(30),
   @TitleOfCourtesy nvarchar(25),
   @BirthDate datetime,
   @HireDate datetime,
   @Address nvarchar(60),
   @City nvarchar(15),
   @Region nvarchar(15),
   @PostalCode nvarchar(10),
   @Country nvarchar(15),
   @HomePhone nvarchar(24),
   @Extension nvarchar(4),
   @Photo image,
   @Notes ntext,
   @ReportsTo int,
   @PhotoPath nvarchar(255),
   @EmployeeID int OUTPUT) AS

DECLARE @return_code int

INSERT Employees (
   LastName,
   FirstName,
   Title,
   TitleOfCourtesy,
   BirthDate,
   HireDate,
   Address,
   City,
   Region,
   PostalCode,
   Country,
   HomePhone,
   Extension,
   Photo,
   Notes,
   ReportsTo,
   PhotoPath)
VALUES (
   @LastName,
   @FirstName,
   @Title,
   @TitleOfCourtesy,
   @BirthDate,
   @HireDate,
   @Address,
   @City,
   @Region,
   @PostalCode,
   @Country,
   @HomePhone,
   @Extension,
   @Photo,
   @Notes,
   @ReportsTo,
   @PhotoPath)

SELECT @return_code = @@ERROR, @EmployeeID = @@IDENTITY

RETURN @return_code
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.[P_Employees_S1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[P_Employees_S1]
GO
CREATE PROCEDURE dbo.P_Employees_S1 (
   @EmployeeID int) AS

DECLARE @return_code int

SELECT
   A.EmployeeID,
   A.LastName,
   A.FirstName,
   A.Title,
   A.TitleOfCourtesy,
   A.BirthDate,
   A.HireDate,
   A.Address,
   A.City,
   A.Region,
   A.PostalCode,
   A.Country,
   A.HomePhone,
   A.Extension,
   A.Photo,
   A.Notes,
   A.ReportsTo,
   A.PhotoPath
FROM
   Employees AS A
WHERE
   A.EmployeeID = @EmployeeID

SELECT @return_code = @@ERROR

RETURN @return_code
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.[P_Employees_U1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[P_Employees_U1]
GO
CREATE PROCEDURE dbo.P_Employees_U1 (
   @EmployeeID int,
   @LastName nvarchar(20),
   @FirstName nvarchar(10),
   @Title nvarchar(30),
   @TitleOfCourtesy nvarchar(25),
   @BirthDate datetime,
   @HireDate datetime,
   @Address nvarchar(60),
   @City nvarchar(15),
   @Region nvarchar(15),
   @PostalCode nvarchar(10),
   @Country nvarchar(15),
   @HomePhone nvarchar(24),
   @Extension nvarchar(4),
   @Photo image,
   @Notes ntext,
   @ReportsTo int,
   @PhotoPath nvarchar(255)) AS

DECLARE @return_code int

UPDATE Employees SET
   LastName = @LastName,
   FirstName = @FirstName,
   Title = @Title,
   TitleOfCourtesy = @TitleOfCourtesy,
   BirthDate = @BirthDate,
   HireDate = @HireDate,
   Address = @Address,
   City = @City,
   Region = @Region,
   PostalCode = @PostalCode,
   Country = @Country,
   HomePhone = @HomePhone,
   Extension = @Extension,
   Photo = @Photo,
   Notes = @Notes,
   ReportsTo = @ReportsTo,
   PhotoPath = @PhotoPath
WHERE
   EmployeeID = @EmployeeID

SELECT @return_code = @@ERROR

RETURN @return_code
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.[P_Employees_D1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[P_Employees_D1]
GO
CREATE PROCEDURE dbo.P_Employees_D1 (
   @EmployeeID int) AS

DECLARE @return_code int

DELETE
   Employees
WHERE
   EmployeeID = @EmployeeID

SELECT @return_code = @@ERROR

RETURN @return_code
GO