将数据库名称从存储过程传递给输入参数

Question

我对这个申请有一些意见。我知道有很多关于将数据库名称作为输入传递的主题，但是我仍然不知道我的代码的问题是什么。希望你们能帮我！

我几乎没有数据库，每天都会创建一个以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-9 SEBDMDC9O9_HMI#B5N2_阿尔格_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

你能帮我找出这段代码有什么问题吗？

非常感谢！

Answer 1

好的。这件事解决了。我必须输入值WIN-9 SEBDMDC9O9_HMI#B5N2_阿尔格_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

感谢您的支持，祝您今天愉快！

Answer 2

我不知道您的错误消息，但是尝试用Exec @Sql替换Exec sp_executesql @Sql