A SQL statement is needed to create a stored procedure, which adds a new record to the Part table of week 3. The stored proc returns an out parameter that contains the value of the PartID PK of the record just added. Input parameters are needed to pass the field values of the record to be added. Keep in mind that the PK field in the Part table of week 3 is an idendity field, which SQL Server creates values for. The identity PK field is managed by SQL Server.
To create a stored procedure in SQL Server that adds a new record to the Part table with an identity PK field and returns the PartID of the record just added, you can use the following SQL statement:
```sql
CREATE PROCEDURE AddPart
@PartName VARCHAR(100),
@PartDescription VARCHAR(500),
@PartPrice DECIMAL(10, 2),
@PartID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Part (PartName, PartDescription, PartPrice)
VALUES (@PartName, @PartDescription, @PartPrice);
SET @PartID = SCOPE_IDENTITY();
END;
```
Explanation:
1. We start by using the `CREATE PROCEDURE` statement to create the stored procedure named `AddPart`.
2. The `@PartName`, `@PartDescription`, `@PartPrice` parameters are declared to hold the field values of the record to be added.
3. The `@PartID INT OUTPUT` parameter is declared as an output parameter to store the PartID of the newly added record.
4. The `AS BEGIN` block encloses the body of the stored procedure.
5. `SET NOCOUNT ON` is used to prevent the count of the number of rows affected by the INSERT statement from being returned as a result.
6. The `INSERT INTO` statement adds a new record to the Part table with the provided field values.
7. The `SET @PartID = SCOPE_IDENTITY()` statement assigns the identity value generated for the newly added record to the @PartID output parameter. `SCOPE_IDENTITY()` returns the last identity value created within the current scope, which in this case, is the Part table.
Now you can execute the stored procedure and retrieve the PartID of the record just added using an output parameter.