SQL Server中usp_createPackage存储过程表值参数实现咨询

Hey there! I totally get that table-valued parameters (TVPs) can feel confusing when you're first working with them—let's walk through building your usp_createPackage stored procedure with clear, actionable steps.

1. First: Define the Table-Valued Parameter Type #

Before you can use a TVP in your stored procedure, you need to create a user-defined table type (UDTT) that matches the structure of the service data you're passing in (based on the schema you referenced). Here's how to create it:

-- Create the user-defined table type for service items
CREATE TYPE dbo.ServiceItemType AS TABLE
(
    ServiceID INT NOT NULL,
    Quantity INT NOT NULL,
    Price DECIMAL(18,2) NOT NULL
);
GO

This type mirrors the table structure you'll use to pass multiple service entries into the procedure in one go.

2. Create the usp_createPackage Stored Procedure #

Now let's build the stored procedure with input parameters (including the TVP) and output parameters. We'll include transaction handling to ensure data integrity, plus clear output for success/error status.

CREATE PROCEDURE dbo.usp_createPackage
    @PackageName NVARCHAR(100) NOT NULL, -- Input: Name of the new package
    @PackageDescription NVARCHAR(500) NULL, -- Optional input: Package description
    @ServiceItems dbo.ServiceItemType READONLY, -- TVP input: List of services in the package
    @NewPackageID INT OUTPUT, -- Output: ID of the newly created package
    @ResultMessage NVARCHAR(250) OUTPUT -- Output: Status message (success/error)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- Start a transaction to ensure all operations succeed or fail together
        BEGIN TRANSACTION;

        -- 1. Insert the new package into your Package table
        INSERT INTO dbo.Package (PackageName, PackageDescription)
        VALUES (@PackageName, @PackageDescription);

        -- Get the ID of the newly created package
        SET @NewPackageID = SCOPE_IDENTITY();

        -- 2. Insert each service from the TVP into the Package-Service junction table
        INSERT INTO dbo.PackageServices (PackageID, ServiceID, Quantity, Price)
        SELECT @NewPackageID, ServiceID, Quantity, Price
        FROM @ServiceItems;

        -- Commit the transaction if all steps succeed
        COMMIT TRANSACTION;
        SET @ResultMessage = 'Package created successfully. New Package ID: ' + CAST(@NewPackageID AS NVARCHAR(10));
    END TRY
    BEGIN CATCH
        -- Rollback transaction if any error occurs
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Set error message for output
        SET @ResultMessage = 'Error creating package: ' + ERROR_MESSAGE();
        SET @NewPackageID = NULL; -- Clear output ID on error
    END CATCH
END
GO

3. Key Details to Understand #

• TVP Usage: Notice we mark the TVP parameter as READONLY—this is required because TVPs can't be modified inside the stored procedure.
• Transaction Handling: The BEGIN TRANSACTION/COMMIT/ROLLBACK ensures that if inserting the package or its services fails, none of the changes are saved to the database.
• Output Parameters: @NewPackageID returns the unique ID of the new package, and @ResultMessage gives clear feedback on whether the operation succeeded or failed.

4. How to Execute the Procedure #

To test it, you'll declare a variable of the UDTT type, populate it with service data, then call the procedure:

DECLARE @ServiceItems dbo.ServiceItemType;
DECLARE @NewPackageID INT;
DECLARE @ResultMessage NVARCHAR(250);

-- Populate the TVP with service entries
INSERT INTO @ServiceItems (ServiceID, Quantity, Price)
VALUES (1, 2, 50.00), (3, 1, 100.00);

-- Call the stored procedure
EXEC dbo.usp_createPackage
    @PackageName = 'Premium Home Service Package',
    @PackageDescription = 'Includes 2 cleaning sessions and 1 HVAC check',
    @ServiceItems = @ServiceItems,
    @NewPackageID = @NewPackageID OUTPUT,
    @ResultMessage = @ResultMessage OUTPUT;

-- View the results
SELECT @NewPackageID AS NewPackageID, @ResultMessage AS ResultMessage;

That's it! This setup lets you pass multiple service records into the procedure efficiently, which is way cleaner than passing individual parameters for each service.

内容的提问来源于stack exchange，提问作者Zun Jie