SQL server 动态SQL的存储过程编写

USE [data_big]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_RefreshOdsByDate]
   @DateThreshold DATE = NULL
AS

DECLARE @DatabaseName NVARCHAR(255) = N'big_source';

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	 -- Use yesterday's date as the default value if no date parameter is provided  
    IF @DateThreshold IS NULL  
        SET @DateThreshold = CONVERT(DATE, GETDATE() - 1);
	


	 --- dbo.ods_sale_com

    -- Insert statements for procedure here
	-- 开始 TRY 块  
    BEGIN TRY  
        -- 执行可能引发异常的 SQL 语句  
		DELETE FROM dbo.ods_sale_com WHERE dt = @DateThreshold

        DECLARE @Sql2 NVARCHAR(MAX) = N'
			INSERT INTO dbo.ods_sale_com ([id],[title],[bh],[company],[person],[content],[dt])
			SELECT  [id]
			,[title]
			,[bh]
			,[company]
			,[person]	
			,[content]
			,' + QUOTENAME(CONVERT(NVARCHAR(10), @DateThreshold, 120), '''') + N'as dt FROM' + QUOTENAME(@DatabaseName) + N'.[dbo].[sale_com]';

		SET @Sql2 = REPLACE(@Sql2, '@DateThreshold', @DateThreshold);

		EXEC sp_executesql @Sql2;
  
		DELETE FROM dbo.ods_sale_com WHERE dt = DATEADD(day, -2, @DateThreshold);
    END TRY  
      
    -- 开始 CATCH 块  
    BEGIN CATCH  
        -- 捕捉异常并执行特定的操作  
        DECLARE @ErrorMessage2 NVARCHAR(4000);  
        SET @ErrorMessage2 = ERROR_MESSAGE();  
          
        RAISERROR (@ErrorMessage2, 16, 1);  
          
        -- 继续执行其他操作  
        -- ...  
    END CATCH


END