如何创建以表名为输入参数的SQL Server 2017更新存储过程

Absolutely, you can create a stored procedure that takes a table name as input and runs your fixed update operations on it—dynamic SQL is the right approach here, since static SQL can’t handle variable table names. Let me walk you through a safe, practical implementation tailored to your needs.

Step 1: Create the Stored Procedure with Input Validation #

First, we’ll define a procedure that accepts a table name parameter, and add a check to ensure the table actually exists (to avoid runtime errors from invalid inputs).

Step 2: Build Safe Dynamic Update Logic #

We’ll use QUOTENAME() to wrap the table name—this escapes special characters (like spaces or reserved keywords) and prevents SQL injection, which is critical for user-provided object names. Then we’ll embed your fixed update operations into the dynamic SQL string.

Full Example Code #

Here’s a complete, ready-to-use procedure (replace the sample update logic with your actual operations):

CREATE PROCEDURE dbo.RunTableUpdates
    @TargetTableName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate the input table exists in the dbo schema (adjust schema if needed)
    IF NOT EXISTS (
        SELECT 1 
        FROM sys.tables 
        WHERE name = @TargetTableName 
          AND schema_id = SCHEMA_ID('dbo')
    )
    BEGIN
        RAISERROR('Table %s does not exist in the dbo schema.', 16, 1, @TargetTableName);
        RETURN;
    END

    -- Build dynamic SQL with safe table name handling
    DECLARE @DynamicUpdateSQL NVARCHAR(MAX);
    SET @DynamicUpdateSQL = N'
        UPDATE ' + QUOTENAME(@TargetTableName) + N'
        SET 
            -- Replace these lines with your actual fixed-column update logic
            LastUpdated = GETDATE(),
            Status = CASE WHEN ExpiryDate < GETDATE() THEN ''Expired'' ELSE Status END,
            TotalValue = BaseValue * Multiplier
        -- Add your WHERE clause here if needed
        WHERE IsActive = 1;
    ';

    -- Optional: Preview the generated SQL for testing
    -- PRINT @DynamicUpdateSQL;

    -- Execute the dynamic update
    EXEC sp_executesql @DynamicUpdateSQL;
END
GO

Key Notes for Your Use Case #

• SQL Injection Protection: Never concatenate raw input directly into dynamic SQL—QUOTENAME() ensures the table name is treated as a safe object identifier.
• Testing: Uncomment the PRINT statement to preview the generated SQL before executing, which helps catch syntax errors quickly.
• Permissions: Ensure the account running the procedure has UPDATE permissions on the target table, and access to sys.tables for validation.
• Schema Adjustment: If your tables aren’t in the dbo schema, modify the schema_id check to match your target schema.

How to Use the Procedure #

Simply call it with your table name as the parameter:

EXEC dbo.RunTableUpdates @TargetTableName = 'AnnualDataTable';

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