0

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 !!!

2
  • The error message is : Incorrect syntax near '-' I added the sp_executesql so it looks like Exec sp_executesql @Sql but still the same error message : Incorrect syntax near '-'. Dec 3, 2020 at 10:26
  • I also changed the +(@NomDBAlarms)+ to +@NomDBAlarms+ but it did not change the result. What i insert as input parameter is WIN9SEBDMDC9O9_HMI#B5N2_ALG_202012020924 Dec 3, 2020 at 10:27

2 Answers 2

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 !

1
  • 2
    That is why you should always PRINT first what you are building (poor mans debugging). If you would have done that, you might have caught it immediately, or we probably would if you posted that SQL. Dec 3, 2020 at 10:39
-1

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

1
  • Hello ! It didn't change the result. I guess the query is more secure now. But there is still an error Dec 3, 2020 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.