Use dynamic database name in t-sql statement

Question

Use dynamic database name in t-sql statement

Viewing 3 posts - 16 through 17 (of 17 total)

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

benkraiemchedlia SSCommitted Points: 1672 More actions · Answer 1

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

It works now if I dont create a Stored procedure, When I create the stored procedure I have : SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. ..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

It's almost certainly too late now, but if you are faced with this kind of issue in the future, consider merging the iQMPlatformDemo databases together. Use an additional column in each of the tables, call it [InputSource] or [Client] or whatever and populate it with something you can use to uniquely identify each client. You will then be able to interrogate data for one or more clients without jumping through hoops.
This exercise you are attempting to get to work could get very much more complex as it is.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101091 More actions · Answer 3

Very sorry, sp_marksystemobject is my own version of the true MS proc.

You should run this:
USE master
EXEC sys.sp_MS_marksystemobject 'dbo.sp_your_proc_name_here'

The point of creating it in master is that the dbname then does not need to be a parameter, you can just run in the context of that db. Therefore you don't need dynamic SQL, which is easier to code and has fewer security issues.

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".