Single quote for cross apply to use dynamic DBname

• benkraiemchedlia

  SSCommitted

  Points: 1672

  More actions

  March 15, 2017 at 5:45 am

  #328347

  Hi, 
  I have a sql query with cross apply function, it works fine when I set statically the DBname, but When I try to put a dynamic DBname from a variable, I am lost with where to put quotes and the use of single or double for each case.
  this is the code with static DBname works well :
  
 DECLARE @surveyID INT, @statutID INT
SET @surveyID = 6
SET @statutID = 3
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 iQMPlatform.[dbo].[Answers] as a
cross apply (select
      stuff((select
         ',' + l.name
        from
         iQMPlatform.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 iQMPlatform.dbo.SurveySessions ss on a.sessionId = ss.id and ss.stateid=@statutID
inner join iQMPlatform.dbo.Questions as qst on a.questionId = qst.id
where qst.typeId='14' and entitytypeid='5' and qst.surveyId= @surveyID and (helper is null or helper = '')
and a.value is not null ) as tab
select * from #tblfullvalue
  
  [ And this is the new code with the quotes

  
declare @ssid varchar(max), @DBname varchar(max), @ApplicationID int, @qtype int, @entityType int, @sql varchar(max), @fullFiles varchar(max), @comma varchar(max), @varcharmax varchar(max), @dot varchar(max)
set @ApplicationID = 5
set @qtype = 14
set @ssid = 74
set @entityType = 5
set @comma = ','
set @varcharmax = 'varchar(max)'
set @dot = '.'
SET @DBname= (SELECT DatabaseName FROM [iQMGlobal].[dbo].[applications] WHERE id = @ApplicationID )
--IF OBJECT_ID('tempdb..#tblfullvalue') IS NOT NULL DROP TABLE #tblfullvaluefile
set @fullFiles = 'select * into #tblfullvaluefile 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
        '''+@comma+''' + l.name
        from
         '+@DBname+'.dbo.Files l
         inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],'''+@comma+''') ds
          on l.id = ds.Item
        order by
         ds.ItemNumber
        for xml path(''),type).value( '''+@dot+''' '''+@comma+''' '''+@varcharmax+'''),1,1,'')) ca(cValues)
inner join '+@DBname+'.dbo.SurveySessions ss on a.sessionId = ss.id
inner join '+@DBname+'.dbo.Questions qst on a.questionId = qst.id
where qst.typeId='+convert (varchar, @qtype)+'
and entitytypeid= '+convert (varchar, @entityType)+'
and ss.id = @ssid and (helper is null or helper = '')
and a.value is not null ) as tab'
exec (@fullFiles)

  and the error message is  :

  
  Msg 102, Level 15, State 1, Line 16
  Incorrect syntax near '.'.
  Msg 105, Level 15, State 1, Line 21
  Unclosed quotation mark after the character string ')
  and a.value is not null ) as tab'.  
  

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  March 15, 2017 at 6:00 am

  #1933213

  There's a technique to this.

  1) Start with your original SQL & do a global replace of ' with ''.

  2) Put a single ' at the start and end of the resultant string

  That's your string

  'DECLARE @surveyID INT, @statutID INT
