Passing database name to input parameter from a stored procedure

Question

I have some issue with this application. I know that there are a lot of topics about passing a Database name as input but still I couldn't figure out what is the issue my code. Hopefuly, you guys can help me out !

I have few database and everyday, a new database is created with XXXXX-YEARMONTHDAY as name. What i want to achieve is to pass a new database name as input parameter from my stored procedure.

When it's hard written, it's working like a charm :

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 

But i want to dynamically name the [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924] part of the request. Here is the code that i wrote :

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

Can you help me figure out what is wrong with this piece of code?

Thanks a lot !!!

Answer 1

Ok. This is solved. I had to enter as input parameter the value [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924] instead of WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924

Also for anyone who might want the final and working code; Here it is !

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

Thank you for your support and have a nice day !

Answer 2

I don't know your error message, but try to replace Exec @Sql with Exec sp_executesql @Sql