從儲存過程將數據庫名稱傳遞給輸入參數

我對這個應用程序有一些問題。我知道有很多關於將數據庫名稱作為輸入傳遞的主題，但我仍然無法弄清楚我的程式碼有什麼問題。希望大家能幫幫我！

我的數據庫很少，每天都會創建一個以 XXXXX-YEARMONTHDAY 為名稱的新數據庫。我想要實現的是從我的儲存過程中傳遞一個新的數據庫名稱作為輸入參數。

當它很難編寫時，它就像一個魅力：

GO
/****** Object:  StoredProcedure [dbo].[WinccToAlarms]    Script Date: 03-12-20 08:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[WinccToAlarms]
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   -- Insert statements for procedure here
   INSERT INTO Reporting.dbo.LiaisonAlarmes
   SELECT * FROM [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924].dbo.AlgRtTextsFRA
END 

但我想動態命名**$$ WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924 $$**請求的一部分。這是我寫的程式碼：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms20
   -- Add the parameters for the stored procedure here
   @NomDBAlarms sysname
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
   SET XACT_ABORT ON

   IF DB_ID(@NomDBAlarms) IS NULL
   BEGIN
   RAISERROR('Nom de base de donnée invalide',16,1)
   RETURN
   END


   DECLARE @Sql NvarChar(max)

   -- Insert statements for procedure here
   SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
   SELECT * FROM ['+(@NomDBAlarms)+'].dbo.AlgRtTextsFRA'

   Exec @Sql
   END
GO

你能幫我弄清楚這段程式碼有什麼問題嗎？

非常感謝 ！！！

行。這解決了。我必須輸入值作為輸入參數*$$ WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924 $$而不是WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924*

也適用於任何可能想要最終和工作程式碼的人；這裡是 ！

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms22
   -- Add the parameters for the stored procedure here
   @NomDBAlarms sysname
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
   SET XACT_ABORT ON

   IF DB_ID(@NomDBAlarms) IS NULL
   BEGIN
   RAISERROR('Nom de base de donnée invalide',16,1)
   RETURN
   END


   DECLARE @Sql NvarChar(max)

   -- Insert statements for procedure here
   SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
   SELECT * FROM ['+@NomDBAlarms+'].dbo.AlgRtTextsFRA'

   Exec sp_executesql @Sql
   END
GO

感謝您的支持，祝您有美好的一天！

我不知道您的錯誤消息，但嘗試替換Exec @Sql為Exec sp_executesql @Sql

引用自：https://dba.stackexchange.com/questions/280808