SET @surveyID = 6
SET @statutID = 3
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 iQMPlatform.[dbo].[Answers] as a
cross apply (select
   stuff((select
    '','' + l.name
   from
   iQMPlatform.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 iQMPlatform.dbo.SurveySessions ss on a.sessionId = ss.id and ss.stateid=@statutID
inner join iQMPlatform.dbo.Questions as qst on a.questionId = qst.id
where qst.typeId=''14'' and entitytypeid=''5'' and qst.surveyId= @surveyID and (helper is null or helper = '''')
and a.value is not null ) as tab'

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 15, 2017 at 6:04 am

  #1933215

  Instead of using exec (@fullFiles), use: PRINT @fullFiles
  Then copy the code from the messages tab and find where you need to fix your quotes. You have some missing quotes (quotes that weren't escaped) and some additional quotes that were inserted where they didn't belong.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• benkraiemchedlia

  SSCommitted

  Points: 1672

  More actions

  March 16, 2017 at 6:24 am

  #1933444

  Phil Parkin - Wednesday, March 15, 2017 6:00 AM

  There's a technique to this.

  1) Start with your original SQL & do a global replace of ' with ''.

  2) Put a single ' at the start and end of the resultant string

  That's your string

  'DECLARE @surveyID INT, @statutID INT
SET @surveyID = 6
SET @statutID = 3
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 iQMPlatform.[dbo].[Answers] as a
cross apply (select
   stuff((select
    '','' + l.name
   from
   iQMPlatform.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 iQMPlatform.dbo.SurveySessions ss on a.sessionId = ss.id and ss.stateid=@statutID
inner join iQMPlatform.dbo.Questions as qst on a.questionId = qst.id
where qst.typeId=''14'' and entitytypeid=''5'' and qst.surveyId= @surveyID and (helper is null or helper = '''')
and a.value is not null ) as tab'

  I did it but I cannot use the temp table inside another t-sql quoted statement, it will say invalid object name #tblfullvalue
  this is the code :
  
Set @sql = '
select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvaluefile.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 #tblfullvaluefile
on #tblfullvaluefile.val = ans.value
Where qst.typeId='+convert (varchar, @qtype)+' and ss.id='+convert (varchar, @ssid)+' and ans.sessionId = '+convert (varchar, @ssid)+''

  exec (@sql)


• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  March 16, 2017 at 6:28 am

  #1933446

  benkraiemchedlia - Thursday, March 16, 2017 6:24 AM

  I did it but I cannot use the temp table inside another t-sql quoted statement, it will say invalid object name #tblfullvalue
  this is the code :
  
  Set @sql = '
  select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvaluefile.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 #tblfullvaluefile
  on #tblfullvaluefile.val = ans.value
  Where qst.typeId='+convert (varchar, @qtype)+' and ss.id='+convert (varchar, @ssid)+' and ans.sessionId = '+convert (varchar, @ssid)+''

  exec (@sql)


  No you didn't. Your code still includes single occurrences of single quotes, which I told you to replace with double occurrences of single quotes.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• benkraiemchedlia

  SSCommitted

  Points: 1672

  More actions

  March 16, 2017 at 7:11 am

  #1933454

  Phil Parkin - Thursday, March 16, 2017 6:28 AM

  benkraiemchedlia - Thursday, March 16, 2017 6:24 AM

  I edited my code to insert the resultunder a table, and I added the double quotes but the problem is still here, this is my edited code
  

  DECLARE @surveyID INT, @statutID INT, @sql varchar(max), @ssid varchar(max),@ApplicationID int, @DBname varchar(max), @qtype int
  DECLARE @fullFiles TABLE (val varchar(max), qst varchar(max), fullval varchar(max))
  SET @surveyID = 1
  SET @statutID = 3
  set @ssid = 74
  set @ApplicationID = 5
  set @qtype = 14
  SET @DBname= (SELECT DatabaseName FROM [iQMGlobal].[dbo].[applications] WHERE id = @ApplicationID )

  insert into @fullFiles
  '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=''14''
  and ss.id = ''+convert (varchar, @ssid)+''
  and (helper is null or helper = '''')
  and a.value is not null '

  --exec(@fullFiles)
  select * from @fullFiles

  Set @sql = '
  select qst.id as qid, ss.id as ssid, ss.surveyId, @fullFiles.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 '''+@fullFiles+'''
  on #tblfullvaluefile.val = ans.value
  Where qst.typeId='''+convert (varchar, @qtype)+''' and ss.id='''+convert (varchar, @ssid)+''' and ans.sessionId = '''+convert (varchar, @ssid)+''''

  exec (@sql)


  Please help
  Thanks

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  March 16, 2017 at 7:19 am

  #1933459

  benkraiemchedlia - Thursday, March 16, 2017 7:11 AM

  Phil Parkin - Thursday, March 16, 2017 6:28 AM

  benkraiemchedlia - Thursday, March 16, 2017 6:24 AM

  I edited my code to insert the resultunder a table, and I added the double quotes but the problem is still here, this is my edited code
  

  DECLARE @surveyID INT, @statutID INT, @sql varchar(max), @ssid varchar(max),@ApplicationID int, @DBname varchar(max), @qtype int
  DECLARE @fullFiles TABLE (val varchar(max), qst varchar(max), fullval varchar(max))
  SET @surveyID = 1
  SET @statutID = 3
  set @ssid = 74
  set @ApplicationID = 5
  set @qtype = 14
  SET @DBname= (SELECT DatabaseName FROM [iQMGlobal].[dbo].[applications] WHERE id = @ApplicationID )

  insert into @fullFiles
  '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=''14''
  and ss.id = ''+convert (varchar, @ssid)+''
  and (helper is null or helper = '''')
  and a.value is not null '

  --exec(@fullFiles)
  select * from @fullFiles

  Set @sql = '
  select qst.id as qid, ss.id as ssid, ss.surveyId, @fullFiles.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 '''+@fullFiles+'''
  on #tblfullvaluefile.val = ans.value
  Where qst.typeId='''+convert (varchar, @qtype)+''' and ss.id='''+convert (varchar, @ssid)+''' and ans.sessionId = '''+convert (varchar, @ssid)+''''

  exec (@sql)


  Please help
  Thanks

  Now your single quotes have changed to triple quotes. Is my explanation really that difficult to understand?
  Change this '
  To this ''
  Not to this '''

  So
  Select 'Fred'

  becomes
  Select ''Fred''

  Then add single quotes around the entire result:
  'Select ''Fred'''

  And that is your string.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• benkraiemchedlia

  SSCommitted

  Points: 1672

  More actions

  March 16, 2017 at 7:34 am

  #1933465

  when I follow the double quotes it' s not ok too
  from the fisrt line I have error 

  
insert into @fullFiles
'select
a.value as val,
qst.id as qst,
ca.cValues as fullval   .........

  error message

  Incorrect syntax near 'select
  a.value as val,
  qst.id as qst,
  ca.cValues as fullval