Dynamic databasename : no row inserted

• benkraiemchedlia

  SSCommitted

  Points: 1672

  More actions

  March 15, 2017 at 3:03 am

  #321342

  Hi, 
  I have a script with a table set from a variable, but the code is not working. 
  This is my code :

  DECLARE @ClientID int, @user-id int, @SurveyID int, @ApplicationID int, @link varchar(max) ,@SurveySessionID int
DECLARE @ApplicationName NVARCHAR(max), @ClientNameNVARCHAR(max), @UserName NVARCHAR(max), @UserMail varchar(max), @DBname nVARCHAR(MAX), @sql nvarchar(max), @UserType nvarchar(max), @ssID int
set @ClientID = 473
set @ApplicationID = 5
set @user-id = 474
set @SurveyID = 36
Set @UserType = 'User'
SELECT @ClientName = name
FROM
iQMGlobal.dbo.ClientEntities
WHERE
ID = @ClientID and ID IS NOT NULL

  SELECT @UserName = name, @UserMail = email
FROM
iQMGlobal.dbo.ClientEntities
WHERE
ID = @user-id and ID IS NOT NULL

   SELECT @ApplicationName = name, @DBname = DatabaseName
FROM
iQMGlobal.dbo.Applications
WHERE
ID = @ApplicationID and ID IS NOT NULL

  -- create temtable
CREATE TABLE #Base_ImportUsers
(
  [ClientName][NVARCHAR](255) NULL,
  [UserName]  [NVARCHAR](255) NULL,
  [Email]  [NVARCHAR](255) NULL,
  [Type] [NVARCHAR](255) NULL
)
INSERT INTO #Base_ImportUsers
(
[ClientName],
[UserName],
[Email],
[Type]
)
VALUES  
(@ClientName, @ClientName, N'', N'Client'),
(@ClientName, @UserName, @UserMail, N'User')

  -- end temptable
-------------------------------

  SET @sql = 'INSERT INTO '+@DBname+'.[dbo].[surveysessions]
(
surveyid,
stateid,
NAME,
cliententitymappingid,
createddate
)
SELECT
'+convert (varchar, @SurveyID)+',
   1,
   I.ClientName, 
   CEM.ID,
   Getdate()
FROM#Base_ImportUsers I
INNER JOIN [iQMGlobal].[dbo].[cliententities] ClientON Client.NAME = I.ClientName
INNER JOIN [iQMGlobal].[dbo].[applicationclientmappings] ACMON ACM.cliententityid = Client.id
INNER JOIN [iQMGlobal].[dbo].[cliententities] UON (I.email = U.login AND I.UserName = U.name)
INNER JOIN [iQMGlobal].[dbo].[cliententitymappings] CEM ON
CEM.[ClientEntityId] = U.ID
AND
CEM.[ParentId] = Client.ID
AND
CEM.[ApplicationId] = ACM.[ApplicationId]
INNER JOIN '+@DBname+'.[dbo].surveys S ON
S.ID = '+convert (varchar, @SurveyID)+'
LEFT JOIN '+@DBname+'.[dbo].[surveysessions] SS ON
SS.surveyid = '+convert (varchar, @SurveyID)+'
AND
SS.name = I.ClientName
AND
SS.cliententitymappingid = CEM.ID
WHERE
I.Type = '+@UserType+'
AND
SS.id IS NULL'
exec (@sql)

  ----------------------------------------------------------------------------------------------------------------------------------------------------------

  DROP TABLE #Base_ImportUsers [/code]
  
  tNo row inserted

  When I put the Databasename it works(in the code below) :
  
  ---USE [iQMGlobal]
--GO
--/****** Object: StoredProcedure [dbo].[CreateNewInstanceForExistiongClient]  Script Date: 3/14/2017 2:05:28 PM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

  --ALTER PROCEDURE [dbo].[CreateNewInstanceForExistiongClient]
declare @ClientID int, @user-id int, @SurveyID int, @ApplicationID int, @link varchar(max)

  DECLARE @ApplicationName NVARCHAR(max), @ClientNameNVARCHAR(max), @UserName NVARCHAR(max), @UserMail varchar(max), @UserType NVARCHAR(max), @SurveySessionID nvarchar(max), @DBname varchar(max)

  set @ClientID = 473
set @ApplicationID = 5
set @user-id = 474
set @SurveyID = 36
Set @UserType = 'User'

  SELECT @ClientName = name
FROM
iQMGlobal.dbo.ClientEntities
WHERE
ID = @ClientID and ID IS NOT NULL

  SELECT @UserName = name, @UserMail = email
FROM
iQMGlobal.dbo.ClientEntities
WHERE
ID = @user-id and ID IS NOT NULL

   SELECT @ApplicationName = name, @DBname = DatabaseName
FROM
iQMGlobal.dbo.Applications
WHERE
ID = @ApplicationID and ID IS NOT NULL

  CREATE TABLE #Base_ImportUsers
(
  [ClientName][NVARCHAR](255) NULL,
  [UserName]  [NVARCHAR](255) NULL,
  [Email]  [NVARCHAR](255) NULL,
  [Type] [NVARCHAR](255) NULL
)

  INSERT INTO #Base_ImportUsers
(
[ClientName],
[UserName],
[Email],
[Type]
)
VALUES  
(@ClientName, @ClientName, N'', N'Client'),
(@ClientName, @UserName, @UserMail, N'User')

  ------------------------------------------------
INSERT INTO iQMPlatform_Demo.[dbo].[surveysessions]
(
surveyid,
stateid,
NAME,
cliententitymappingid,
createddate
)
SELECT
@SurveyID,
   1,
   I.ClientName ,  -- Construct your own survey session name here.
   CEM.ID,
   Getdate()
FROM#Base_ImportUsers I
INNER JOIN [iQMGlobal].[dbo].[cliententities] ClientON Client.NAME = I.ClientName
INNER JOIN [iQMGlobal].[dbo].[applicationclientmappings] ACMON ACM.cliententityid = Client.id
INNER JOIN [iQMGlobal].[dbo].[cliententities] UON (I.email = U.login AND I.UserName = U.name)
INNER JOIN [iQMGlobal].[dbo].[cliententitymappings] CEM ON
CEM.[ClientEntityId] = U.ID
AND
CEM.[ParentId] = Client.ID
AND
CEM.[ApplicationId] = ACM.[ApplicationId]
INNER JOIN iQMPlatform_Demo.[dbo].surveys S ON
S.ID = @SurveyID
LEFT JOIN iQMPlatform_Demo.[dbo].[surveysessions] SS ON
SS.surveyid = @SurveyID
AND
SS.name = I.ClientName
AND
SS.cliententitymappingid = CEM.ID
WHERE
I.Type = @UserType
AND
SS.id IS NULL

  DROP TABLE #Base_ImportUsers [/code]
  
  thank you

• benkraiemchedlia

  SSCommitted

  Points: 1672

  More actions

  March 15, 2017 at 3:37 am

  #1933190

  I found it, for line 
  I.Type = '+@UserType+' it should be  I.Type = '''+@UserType+''' because I.Type = ''User' and not I.Type = 'User