November 28, 2011 at 3:02 am
Hi All,
I keep getting these error messages.
Invalid length parameter passed to the substring function OR conversion failed when converting the varchar value '' to type int.
declare @col1 char(50),@col2 char(50)....
while charindex(',',@MultiParam) <> 0
begin
set @col1 = substring(@MultiParam,1,charindex(',',@MultiParam)-1)
set @MultiParam = (select substring(@MultiParam,charindex(',',@MultiParam)+1,LEN(@MultiParam)))
All the tables columns has varchar values, I'm trying to execute a string of multiparameter values in SSRS.
Anyone that has some suggestions of what is wrong here? Any help would be much appreciated. I'm not so sure myself what the -1 and the +1 does, I haven't scripting for a while.
Thnks
/rz
November 28, 2011 at 4:44 am
the problem is the substring you are looking for does not exist in every field....you have to use a where statement or a case statement to filter them out. specifically, there's no comma in at least one of the fields.
declare @col1 char(50),@col2 char(50)....
while charindex(',',@MultiParam) <> 0
begin
set @col1 = CASE
WHEN charindex(',',@MultiParam) > 0
THEN substring(@MultiParam,1,charindex(',',@MultiParam)-1)
ELSE @MultiParam
END
set @MultiParam = (select CASE
WHEN charindex(',',@MultiParam) > 0
THEN substring(@MultiParam,charindex(',',@MultiParam)+1,LEN(@MultiParam))
ELSE @MultiParam
END)
Lowell
November 28, 2011 at 7:13 am
Hi SSCertifiable thanks for your respond. it is really appreciated. This thing is killing me.
I do have have problems still though when I execute this thing.
When I'm using your script it is finally writing down to the database but, the records slides in in different columns.
The first 5 of 7 columns are correct and then something happens. The records that are saved to the database are generated from a comma separated list in a dropdown list within sql server reporting services, the records from column 6 and 7 seems to be missing so instead half of the information from column 1 and 2 populate the columns 6 and 7. Depending on how many filter that I want to have. Any ideas how I go about this? Thx again.
When I execute the sproc in SSMS and hardcoding values, it has not problem to save the data. It's just in the SSRS environment that it doesn't save the data. Anyone that could give a pair of eyes here, I could certainly need it. Thanks so much in advance
/rz
Dataset 1
create procedure sproc
(@MultiParam nvarchar(max))
AS
---------------------------
Declare @t1 table(
col1 nchar(50) NOT NULL
,col2 nchar(50) NOT NULL
,col3 nchar(50) NOT NULL
,col4 nchar(4) NOT NULL
,col5 nchar(255) NOT NULL
)
insert into @t1
selectcol1
,col2
,col3
,col4
,col5
from table
where col1 <>''
group by col1.....
-------------------end-------------------------------
-------------------@t2-------------------------------
declare @t2 table(
col6 nchar(50) Not Null
,col7 nchar(50)Not Null
)
insert into @t2
selectcol6
,col7
from table2 a
inner join table b on a.col6 = b.col6
full outer join TableFilter c on b.col7 = c.col7
declare@col1(50), @col2 nchar(50),@col3 nchar(50),@col4 nchar(4),@col5 nchar(255),@col 6 nchar(50),@col7 nchar(50)
while charindex(',',@MultiParam) <> 0
begin
set @col1 =CASE
WHEN charindex(',',@MultiParam) > 0
THEN substring(@MultiParam,1,charindex(',',@MultiParam)-1)
ELSE @MultiParam
END
set @MultiParam = (select CASE
WHEN charindex(',',@MultiParam) > 0
THEN substring(@MultiParam,charindex(',',@MultiParam)+1,LEN(@MultiParam))
ELSE @MultiParam
END)
set @col2 = CASE
WHEN charindex(',',@MultiParam) > 0
THEN substring(@MultiParam,1,charindex(',',@MultiParam)-1)
ELSE @MultiParam
END
.....
if charindex(',',@MultiParam) <> 0
begin
set @col7 = CASE
WHEN charindex(',',@MultiParam) > 0
THEN substring(@MultiParam,1,charindex(',',@MultiParam)-1)
ELSE @MultiParam
END
set @MultiParam = (select CASE
WHEN charindex(',',@MultiParam) > 0
THEN substring(@MultiParam,charindex(',',@MultiParam)+1,LEN(@MultiParam))
ELSE @MultiParam
END)
end
else
begin
set @user_login = @MultiParam
end
-----------------------------------------------
--------------------looping end------------------------
--------------------insert into------------------------
if exists (select * from table where column1 = @col1)
delete tableFilter where column1 = @col1
else
insert into tableFilter (col1,col2,col3,col4,col5,col6,col7,col8)
selectcast(ltrim(rtrim(@col1)) as varchar),cast(ltrim(rtrim(@col2)) as varchar),cast(ltrim(rtrim(@col3)) as varchar),cast(ltrim(rtrim(@col4)) as varchar),cast(ltrim(rtrim(@col5))as varchar),cast(ltrim(rtrim(@col6))as varchar),cast(ltrim(rtrim(@col7))as varchar)
end
Dataset 2 "Dropdown list"
select cast(ltrim(rtrim(col1)) as varchar)+','+cast(ltrim(rtrim(col2)) as varchar)+','+cast(ltrim(rtrim(col3)) as varchar)+','+cast(ltrim(rtrim(col4)) as varchar)+','+cast(ltrim(rtrim(col5))as varchar) as ID
from table
where col1 <>''
group by col1,col2,col3,col4,col5,col6,col7
order by 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply