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,
I solve it as per the below:
@DBNameString = join(Parameters!DatabaseName.Value,",")
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,