在 SQL 中执行外部 SQL 脚本

问：

我正在尝试构建一个 sql 脚本，我可以将其分解为一口大小的部分以使其更易于维护，但是我正在努力让它工作并且使用 AI 来帮助调试它没有帮助。我想知道是否有人可以给我一些见解，为什么我不能简单地执行一个单独的 sql 脚本？

-- Main SQL script for creating demo/test data
-- Prompt the user for clearing the database (Y/N)
DECLARE @ClearDatabaseOption CHAR(1)
SET @ClearDatabaseOption = 'Y' -- Change to 'Y' to clear the database

-- Check if the user wants to clear the database
IF @ClearDatabaseOption = 'Y'
BEGIN
    -- Execute a script to clear the database
    EXEC sp_executesql N':r PurchasingDataScripts\clear_database.sql';    
END
ELSE
BEGIN
-- Set up a transaction to ensure all or none of the changes are committed
BEGIN TRANSACTION;

-- Step 1: Create default document type groups
BEGIN TRY
    EXEC sp_executesql N':r PurchasingDataScripts\create_default_document_type_groups.sql';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 1: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- Step 2: Create default document types
BEGIN TRY
    EXEC sp_executesql N'
    :r PurchasingDataScripts\create_default_document_types.sql
    ';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 2: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- Step 3: Create default document sequences
BEGIN TRY
    EXEC sp_executesql N'
    :r PurchasingDataScripts\create_default_document_sequences.sql
    ';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 3: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- Step 4: Create default profile
BEGIN TRY
    EXEC sp_executesql N'
    :r PurchasingDataScripts\create_default_profile.sql
    ';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 4: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- Step 5: Create Contacts
BEGIN TRY
    EXEC sp_executesql N'
    :r PurchasingDataScripts\create_contacts.sql
    ';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 5: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- Step 6: Create demo documents
BEGIN TRY
    EXEC sp_executesql N'
    :r PurchasingDataScripts\create_demo_documents.sql
    ';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 6: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- Step 7: Create demo people
BEGIN TRY
    EXEC sp_executesql N'
    :r PurchasingDataScripts\create_demo_people.sql
    ';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 7: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- Step 8: Create Purchasing Profile Finance Codes
BEGIN TRY
    EXEC sp_executesql N'
    :r PurchasingDataScripts\create_purchasing_profile_finance_codes.sql
    ';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 8: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- Step 9: Create PurchasingEvents
BEGIN TRY
    EXEC sp_executesql N'
    :r PurchasingDataScripts\create_purchasing_events.sql
    ';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Step 9: Error encountered. Rolling back changes.';
    RETURN;
END CATCH

-- If all steps completed successfully, commit the changes
COMMIT;
PRINT 'All steps completed successfully. Changes have been committed.';

-- End of the main script

我试过了

DECLARE @CmdString NVARCHAR(4000);
SET @CmdString = 'sqlcmd -S server -d database -E -i "PurchasingDataScripts\clear_database.sql"';
EXEC xp_cmdshell @CmdString;

-- Disable xp_cmdshell for security (optional)
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;