INSERT INTO @Temp_Table VALUES from multi value SSRS Parameter

Question

i'm building a report using ssrs report builder where in the code there is a part where i have to insert into a temp table some values. these values should be loaded from a multi value parameter DatabaseName. i tried the below code:

DECLARE @Rep_Temp TABLE(SurrogateKeyIDENTITY int not null IDENTITY (1,1),tempDBName nvarchar(100) NOT NULL);
INSERT INTO @Rep_Temp (tempDBName) VALUES (@DatabaseName);

it works only if i select one value, when i select multiple value it will give error.

i also tried the below code with no success:

INSERT INTO @Rep_Temp (tempDBName) VALUES (join(Parameters!DatabaseName.Value,","));

appreciate your assistance.

Best regards,

Answer 1

Hi can you try this solution? Turning a multi-value parameter into a temp table in SQL Server Business Intelligence Development Studio

Answer 2

I solve it as per the below:

1. I added Parameter in the dataset: @DBNameString = join(Parameters!DatabaseName.Value,",")
2. I tried to use STRING_SPLIT when inserting the table but i couldn't due to the fact that i have SQL Server 2012 "is not a recognized built-in function name". instead i did the following:

DECLARE @Rep_Temp TABLE(SurrogateKeyIDENTITY int not null IDENTITY (1,1),tempDBName nvarchar(100) NOT NULL);

DECLARE @DBs VARCHAR(500);
DECLARE @DBName VARCHAR(500);
DECLARE @charSpliter CHAR;

SET @charSpliter = ','
SET @DBs = @DBNameString + @charSpliter;

WHILE CHARINDEX(@charSpliter, @DBs) > 0
BEGIN
SET @DBName = SUBSTRING(@DBs, 0, CHARINDEX(@charSpliter, @DBs))
SET @DBs = SUBSTRING(@DBs, CHARINDEX(@charSpliter, @DBs) + 1, LEN(@DBs))

INSERT INTO @Rep_Temp (tempDBName) VALUES (@DBName);    
END 

Best Regards,