Use dynamic database name in t-sql statement

  • Hi,

    I am sorry because I have duplicated this topic, Some one else tried to help me but I couldn't make it work

    I have a script who retrieve data from cross apply table and insert it into a temp table, then I use this temp table in an inner join, my code works fine when I set the database name statically, but I need to put dynamic database names.

    I know that I need to put my code inside quotes and call exec, but I don't know where I should put my quotes to make it work.

    This is my initial code

     
      DECLARE @sql varchar(max), @ssid varchar(max), @qtype int, @ApplicationID int, @DBname varchar(max)
     
      set @ssid = 74
      set @qtype = 14
      -- @DBname will be the Database name that I will use instead of iQMPlatformDemo
      SET @DBname= (SELECT DatabaseName FROM [iQMGlobal].[dbo].[applications] WHERE id = @ApplicationID )
      --IF OBJECT_ID('tempdb..#tblfullvalue') IS NOT NULL DROP TABLE #tblfullvalue
      select * into #tblfullvalue from (
      select
      a.value as val,
      qst.id as qst,
      ca.cValues as fullval
      FROM iQMPlatformDemo.[dbo].[Answers] as a
      cross apply (select
        stuff((select
         ',' + l.name
        from
         iQMPlatformDemo.dbo.Files l
         inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
         on l.id = ds.Item
        order by
         ds.ItemNumber
        for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(cValues)
      inner join iQMPlatformDemo.dbo.SurveySessions ss
      on a.sessionId = ss.id
      inner join iQMPlatformDemo.dbo.Questions as qst
      on a.questionId = qst.id
      where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype))
      and ss.id = @ssid
      and (helper is null or helper = '')
      and a.value is not null ) as tab
     
     
      select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvalue.fullval as Response
      from iQMPlatformDemo.dbo.SurveySessions ss
      inner join iQMPlatformDemo.dbo.Questions qst
      on ss.surveyId = qst.surveyId
      inner join iQMPlatformDemo.dbo.Answers ans
      on ans.questionId = qst.id
      inner join #tblfullvalue
      on #tblfullvalue.val = ans.value
      Where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype)) and  ss.id=convert (varchar, @ssid)
      and ans.sessionId = convert (varchar, @ssid)
     
      drop table #tblfullvalue

     
     
    Please can you help me to make it work.
    thank you

  • I recommend you create a procedure in the master db that can automatically run in the context of any db you need.  Below is the code to create the proc.  Note that the proc name must begin with sp_.

    Here's the code to invoke the proc after it's created:

    EXEC iQMPlatformDemo.dbo.sp_your_proc_name_here
    EXEC someOtherDb.dbo.sp_your_proc_name_here
    --or, more generically:
    EXEC(@DBname + '.dbo.sp_your_proc_name_here')


    USE master;
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE PROCEDURE dbo.sp_your_proc_name_here
    AS
    SET NOCOUNT ON;

    DECLARE @sql varchar(max), @ssid varchar(max),
      @qtype int, @ApplicationID int

    set @ssid = 74
    set @qtype = 14
    IF OBJECT_ID('tempdb..#tblfullvalue') IS NOT NULL DROP TABLE #tblfullvalue
    select * into #tblfullvalue from (
    select
    a.value as val,
     qst.id as qst,
    ca.cValues as fullval
    FROM [dbo].[Answers] as a
    cross apply (select
      stuff((select
      ',' + l.name
      from
      dbo.Files l
      inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
      on l.id = ds.Item
      order by
      ds.ItemNumber
      for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(cValues)
    inner join dbo.SurveySessions ss
    on a.sessionId = ss.id
    inner join dbo.Questions as qst
    on a.questionId = qst.id
    where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype))
     and ss.id = @ssid
    and (helper is null or helper = '')
     and a.value is not null ) as tab

    select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvalue.fullval as Response
     from dbo.SurveySessions ss
    inner join dbo.Questions qst
     on ss.surveyId = qst.surveyId
     inner join dbo.Answers ans
     on ans.questionId = qst.id
     inner join #tblfullvalue
     on #tblfullvalue.val = ans.value
     Where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype)) and ss.id=convert (varchar, @ssid)
     and ans.sessionId = convert (varchar, @ssid)

     drop table #tblfullvalue
    GO
    EXEC sp_marksystemobject 'dbo.sp_your_proc_name_here'

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher - Thursday, March 16, 2017 12:17 PM

    I recommend you create a procedure in the master db that can automatically run in the context of any db you need.  Below is the code to create the proc.  Note that the proc name must begin with sp_.

    Here's the code to invoke the proc after it's created:

    EXEC iQMPlatformDemo.dbo.sp_your_proc_name_here
    EXEC someOtherDb.dbo.sp_your_proc_name_here
    --or, more generically:
    EXEC(@DBname + '.dbo.sp_your_proc_name_here')


    USE master;
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE PROCEDURE dbo.sp_your_proc_name_here
    AS
    SET NOCOUNT ON;

    DECLARE @sql varchar(max), @ssid varchar(max),
      @qtype int, @ApplicationID int

    set @ssid = 74
    set @qtype = 14
    IF OBJECT_ID('tempdb..#tblfullvalue') IS NOT NULL DROP TABLE #tblfullvalue
    select * into #tblfullvalue from (
    select
    a.value as val,
     qst.id as qst,
    ca.cValues as fullval
    FROM [dbo].[Answers] as a
    cross apply (select
      stuff((select
      ',' + l.name
      from
      dbo.Files l
      inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
      on l.id = ds.Item
      order by
      ds.ItemNumber
      for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(cValues)
    inner join dbo.SurveySessions ss
    on a.sessionId = ss.id
    inner join dbo.Questions as qst
    on a.questionId = qst.id
    where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype))
     and ss.id = @ssid
    and (helper is null or helper = '')
     and a.value is not null ) as tab

    select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvalue.fullval as Response
     from dbo.SurveySessions ss
    inner join dbo.Questions qst
     on ss.surveyId = qst.surveyId
     inner join dbo.Answers ans
     on ans.questionId = qst.id
     inner join #tblfullvalue
     on #tblfullvalue.val = ans.value
     Where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype)) and ss.id=convert (varchar, @ssid)
     and ans.sessionId = convert (varchar, @ssid)

     drop table #tblfullvalue
    GO
    EXEC sp_marksystemobject 'dbo.sp_your_proc_name_here'

    Hi,
    Thank you for the help, 
    The aim of this code was to create a stored procedure under iQMGlobal, even if I try to follow your idea when I try to run the script it says : Could not find stored procedure 'sp_marksystemobject'.

  • I would try using sp_MSForEachDB

  • Joe Torre - Thursday, March 16, 2017 2:11 PM

    I would try using sp_MSForEachDB

    Is there another way to solve this issue without creating system stored procedure please , because the DBname is an input parameter?

  • You don't need to create it, it still ships with SQL Server. You can use it to do what you want calling it from your code.

  • Can you please explain again what you want to accomplish?

  • Joe Torre - Thursday, March 16, 2017 2:44 PM

    Can you please explain again what you want to accomplish?

    I want to create a stored procedure with 2 inputs : database name and another variable @ssid
    in the first code I set @ssid = 74 and database name =  iQMPlatformDemo 
    and it contains joins between the above database name and the master database name iQMGlobal

  • What do you want the procedure to do? Insert some data in a table from tables in a set of databases on your SQL Server Instance? Please remember we know nothing about the databases on your server. I want to help but I'm not sure what you want.

  • Joe Torre - Thursday, March 16, 2017 3:05 PM

    What do you want the procedure to do? Insert some data in a table from tables in a set of databases on your SQL Server Instance? Please remember we know nothing about the databases on your server. I want to help but I'm not sure what you want.

    I have a master DB named iQMGlobal,
    and other Databases : iQMPlatformDemo , iQMPlatformDemo_1, iQMPlatformDemo_2... with the same structure, it means the same tables and stored procedure...
     In each Database we store the answers of the clients.
    the creation of this stored procedurewith 2 input parameters : the Database name (example : iQMPlatformDemo ), and the sessionID = @ssid , allow me to fetch information from both iQMGlobal and iQMPlatformDemo for this sessionid

  • benkraiemchedlia - Thursday, March 16, 2017 3:16 PM

    Joe Torre - Thursday, March 16, 2017 3:05 PM

    What do you want the procedure to do? Insert some data in a table from tables in a set of databases on your SQL Server Instance? Please remember we know nothing about the databases on your server. I want to help but I'm not sure what you want.

    I have a master DB named iQMGlobal,
    and other Databases : iQMPlatformDemo , iQMPlatformDemo_1, iQMPlatformDemo_2... with the same structure, it means the same tables and stored procedure...
     In each Database we store the answers of the clients.
    the creation of this stored procedurewith 2 input parameters : the Database name (example : iQMPlatformDemo ), and the sessionID = @ssid , allow me to fetch information from both iQMGlobal and iQMPlatformDemo for this sessionid

    Here is the begining of the stored procedure  (the syntax is wrong)


    USE [iQMGlobal]
    GO
    /****** Object: StoredProcedure [dbo].[sp_FetchSurveyFiles]  Script Date: 3/16/2017 9:11:32 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[sp_FetchSurveyFiles] @ApplicationID int,  @ssid varchar(max),
    AS
    DECLARE @sql varchar(max), @qtype int, @DBname varchar(max)

    set @qtype = 14
    SET @DBname= (SELECT DatabaseName FROM [iQMGlobal].[dbo].[applications] WHERE id = @ApplicationID )
    select * into #tblfullvalue from (
    select
    a.value as val,
    qst.id as qst,
    ca.cValues as fullval
    FROM          @DBname.[dbo].[Answers] as a
    cross apply (select
       stuff((select
        ',' + l.name
       from
      @DBname.dbo.Files l
        inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
        on l.id = ds.Item
       order by
        ds.ItemNumber
       for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(cValues)
    inner join          @DBname.dbo.SurveySessions ss
    on a.sessionId = ss.id
    inner join         @DBname.dbo.Questions as qst
    on a.questionId = qst.id
    where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype))
    and ss.id = @ssid
    and (helper is null or helper = '')
    and a.value is not null ) as tab

    select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvalue.fullval as Response
    from     @DBname.dbo.SurveySessions ss
    inner join @DBname .dbo.Questions qst
    on ss.surveyId = qst.surveyId
    inner join @DBname.dbo.Answers ans
    on ans.questionId = qst.id
    inner join #tblfullvalue
    on #tblfullvalue.val = ans.value
    Where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype)) and ss.id=convert (varchar, @ssid)
    and ans.sessionId = convert (varchar, @ssid)

    drop table #tblfullvalue

  • Are you inserting the data in from a query in each database, iQMPlatformDemo , iQMPlatformDemo_1, iQMPlatformDemo_2... into a table in iQMGlobal?
    Try:

    DECLARE
         @sql1 nvarchar(100)='USE ?'
       , @sql2 nvarchar(2000)='IF ''?'' IN (''iQMPlatformDemo'' , ''iQMPlatformDemo_1'', ''iQMPlatformDemo_2'')
       INSERT INTO iQMGlobal.dbo.tblResults (...) SELECT ...'
       , @replchar nchar(1)='?';
    EXEC sys.sp_MSforeachdb @command1 = @sql1, @replacechar = @replchar, @command2 = @sql2;


  • USE [iQMGlobal]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    CREATE PROCEDURE [dbo].[sp_FetchSurveyFiles]  @ApplicationID int,  @ssid varchar(100)
    AS
      
    DECLARE @sql varchar(max), @qtype char(2) = 14, @DBname sysname;
       SELECT
          @DBname= DatabaseName 
       FROM  [iQMGlobal].[dbo].[applications]
       WHERE id = @ApplicationID;
       SET @sql =
    "WITH
          fullvalue as
       (
          SELECT
             a.value as val,
             qst.id as qst,
             ca.cValues as fullval
          FROM         
          @DBname.[dbo].[Answers] as a
          CROSS APPLY
             (
             SELECT
                Stuff((select ',' + l.name
             FROM
                @DBname.dbo.Files l
             INNER JOIN
                [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
             ON l.id = ds.Item
             ORDER BY
             ds.ItemNumber
             FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
             ) ca(cValues)
          INNER JOIN
             "+@DBname+".dbo.SurveySessions ss ON a.sessionId = ss.id
          INNER JOIN
             "+@DBname+".dbo.Questions as qst ON a.questionId = qst.id
          WHERE
             qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), "+@qtype+"))
          AND ss.id = "+@ssid+"
          AND (helper is null or helper = '')
          AND a.value is not null
          )
       )
    SELECT
           qst.id as qid
         , ss.id as ssid
         , ss.surveyId
         , fullvalue.fullval as Response
    FROM
       "+@DBname+".dbo.SurveySessions ss
    INNER JOIN
       "+@DBname+".dbo.Questions qst ON ss.surveyId = qst.surveyId
    INNER JOIN
       "+@DBname+".dbo.Answers ans ON ans.questionId = qst.id
    INNER JOIN
       fullvalue ON fullvalue.val = ans.value
    WHERE
        qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), "+@qtype+"))
    AND ss.id=convert (varchar, "+@ssid+")
    AND ans.sessionId = convert (varchar, "+@ssid+")";
    EXEC(@sql);
    GO
  • Joe Torre - Thursday, March 16, 2017 3:57 PM


    USE [iQMGlobal]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    CREATE PROCEDURE [dbo].[sp_FetchSurveyFiles]  @ApplicationID int,  @ssid varchar(100)
    AS
      
    DECLARE @sql varchar(max), @qtype char(2) = 14, @DBname sysname;
       SELECT
          @DBname= DatabaseName 
       FROM  [iQMGlobal].[dbo].[applications]
       WHERE id = @ApplicationID;
       SET @sql =
    "WITH
          fullvalue as
       (
          SELECT
             a.value as val,
             qst.id as qst,
             ca.cValues as fullval
          FROM         
          @DBname.[dbo].[Answers] as a
          CROSS APPLY
             (
             SELECT
                Stuff((select ',' + l.name
             FROM
                @DBname.dbo.Files l
             INNER JOIN
                [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
             ON l.id = ds.Item
             ORDER BY
             ds.ItemNumber
             FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
             ) ca(cValues)
          INNER JOIN
             "+@DBname+".dbo.SurveySessions ss ON a.sessionId = ss.id
          INNER JOIN
             "+@DBname+".dbo.Questions as qst ON a.questionId = qst.id
          WHERE
             qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), "+@qtype+"))
          AND ss.id = "+@ssid+"
          AND (helper is null or helper = '')
          AND a.value is not null
          )
       )
    SELECT
           qst.id as qid
         , ss.id as ssid
         , ss.surveyId
         , fullvalue.fullval as Response
    FROM
       "+@DBname+".dbo.SurveySessions ss
    INNER JOIN
       "+@DBname+".dbo.Questions qst ON ss.surveyId = qst.surveyId
    INNER JOIN
       "+@DBname+".dbo.Answers ans ON ans.questionId = qst.id
    INNER JOIN
       fullvalue ON fullvalue.val = ans.value
    WHERE
        qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), "+@qtype+"))
    AND ss.id=convert (varchar, "+@ssid+")
    AND ans.sessionId = convert (varchar, "+@ssid+")";
    EXEC(@sql);
    GO

     I have an error message :

    Msg 102, Level 15, State 1, Line 22
    Incorrect syntax near '('.

  • Joe Torre - Thursday, March 16, 2017 3:31 PM

    Are you inserting the data in from a query in each database, iQMPlatformDemo , iQMPlatformDemo_1, iQMPlatformDemo_2... into a table in iQMGlobal?
    Try:

    DECLARE
         @sql1 nvarchar(100)='USE ?'
       , @sql2 nvarchar(2000)='IF ''?'' IN (''iQMPlatformDemo'' , ''iQMPlatformDemo_1'', ''iQMPlatformDemo_2'')
       INSERT INTO iQMGlobal.dbo.tblResults (...) SELECT ...'
       , @replchar nchar(1)='?';
    EXEC sys.sp_MSforeachdb @command1 = @sql1, @replacechar = @replchar, @command2 = @sql2;

    I am not inserting data, I want just to fetch data from Database. As I said the number of duplicated Databases maybe be greater then 3 (they may add iQMPlatformDemo_3, iQMPlatformDemo_4 .... )

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply