| Groups | Blog | Home |
sql server reporting services : multivalued parameters and SQL stored procedures
that multivalue params don't work with SQL stored procs: "The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure." I'm wondering if anybody's tried any workarounds to this limitation. I'm just grasping at straws here... maybe converting the parameter array in SSRS to XML and using that as an XML typed parameter to a stored proc? or maybe some trick using a CLR stored proc as a "helper" to get a stored proc a
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure. Let's say you have a Parameter called @MyParams Now you can map that parameter to a multi-value parameter but if in your stored procedure you try to do this: select * from sometable where somefield in (@MyParams) It won't work. Try it. Create a stored procedure and try to pass a multi-value parameter to the stored procedure. It won't work. What you can do is to have a string parameter that is passed as a multivalue parameter and then change the string into a table. This technique was told to me by SQL Server MVP, Erland Sommarskog For example I have done this inner join charlist_to_table(@STO,Default)f on b.sto = f.str So note this is NOT an issue with RS, it is strictly a stored procedure issue. Here is the function: CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) > states > that multivalue params don't work with SQL stored procs: > > "The data source cannot be a stored procedure. Reporting Services does not > support passing a multivalued parameter array to a stored procedure." > > I'm wondering if anybody's tried any workarounds to this limitation. I'm > just grasping at straws here... maybe converting the parameter array in > SSRS > to XML and using that as an XML typed parameter to a stored proc? or maybe > some trick using a CLR stored proc as a "helper" to get a stored proc a > table/array? Any thoughts?
Bruce-
Thanks for your reply. That's a pretty good workaround given what's at our disposal to use. I sure wish SQL would let you use a table variable as a parameter type for a stored proc, cause all the workarounds to this are a bit kludgy. Thanks for your help. [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> What doesn't work has nothing really to do with RS but has to do with Stored > > Procedures in SQL Server. You cannot do the following in a stored procedure. > > Let's say you have a Parameter called @MyParams > > Now you can map that parameter to a multi-value parameter but if in your > > stored procedure you try to do this: > > select * from sometable where somefield in (@MyParams) > > It won't work. Try it. Create a stored procedure and try to pass a > > multi-value parameter to the stored procedure. It won't work. > > What you can do is to have a string parameter that is passed as a multivalue > > parameter and then change the string into a table. > > This technique was told to me by SQL Server MVP, Erland Sommarskog > > For example I have done this > > inner join charlist_to_table(@STO,Default)f on b.sto = f.str > > So note this is NOT an issue with RS, it is strictly a stored procedure > > issue. > > Here is the function: > > CREATE FUNCTION charlist_to_table > > (@list ntext, > > @delimiter nchar(1) = N',') > > RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, > > str varchar(4000), > > nstr nvarchar(2000)) AS > > BEGIN > > DECLARE @pos int, > > @textpos int, > > @chunklen smallint, > > @tmpstr nvarchar(4000), > > @leftover nvarchar(4000), > > @tmpval nvarchar(4000) > > SET @textpos = 1 > > SET @leftover = '' > > WHILE @textpos <= datalength(@list) / 2 > > BEGIN > > SET @chunklen = 4000 - datalength(@leftover) / 2 > > SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) > > SET @textpos = @textpos + @chunklen > > SET @pos = charindex(@delimiter, @tmpstr) > > WHILE @pos > 0 > > BEGIN > > SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) > > INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) > > SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) > > SET @pos = charindex(@delimiter, @tmpstr) > > END > > SET @leftover = @tmpstr > > END > > INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), > > ltrim(rtrim(@leftover))) > > RETURN > > END > > GO > > -- > > Bruce Loehle-Conger > > MVP SQL Server Reporting Services > > "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message > news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... > > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) > > states > > that multivalue params don't work with SQL stored procs: > > > > "The data source cannot be a stored procedure. Reporting Services does not > > support passing a multivalued parameter array to a stored procedure." > > > > I'm wondering if anybody's tried any workarounds to this limitation. I'm > > just grasping at straws here... maybe converting the parameter array in > > SSRS > > to XML and using that as an XML typed parameter to a stored proc? or maybe > > some trick using a CLR stored proc as a "helper" to get a stored proc a > > table/array? Any thoughts? > >
Way too much code man...why not just use dynamic sql in the proc?
create prod MyProc @list varchar(1000) as declare @sql varchar(8000) set @sql = '' select @sql = 'Select col1, col2 from mytable where col1 in (' + @list + ')' exec (@sql) go I've done this in many cases where a user enters order numbers lets say that are seperated by commas... Just my thoughts on how to approach it... Cheers! -- Ben Sullins http://bensullins.com
Right....there is definantely room for the user to enter an invalid
list that might throw an exception or cause invalid results...if however they entered something like an order type seperated by spaces you could just do a replace on the @list /******************************************/ declare @list varchar(100) set @list = 'type1 Type2 Type3' select '''' + replace(@list,' ',''',''') + '''' /******************************************/ Something like that would produce 'type1','Type2','Type3' which should work in most cases I beleive...even if it was comparing numeric values... -- Ben Sullins http://bensullins.com
Beware of sql injection attacks, too... what's to keep a sly user from typing
in a parameter which is: /ReportServer?/MyReport¶m1='--exec xp_cmdshell 'del important file' But if you can parse out the CSV list like Bruce has done without ever executing the strings, that's safe. [quoted text, click to view] "sullins602" wrote:
> Way too much code man...why not just use dynamic sql in the proc? > > > create prod MyProc > @list varchar(1000) > as > > declare @sql varchar(8000) > set @sql = '' > > select @sql = 'Select col1, col2 from mytable where col1 in (' + @list > + ')' > exec (@sql) > > go > > I've done this in many cases where a user enters order numbers lets say > that are seperated by commas... > > Just my thoughts on how to approach it... > > Cheers! > -- > Ben Sullins > http://bensullins.com >
True...thats why I would prefer to require the user to enter a comma
seperated list and validate it first before actually executing any command... -- Ben Sullins http://bensullins.com
OK, assuming that it isn't character data or dates. It gets real messy real
quick. So, modify your code to loop through and split the list up (with an unknown number) and put in all the appopriate single quotes. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "sullins602" <ben.sullins@gmail.com> wrote in message news:1142444593.922029.206490@p10g2000cwp.googlegroups.com... > Way too much code man...why not just use dynamic sql in the proc? > > > create prod MyProc > @list varchar(1000) > as > > declare @sql varchar(8000) > set @sql = '' > > select @sql = 'Select col1, col2 from mytable where col1 in (' + @list > + ')' > exec (@sql) > > go > > I've done this in many cases where a user enters order numbers lets say > that are seperated by commas... > > Just my thoughts on how to approach it... > > Cheers! > -- > Ben Sullins > http://bensullins.com >
I dont think that helps me ... I am calling a db2 stored procedure.
[quoted text, click to view] "Bruce L-C [MVP]" wrote:
> What doesn't work has nothing really to do with RS but has to do with Stored > > Procedures in SQL Server. You cannot do the following in a stored procedure. > > Let's say you have a Parameter called @MyParams > > Now you can map that parameter to a multi-value parameter but if in your > > stored procedure you try to do this: > > select * from sometable where somefield in (@MyParams) > > It won't work. Try it. Create a stored procedure and try to pass a > > multi-value parameter to the stored procedure. It won't work. > > What you can do is to have a string parameter that is passed as a multivalue > > parameter and then change the string into a table. > > This technique was told to me by SQL Server MVP, Erland Sommarskog > > For example I have done this > > inner join charlist_to_table(@STO,Default)f on b.sto = f.str > > So note this is NOT an issue with RS, it is strictly a stored procedure > > issue. > > Here is the function: > > CREATE FUNCTION charlist_to_table > > (@list ntext, > > @delimiter nchar(1) = N',') > > RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, > > str varchar(4000), > > nstr nvarchar(2000)) AS > > BEGIN > > DECLARE @pos int, > > @textpos int, > > @chunklen smallint, > > @tmpstr nvarchar(4000), > > @leftover nvarchar(4000), > > @tmpval nvarchar(4000) > > SET @textpos = 1 > > SET @leftover = '' > > WHILE @textpos <= datalength(@list) / 2 > > BEGIN > > SET @chunklen = 4000 - datalength(@leftover) / 2 > > SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) > > SET @textpos = @textpos + @chunklen > > SET @pos = charindex(@delimiter, @tmpstr) > > WHILE @pos > 0 > > BEGIN > > SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) > > INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) > > SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) > > SET @pos = charindex(@delimiter, @tmpstr) > > END > > SET @leftover = @tmpstr > > END > > INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), > > ltrim(rtrim(@leftover))) > > RETURN > > END > > GO > > -- > > Bruce Loehle-Conger > > MVP SQL Server Reporting Services > > "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message > news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... > > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) > > states > > that multivalue params don't work with SQL stored procs: > > > > "The data source cannot be a stored procedure. Reporting Services does not > > support passing a multivalued parameter array to a stored procedure." > > > > I'm wondering if anybody's tried any workarounds to this limitation. I'm > > just grasping at straws here... maybe converting the parameter array in > > SSRS > > to XML and using that as an XML typed parameter to a stored proc? or maybe > > some trick using a CLR stored proc as a "helper" to get a stored proc a > > table/array? Any thoughts? > >
What format does the DB2 stored proc expect the parameter to be in? I'm not
familiar with DB2... does it accept arrays as parameters or something? [quoted text, click to view] "MJT" wrote:
> I dont think that helps me ... I am calling a db2 stored procedure. > > "Bruce L-C [MVP]" wrote: > > > What doesn't work has nothing really to do with RS but has to do with Stored > > > > Procedures in SQL Server. You cannot do the following in a stored procedure. > > > > Let's say you have a Parameter called @MyParams > > > > Now you can map that parameter to a multi-value parameter but if in your > > > > stored procedure you try to do this: > > > > select * from sometable where somefield in (@MyParams) > > > > It won't work. Try it. Create a stored procedure and try to pass a > > > > multi-value parameter to the stored procedure. It won't work. > > > > What you can do is to have a string parameter that is passed as a multivalue > > > > parameter and then change the string into a table. > > > > This technique was told to me by SQL Server MVP, Erland Sommarskog > > > > For example I have done this > > > > inner join charlist_to_table(@STO,Default)f on b.sto = f.str > > > > So note this is NOT an issue with RS, it is strictly a stored procedure > > > > issue. > > > > Here is the function: > > > > CREATE FUNCTION charlist_to_table > > > > (@list ntext, > > > > @delimiter nchar(1) = N',') > > > > RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, > > > > str varchar(4000), > > > > nstr nvarchar(2000)) AS > > > > BEGIN > > > > DECLARE @pos int, > > > > @textpos int, > > > > @chunklen smallint, > > > > @tmpstr nvarchar(4000), > > > > @leftover nvarchar(4000), > > > > @tmpval nvarchar(4000) > > > > SET @textpos = 1 > > > > SET @leftover = '' > > > > WHILE @textpos <= datalength(@list) / 2 > > > > BEGIN > > > > SET @chunklen = 4000 - datalength(@leftover) / 2 > > > > SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) > > > > SET @textpos = @textpos + @chunklen > > > > SET @pos = charindex(@delimiter, @tmpstr) > > > > WHILE @pos > 0 > > > > BEGIN > > > > SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) > > > > INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) > > > > SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) > > > > SET @pos = charindex(@delimiter, @tmpstr) > > > > END > > > > SET @leftover = @tmpstr > > > > END > > > > INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), > > > > ltrim(rtrim(@leftover))) > > > > RETURN > > > > END > > > > GO > > > > -- > > > > Bruce Loehle-Conger > > > > MVP SQL Server Reporting Services > > > > "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message > > news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... > > > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) > > > states > > > that multivalue params don't work with SQL stored procs: > > > > > > "The data source cannot be a stored procedure. Reporting Services does not > > > support passing a multivalued parameter array to a stored procedure." > > > > > > I'm wondering if anybody's tried any workarounds to this limitation. I'm > > > just grasping at straws here... maybe converting the parameter array in > > > SSRS > > > to XML and using that as an XML typed parameter to a stored proc? or maybe > > > some trick using a CLR stored proc as a "helper" to get a stored proc a > > > table/array? Any thoughts? > > > >
Actually ... after reading the books ... it looks like I am going to luck out
on this one. I mapped the parm in my stored procedure (on the dataset parameters tab) to this expression for my multi-value parm and it is working ProcParmName mapped to this expression =Join(Parameters!myParm.Value, ", ") [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> The same issue will most likely be true in db2. So, the issue is, if you can > pass a comma separated string to DB2 from a query editor and it works fine. > If it doesn't then most likely the issue is that the stored procedure does > not allow it. I don't know if DB2 has table data types. If not, then you > would have to create dynamic SQL which is a real pain. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "MJT" <MJT@discussions.microsoft.com> wrote in message > news:FE80A357-920F-43E0-B417-970C425C81D6@microsoft.com... > >I dont think that helps me ... I am calling a db2 stored procedure. > > > > "Bruce L-C [MVP]" wrote: > > > >> What doesn't work has nothing really to do with RS but has to do with > >> Stored > >> > >> Procedures in SQL Server. You cannot do the following in a stored > >> procedure. > >> > >> Let's say you have a Parameter called @MyParams > >> > >> Now you can map that parameter to a multi-value parameter but if in your > >> > >> stored procedure you try to do this: > >> > >> select * from sometable where somefield in (@MyParams) > >> > >> It won't work. Try it. Create a stored procedure and try to pass a > >> > >> multi-value parameter to the stored procedure. It won't work. > >> > >> What you can do is to have a string parameter that is passed as a > >> multivalue > >> > >> parameter and then change the string into a table. > >> > >> This technique was told to me by SQL Server MVP, Erland Sommarskog > >> > >> For example I have done this > >> > >> inner join charlist_to_table(@STO,Default)f on b.sto = f.str > >> > >> So note this is NOT an issue with RS, it is strictly a stored procedure > >> > >> issue. > >> > >> Here is the function: > >> > >> CREATE FUNCTION charlist_to_table > >> > >> (@list ntext, > >> > >> @delimiter nchar(1) = N',') > >> > >> RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, > >> > >> str varchar(4000), > >> > >> nstr nvarchar(2000)) AS > >> > >> BEGIN > >> > >> DECLARE @pos int, > >> > >> @textpos int, > >> > >> @chunklen smallint, > >> > >> @tmpstr nvarchar(4000), > >> > >> @leftover nvarchar(4000), > >> > >> @tmpval nvarchar(4000) > >> > >> SET @textpos = 1 > >> > >> SET @leftover = '' > >> > >> WHILE @textpos <= datalength(@list) / 2 > >> > >> BEGIN > >> > >> SET @chunklen = 4000 - datalength(@leftover) / 2 > >> > >> SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) > >> > >> SET @textpos = @textpos + @chunklen > >> > >> SET @pos = charindex(@delimiter, @tmpstr) > >> > >> WHILE @pos > 0 > >> > >> BEGIN > >> > >> SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) > >> > >> INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) > >> > >> SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) > >> > >> SET @pos = charindex(@delimiter, @tmpstr) > >> > >> END > >> > >> SET @leftover = @tmpstr > >> > >> END > >> > >> INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), > >> > >> ltrim(rtrim(@leftover))) > >> > >> RETURN > >> > >> END > >> > >> GO > >> > >> -- > >> > >> Bruce Loehle-Conger > >> > >> MVP SQL Server Reporting Services > >> > >> "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message > >> news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... > >> > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) > >> > states > >> > that multivalue params don't work with SQL stored procs: > >> > > >> > "The data source cannot be a stored procedure. Reporting Services does > >> > not > >> > support passing a multivalued parameter array to a stored procedure." > >> > > >> > I'm wondering if anybody's tried any workarounds to this limitation. > >> > I'm > >> > just grasping at straws here... maybe converting the parameter array in > >> > SSRS > >> > to XML and using that as an XML typed parameter to a stored proc? or > >> > maybe > >> > some trick using a CLR stored proc as a "helper" to get a stored proc a > >> > table/array? Any thoughts? > >> > >> > >> > >
The same issue will most likely be true in db2. So, the issue is, if you can
pass a comma separated string to DB2 from a query editor and it works fine. If it doesn't then most likely the issue is that the stored procedure does not allow it. I don't know if DB2 has table data types. If not, then you would have to create dynamic SQL which is a real pain. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "MJT" <MJT@discussions.microsoft.com> wrote in message news:FE80A357-920F-43E0-B417-970C425C81D6@microsoft.com... >I dont think that helps me ... I am calling a db2 stored procedure. > > "Bruce L-C [MVP]" wrote: > >> What doesn't work has nothing really to do with RS but has to do with >> Stored >> >> Procedures in SQL Server. You cannot do the following in a stored >> procedure. >> >> Let's say you have a Parameter called @MyParams >> >> Now you can map that parameter to a multi-value parameter but if in your >> >> stored procedure you try to do this: >> >> select * from sometable where somefield in (@MyParams) >> >> It won't work. Try it. Create a stored procedure and try to pass a >> >> multi-value parameter to the stored procedure. It won't work. >> >> What you can do is to have a string parameter that is passed as a >> multivalue >> >> parameter and then change the string into a table. >> >> This technique was told to me by SQL Server MVP, Erland Sommarskog >> >> For example I have done this >> >> inner join charlist_to_table(@STO,Default)f on b.sto = f.str >> >> So note this is NOT an issue with RS, it is strictly a stored procedure >> >> issue. >> >> Here is the function: >> >> CREATE FUNCTION charlist_to_table >> >> (@list ntext, >> >> @delimiter nchar(1) = N',') >> >> RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, >> >> str varchar(4000), >> >> nstr nvarchar(2000)) AS >> >> BEGIN >> >> DECLARE @pos int, >> >> @textpos int, >> >> @chunklen smallint, >> >> @tmpstr nvarchar(4000), >> >> @leftover nvarchar(4000), >> >> @tmpval nvarchar(4000) >> >> SET @textpos = 1 >> >> SET @leftover = '' >> >> WHILE @textpos <= datalength(@list) / 2 >> >> BEGIN >> >> SET @chunklen = 4000 - datalength(@leftover) / 2 >> >> SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) >> >> SET @textpos = @textpos + @chunklen >> >> SET @pos = charindex(@delimiter, @tmpstr) >> >> WHILE @pos > 0 >> >> BEGIN >> >> SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) >> >> INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) >> >> SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) >> >> SET @pos = charindex(@delimiter, @tmpstr) >> >> END >> >> SET @leftover = @tmpstr >> >> END >> >> INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), >> >> ltrim(rtrim(@leftover))) >> >> RETURN >> >> END >> >> GO >> >> -- >> >> Bruce Loehle-Conger >> >> MVP SQL Server Reporting Services >> >> "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message >> news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... >> > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) >> > states >> > that multivalue params don't work with SQL stored procs: >> > >> > "The data source cannot be a stored procedure. Reporting Services does >> > not >> > support passing a multivalued parameter array to a stored procedure." >> > >> > I'm wondering if anybody's tried any workarounds to this limitation. >> > I'm >> > just grasping at straws here... maybe converting the parameter array in >> > SSRS >> > to XML and using that as an XML typed parameter to a stored proc? or >> > maybe >> > some trick using a CLR stored proc as a "helper" to get a stored proc a >> > table/array? Any thoughts? >> >> >>
I changed the separator from ',' to '|' in the statement
=Join(Parameters!myPARM.value, '| ') and then put that in a textbox and it showed the values separated by a instead of a comma. I think your stored proc would have to be able to accept that [quoted text, click to view] "Chris" wrote:
> I was having the same problem with muti value parameter. Your solution > worked great for all my MVP's except for the one field that actually > contains a string of values that contain commas (i.e. The Company, LLC). > Does anyone know a way of specifying the seperator character to be a | pipe > or something else. > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message > news:OKni%23uIcGHA.3956@TK2MSFTNGP05.phx.gbl... > > The same issue will most likely be true in db2. So, the issue is, if you > > can pass a comma separated string to DB2 from a query editor and it works > > fine. If it doesn't then most likely the issue is that the stored > > procedure does not allow it. I don't know if DB2 has table data types. If > > not, then you would have to create dynamic SQL which is a real pain. > > > > > > -- > > Bruce Loehle-Conger > > MVP SQL Server Reporting Services > > > > "MJT" <MJT@discussions.microsoft.com> wrote in message > > news:FE80A357-920F-43E0-B417-970C425C81D6@microsoft.com... > >>I dont think that helps me ... I am calling a db2 stored procedure. > >> > >> "Bruce L-C [MVP]" wrote: > >> > >>> What doesn't work has nothing really to do with RS but has to do with > >>> Stored > >>> > >>> Procedures in SQL Server. You cannot do the following in a stored > >>> procedure. > >>> > >>> Let's say you have a Parameter called @MyParams > >>> > >>> Now you can map that parameter to a multi-value parameter but if in your > >>> > >>> stored procedure you try to do this: > >>> > >>> select * from sometable where somefield in (@MyParams) > >>> > >>> It won't work. Try it. Create a stored procedure and try to pass a > >>> > >>> multi-value parameter to the stored procedure. It won't work. > >>> > >>> What you can do is to have a string parameter that is passed as a > >>> multivalue > >>> > >>> parameter and then change the string into a table. > >>> > >>> This technique was told to me by SQL Server MVP, Erland Sommarskog > >>> > >>> For example I have done this > >>> > >>> inner join charlist_to_table(@STO,Default)f on b.sto = f.str > >>> > >>> So note this is NOT an issue with RS, it is strictly a stored procedure > >>> > >>> issue. > >>> > >>> Here is the function: > >>> > >>> CREATE FUNCTION charlist_to_table > >>> > >>> (@list ntext, > >>> > >>> @delimiter nchar(1) = N',') > >>> > >>> RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, > >>> > >>> str varchar(4000), > >>> > >>> nstr nvarchar(2000)) AS > >>> > >>> BEGIN > >>> > >>> DECLARE @pos int, > >>> > >>> @textpos int, > >>> > >>> @chunklen smallint, > >>> > >>> @tmpstr nvarchar(4000), > >>> > >>> @leftover nvarchar(4000), > >>> > >>> @tmpval nvarchar(4000) > >>> > >>> SET @textpos = 1 > >>> > >>> SET @leftover = '' > >>> > >>> WHILE @textpos <= datalength(@list) / 2 > >>> > >>> BEGIN > >>> > >>> SET @chunklen = 4000 - datalength(@leftover) / 2 > >>> > >>> SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) > >>> > >>> SET @textpos = @textpos + @chunklen > >>> > >>> SET @pos = charindex(@delimiter, @tmpstr) > >>> > >>> WHILE @pos > 0 > >>> > >>> BEGIN > >>> > >>> SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) > >>> > >>> INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) > >>> > >>> SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) > >>> > >>> SET @pos = charindex(@delimiter, @tmpstr) > >>> > >>> END > >>> > >>> SET @leftover = @tmpstr > >>> > >>> END > >>> > >>> INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), > >>> > >>> ltrim(rtrim(@leftover))) > >>> > >>> RETURN > >>> > >>> END > >>> > >>> GO > >>> > >>> -- > >>> > >>> Bruce Loehle-Conger > >>> > >>> MVP SQL Server Reporting Services > >>> > >>> "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message > >>> news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... > >>> > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) > >>> > states > >>> > that multivalue params don't work with SQL stored procs: > >>> > > >>> > "The data source cannot be a stored procedure. Reporting Services does > >>> > not > >>> > support passing a multivalued parameter array to a stored procedure." > >>> > > >>> > I'm wondering if anybody's tried any workarounds to this limitation. > >>> > I'm > >>> > just grasping at straws here... maybe converting the parameter array > >>> > in > >>> > SSRS > >>> > to XML and using that as an XML typed parameter to a stored proc? or > >>> > maybe > >>> > some trick using a CLR stored proc as a "helper" to get a stored proc > >>> > a > >>> > table/array? Any thoughts? > >>> > >>> > >>> > > > > > >
I was having the same problem with muti value parameter. Your solution
worked great for all my MVP's except for the one field that actually contains a string of values that contain commas (i.e. The Company, LLC). Does anyone know a way of specifying the seperator character to be a | pipe or something else. [quoted text, click to view] "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message news:OKni%23uIcGHA.3956@TK2MSFTNGP05.phx.gbl... > The same issue will most likely be true in db2. So, the issue is, if you > can pass a comma separated string to DB2 from a query editor and it works > fine. If it doesn't then most likely the issue is that the stored > procedure does not allow it. I don't know if DB2 has table data types. If > not, then you would have to create dynamic SQL which is a real pain. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "MJT" <MJT@discussions.microsoft.com> wrote in message > news:FE80A357-920F-43E0-B417-970C425C81D6@microsoft.com... >>I dont think that helps me ... I am calling a db2 stored procedure. >> >> "Bruce L-C [MVP]" wrote: >> >>> What doesn't work has nothing really to do with RS but has to do with >>> Stored >>> >>> Procedures in SQL Server. You cannot do the following in a stored >>> procedure. >>> >>> Let's say you have a Parameter called @MyParams >>> >>> Now you can map that parameter to a multi-value parameter but if in your >>> >>> stored procedure you try to do this: >>> >>> select * from sometable where somefield in (@MyParams) >>> >>> It won't work. Try it. Create a stored procedure and try to pass a >>> >>> multi-value parameter to the stored procedure. It won't work. >>> >>> What you can do is to have a string parameter that is passed as a >>> multivalue >>> >>> parameter and then change the string into a table. >>> >>> This technique was told to me by SQL Server MVP, Erland Sommarskog >>> >>> For example I have done this >>> >>> inner join charlist_to_table(@STO,Default)f on b.sto = f.str >>> >>> So note this is NOT an issue with RS, it is strictly a stored procedure >>> >>> issue. >>> >>> Here is the function: >>> >>> CREATE FUNCTION charlist_to_table >>> >>> (@list ntext, >>> >>> @delimiter nchar(1) = N',') >>> >>> RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, >>> >>> str varchar(4000), >>> >>> nstr nvarchar(2000)) AS >>> >>> BEGIN >>> >>> DECLARE @pos int, >>> >>> @textpos int, >>> >>> @chunklen smallint, >>> >>> @tmpstr nvarchar(4000), >>> >>> @leftover nvarchar(4000), >>> >>> @tmpval nvarchar(4000) >>> >>> SET @textpos = 1 >>> >>> SET @leftover = '' >>> >>> WHILE @textpos <= datalength(@list) / 2 >>> >>> BEGIN >>> >>> SET @chunklen = 4000 - datalength(@leftover) / 2 >>> >>> SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) >>> >>> SET @textpos = @textpos + @chunklen >>> >>> SET @pos = charindex(@delimiter, @tmpstr) >>> >>> WHILE @pos > 0 >>> >>> BEGIN >>> >>> SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) >>> >>> INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) >>> >>> SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) >>> >>> SET @pos = charindex(@delimiter, @tmpstr) >>> >>> END >>> >>> SET @leftover = @tmpstr >>> >>> END >>> >>> INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), >>> >>> ltrim(rtrim(@leftover))) >>> >>> RETURN >>> >>> END >>> >>> GO >>> >>> -- >>> >>> Bruce Loehle-Conger >>> >>> MVP SQL Server Reporting Services >>> >>> "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message >>> news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... >>> > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) >>> > states >>> > that multivalue params don't work with SQL stored procs: >>> > >>> > "The data source cannot be a stored procedure. Reporting Services does >>> > not >>> > support passing a multivalued parameter array to a stored procedure." >>> > >>> > I'm wondering if anybody's tried any workarounds to this limitation. >>> > I'm >>> > just grasping at straws here... maybe converting the parameter array >>> > in >>> > SSRS >>> > to XML and using that as an XML typed parameter to a stored proc? or >>> > maybe >>> > some trick using a CLR stored proc as a "helper" to get a stored proc >>> > a >>> > table/array? Any thoughts? >>> >>> >>> > >
Actually Bruce, my comments were meant for you.
I was having the same problem with muti value parameter. Your solution worked great for all my MVP's except for the one field that actually contains a string of values that contain commas (i.e. The Company, LLC). Does anyone know a way of specifying the seperator character to be a | pipe or something else. [quoted text, click to view] "Chris" <cexley@enableconsulting.com> wrote in message news:eRiNPZscGHA.1792@TK2MSFTNGP03.phx.gbl... >I was having the same problem with muti value parameter. Your solution > worked great for all my MVP's except for the one field that actually > contains a string of values that contain commas (i.e. The Company, LLC). > Does anyone know a way of specifying the seperator character to be a | > pipe > or something else. > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message > news:OKni%23uIcGHA.3956@TK2MSFTNGP05.phx.gbl... >> The same issue will most likely be true in db2. So, the issue is, if you >> can pass a comma separated string to DB2 from a query editor and it works >> fine. If it doesn't then most likely the issue is that the stored >> procedure does not allow it. I don't know if DB2 has table data types. If >> not, then you would have to create dynamic SQL which is a real pain. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "MJT" <MJT@discussions.microsoft.com> wrote in message >> news:FE80A357-920F-43E0-B417-970C425C81D6@microsoft.com... >>>I dont think that helps me ... I am calling a db2 stored procedure. >>> >>> "Bruce L-C [MVP]" wrote: >>> >>>> What doesn't work has nothing really to do with RS but has to do with >>>> Stored >>>> >>>> Procedures in SQL Server. You cannot do the following in a stored >>>> procedure. >>>> >>>> Let's say you have a Parameter called @MyParams >>>> >>>> Now you can map that parameter to a multi-value parameter but if in >>>> your >>>> >>>> stored procedure you try to do this: >>>> >>>> select * from sometable where somefield in (@MyParams) >>>> >>>> It won't work. Try it. Create a stored procedure and try to pass a >>>> >>>> multi-value parameter to the stored procedure. It won't work. >>>> >>>> What you can do is to have a string parameter that is passed as a >>>> multivalue >>>> >>>> parameter and then change the string into a table. >>>> >>>> This technique was told to me by SQL Server MVP, Erland Sommarskog >>>> >>>> For example I have done this >>>> >>>> inner join charlist_to_table(@STO,Default)f on b.sto = f.str >>>> >>>> So note this is NOT an issue with RS, it is strictly a stored procedure >>>> >>>> issue. >>>> >>>> Here is the function: >>>> >>>> CREATE FUNCTION charlist_to_table >>>> >>>> (@list ntext, >>>> >>>> @delimiter nchar(1) = N',') >>>> >>>> RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, >>>> >>>> str varchar(4000), >>>> >>>> nstr nvarchar(2000)) AS >>>> >>>> BEGIN >>>> >>>> DECLARE @pos int, >>>> >>>> @textpos int, >>>> >>>> @chunklen smallint, >>>> >>>> @tmpstr nvarchar(4000), >>>> >>>> @leftover nvarchar(4000), >>>> >>>> @tmpval nvarchar(4000) >>>> >>>> SET @textpos = 1 >>>> >>>> SET @leftover = '' >>>> >>>> WHILE @textpos <= datalength(@list) / 2 >>>> >>>> BEGIN >>>> >>>> SET @chunklen = 4000 - datalength(@leftover) / 2 >>>> >>>> SET @tmpstr = @leftover + substring(@list, @textpos, >>>> @chunklen) >>>> >>>> SET @textpos = @textpos + @chunklen >>>> >>>> SET @pos = charindex(@delimiter, @tmpstr) >>>> >>>> WHILE @pos > 0 >>>> >>>> BEGIN >>>> >>>> SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) >>>> >>>> INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) >>>> >>>> SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) >>>> >>>> SET @pos = charindex(@delimiter, @tmpstr) >>>> >>>> END >>>> >>>> SET @leftover = @tmpstr >>>> >>>> END >>>> >>>> INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), >>>> >>>> ltrim(rtrim(@leftover))) >>>> >>>> RETURN >>>> >>>> END >>>> >>>> GO >>>> >>>> -- >>>> >>>> Bruce Loehle-Conger >>>> >>>> MVP SQL Server Reporting Services >>>> >>>> "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message >>>> news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... >>>> > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) >>>> > states >>>> > that multivalue params don't work with SQL stored procs: >>>> > >>>> > "The data source cannot be a stored procedure. Reporting Services >>>> > does not >>>> > support passing a multivalued parameter array to a stored procedure." >>>> > >>>> > I'm wondering if anybody's tried any workarounds to this limitation. >>>> > I'm >>>> > just grasping at straws here... maybe converting the parameter array >>>> > in >>>> > SSRS >>>> > to XML and using that as an XML typed parameter to a stored proc? or >>>> > maybe >>>> > some trick using a CLR stored proc as a "helper" to get a stored proc >>>> > a >>>> > table/array? Any thoughts? >>>> >>>> >>>> >> >> > >
Here's what I've done and doesn't involve a bootload of code.
In my stored procedure I join to a function that returns a table. The function takes in a nvarchar(5000) which is a delimited string and a char(1) which is your delimiter. Choose whatever delimeter you want. Here is the function: Create Function dbo.func_StringArrayFromParsedString (@RepParm nvarchar(5000), @Delim char(1) = '~') Returns @Values TABLE (Param varchar(30)) As Begin Declare @chrind INT Declare @Piece nvarchar(30) Select @chrind = 1 While @chrind > 0 Begin Select @chrind = CHARINDEX(@Delim,@RepParm) If @chrind > 0 Select @Piece = LEFT(@RepParm, @chrind - 1) Else Select @Piece = @RepParm Insert @Values(Param) VALUES(Cast(@Piece as Varchar(30))) Select @RepParm = RIGHT(@RepParm, LEN(@RepParm) - @chrind) IF LEN(@RepParm) = 0 BREAK END RETURN END Just another point of view. [quoted text, click to view] "Chris" wrote:
> Actually Bruce, my comments were meant for you. > > > I was having the same problem with muti value parameter. Your solution > worked great for all my MVP's except for the one field that actually > contains a string of values that contain commas (i.e. The Company, LLC). > Does anyone know a way of specifying the seperator character to be a | pipe > or something else. > > "Chris" <cexley@enableconsulting.com> wrote in message > news:eRiNPZscGHA.1792@TK2MSFTNGP03.phx.gbl... > >I was having the same problem with muti value parameter. Your solution > > worked great for all my MVP's except for the one field that actually > > contains a string of values that contain commas (i.e. The Company, LLC). > > Does anyone know a way of specifying the seperator character to be a | > > pipe > > or something else. > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message > > news:OKni%23uIcGHA.3956@TK2MSFTNGP05.phx.gbl... > >> The same issue will most likely be true in db2. So, the issue is, if you > >> can pass a comma separated string to DB2 from a query editor and it works > >> fine. If it doesn't then most likely the issue is that the stored > >> procedure does not allow it. I don't know if DB2 has table data types. If > >> not, then you would have to create dynamic SQL which is a real pain. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "MJT" <MJT@discussions.microsoft.com> wrote in message > >> news:FE80A357-920F-43E0-B417-970C425C81D6@microsoft.com... > >>>I dont think that helps me ... I am calling a db2 stored procedure. > >>> > >>> "Bruce L-C [MVP]" wrote: > >>> > >>>> What doesn't work has nothing really to do with RS but has to do with > >>>> Stored > >>>> > >>>> Procedures in SQL Server. You cannot do the following in a stored > >>>> procedure. > >>>> > >>>> Let's say you have a Parameter called @MyParams > >>>> > >>>> Now you can map that parameter to a multi-value parameter but if in > >>>> your > >>>> > >>>> stored procedure you try to do this: > >>>> > >>>> select * from sometable where somefield in (@MyParams) > >>>> > >>>> It won't work. Try it. Create a stored procedure and try to pass a > >>>> > >>>> multi-value parameter to the stored procedure. It won't work. > >>>> > >>>> What you can do is to have a string parameter that is passed as a > >>>> multivalue > >>>> > >>>> parameter and then change the string into a table. > >>>> > >>>> This technique was told to me by SQL Server MVP, Erland Sommarskog > >>>> > >>>> For example I have done this > >>>> > >>>> inner join charlist_to_table(@STO,Default)f on b.sto = f.str > >>>> > >>>> So note this is NOT an issue with RS, it is strictly a stored procedure > >>>> > >>>> issue. > >>>> > >>>> Here is the function: > >>>> > >>>> CREATE FUNCTION charlist_to_table > >>>> > >>>> (@list ntext, > >>>> > >>>> @delimiter nchar(1) = N',') > >>>> > >>>> RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, > >>>> > >>>> str varchar(4000), > >>>> > >>>> nstr nvarchar(2000)) AS > >>>> > >>>> BEGIN > >>>> > >>>> DECLARE @pos int, > >>>> > >>>> @textpos int, > >>>> > >>>> @chunklen smallint, > >>>> > >>>> @tmpstr nvarchar(4000), > >>>> > >>>> @leftover nvarchar(4000), > >>>> > >>>> @tmpval nvarchar(4000) > >>>> > >>>> SET @textpos = 1 > >>>> > >>>> SET @leftover = '' > >>>> > >>>> WHILE @textpos <= datalength(@list) / 2 > >>>> > >>>> BEGIN > >>>> > >>>> SET @chunklen = 4000 - datalength(@leftover) / 2 > >>>> > >>>> SET @tmpstr = @leftover + substring(@list, @textpos, > >>>> @chunklen) > >>>> > >>>> SET @textpos = @textpos + @chunklen > >>>> > >>>> SET @pos = charindex(@delimiter, @tmpstr) > >>>> > >>>> WHILE @pos > 0 > >>>> > >>>> BEGIN > >>>> > >>>> SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) > >>>> > >>>> INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) > >>>> > >>>> SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) > >>>> > >>>> SET @pos = charindex(@delimiter, @tmpstr) > >>>> > >>>> END > >>>> > >>>> SET @leftover = @tmpstr > >>>> > >>>> END > >>>> > >>>> INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), > >>>> > >>>> ltrim(rtrim(@leftover))) > >>>> > >>>> RETURN > >>>> > >>>> END > >>>> > >>>> GO > >>>> > >>>> -- > >>>> > >>>> Bruce Loehle-Conger > >>>> > >>>> MVP SQL Server Reporting Services > >>>> > >>>> "FurmanGG" <FurmanGG@discussions.microsoft.com> wrote in message > >>>> news:E645D056-59DA-41EC-8B79-F0695B7BF927@microsoft.com... > >>>> > Books online (http://msdn2.microsoft.com/en-us/library/ms155917.aspx) > >>>> > states > >>>> > that multivalue params don't work with SQL stored procs: > >>>> > > >>>> > "The data source cannot be a stored procedure. Reporting Services > >>>> > does not > >>>> > support passing a multivalued parameter array to a stored procedure." > >>>> > > >>>> > I'm wondering if anybody's tried any workarounds to this limitation. > >>>> > I'm > >>>> > just grasping at straws here... maybe converting the parameter array > >>>> > in > >>>> > SSRS > >>>> > to XML and using that as an XML typed parameter to a stored proc? or > >>>> > maybe > >>>> > some trick using a CLR stored proc as a "helper" to get a stored proc > >>>> > a > >>>> > table/array? Any thoughts? > >>>> > >>>> > >>>> > >> > >> > > > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |