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,

score 0 · Answer 1 · answered Dec 30 '19 at 14:29

0

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

answered Dec 30 '19 at 14:29

Atahan Ceylan

106
2
5

i tried it, i got error Invalid Object name 'dbo.splitstring', in the example they are using visual studio while i am using report builder 3.0, can you kindly advise the correct syntax? – Georges Sabbagh Dec 30 '19 at 15:20
DECLARE `@dbname NVARCHAR(100) SET @dbname = 'DB1,DB2,DB3' DECLARE `@Rep_Temp TABLE(SurrogateKeyIDENTITY int not null IDENTITY (1,1),tempDBName nvarchar(100) NOT NULL); INSERT INTO `@Rep_Temp (tempDBName) select(@dbname) this inserts multiple values into temp table – Atahan Ceylan Dec 30 '19 at 16:23
I also suggest to try this solution. Maybe you can use =join(Parameters!.Value,",") In your query, you can then reference the value like so: where yourColumn in (@)https://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services – Atahan Ceylan Dec 30 '19 at 16:24
@ Atahan Ceylan, it will work only when @dbname contain one value example set @dbname='DB1', if i put multiple value i get error Incorrect syntax near ',' – Georges Sabbagh Dec 31 '19 at 07:40

score 0 · Accepted Answer · answered Dec 31 '19 at 10:04

I solve it as per the below:

I added Parameter in the dataset: @DBNameString = join(Parameters!DatabaseName.Value,",")
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,

INSERT INTO @Temp_Table VALUES from multi value SSRS Parameter

2 Answers2