|
|
You're in the
sql server reporting services
group:Multi Value Parameters and Stored Procedures
sql server reporting services:
1: "exec @mvp" does not work because the MVP is recognized as more parameters due to the Commata in the string 2. The procedure cannot work with an "select * where parameter in (MVP)" when as string like 'a','b','c' is submitted to the procedure Does anyone have an idea how to solve this probelm?
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] "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message news:89009B54-C1C6-4E0B-910B-FE1809EAA5E7@microsoft.com... > Hi all, I want to submit a MVP from a Report to the SQL Server. 2 > Problems: > 1: "exec @mvp" does not work because the MVP is recognized as more > parameters due to the Commata in the string > > 2. The procedure cannot work with an "select * where parameter in (MVP)" > when as string like 'a','b','c' is submitted to the procedure > > Does anyone have an idea how to solve this probelm? > > Thanks, Roland
Hi Bruce, thanks a lot the procedure works!! But one problem reamains: How to
call the procedure from RS? "exec proc @mvp" results in an error (because through the commata in the @mvp it is interpreted as more parameters). Is there a special systax necessary? Thanks, Roland [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 > > "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message > news:89009B54-C1C6-4E0B-910B-FE1809EAA5E7@microsoft.com... > > Hi all, I want to submit a MVP from a Report to the SQL Server. 2 > > Problems: > > 1: "exec @mvp" does not work because the MVP is recognized as more > > parameters due to the Commata in the string > > > > 2. The procedure cannot work with an "select * where parameter in (MVP)" > > when as string like 'a','b','c' is submitted to the procedure > > > > Does anyone have an idea how to solve this probelm? > > > > Thanks, Roland > >
Hi Bruce, RS works fine but SQL does not: If I call "exec survey @surveyid,
@questionid, @type, @subtype, @category,0" with all or only one Question ( -> @questionid) it works. But when I select e.g 3 questions, SQL brings that message: "Procedure has to many arguments specified" The parameter @questinid looks this: "403a,487b,382c". SQL apparently cannot recognize this as one parameter :-( Thanks, Roland [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Going against SQL Server you should be able to select stored procedure as > the type and then just put in the name of the stored procedure. RS > recognizes the parameters of the stored procedure. > > If there is not a report parameter named the same it will create one. Then > you just switch the report parameter to multi-value. > > I do this and it will work against stored procedures. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message > news:1D7C6922-99E4-45A8-B3EB-E7945CC0797B@microsoft.com... > > Hi Bruce, thanks a lot the procedure works!! But one problem reamains: How > > to > > call the procedure from RS? "exec proc @mvp" results in an error (because > > through the commata in the @mvp it is interpreted as more parameters). Is > > there a special systax necessary? Thanks, Roland > > "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 > >> > >> "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message > >> news:89009B54-C1C6-4E0B-910B-FE1809EAA5E7@microsoft.com... > >> > Hi all, I want to submit a MVP from a Report to the SQL Server. 2 > >> > Problems: > >> > 1: "exec @mvp" does not work because the MVP is recognized as more > >> > parameters due to the Commata in the string > >> > > >> > 2. The procedure cannot work with an "select * where parameter in > >> > (MVP)" > >> > when as string like 'a','b','c' is submitted to the procedure > >> > > >> > Does anyone have an idea how to solve this probelm? > >> > > >> > Thanks, Roland > >> > >> > >> > >
Going against SQL Server you should be able to select stored procedure as
the type and then just put in the name of the stored procedure. RS recognizes the parameters of the stored procedure. If there is not a report parameter named the same it will create one. Then you just switch the report parameter to multi-value. I do this and it will work against stored procedures. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message news:1D7C6922-99E4-45A8-B3EB-E7945CC0797B@microsoft.com... > Hi Bruce, thanks a lot the procedure works!! But one problem reamains: How > to > call the procedure from RS? "exec proc @mvp" results in an error (because > through the commata in the @mvp it is interpreted as more parameters). Is > there a special systax necessary? Thanks, Roland > "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 >> >> "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message >> news:89009B54-C1C6-4E0B-910B-FE1809EAA5E7@microsoft.com... >> > Hi all, I want to submit a MVP from a Report to the SQL Server. 2 >> > Problems: >> > 1: "exec @mvp" does not work because the MVP is recognized as more >> > parameters due to the Commata in the string >> > >> > 2. The procedure cannot work with an "select * where parameter in >> > (MVP)" >> > when as string like 'a','b','c' is submitted to the procedure >> > >> > Does anyone have an idea how to solve this probelm? >> > >> > Thanks, Roland >> >> >>
You hero :-) Sorry, but now I have checked it! It works fantastic !!!!!!!
Thanka a lost and best regards, Roland [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> How is @questinid declared? > > I have a stored procedure with 4 multi-valued parameters (as well as several > other parameters). I declare these parameters as varchar(255) > > Again, I am not calling the stored procedure they way you are. It could be > that is what makes a difference. > > Go to your dataset declaration. Change the commandtype to stored procedure > and then have this in the pane: > > survey > > That is it, DO NOT list your parameters. RS automatically gets your > parameters from the stored procedure. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message > news:3C7500C0-2E19-4C95-8DA1-026FCC0DB78C@microsoft.com... > > Hi Bruce, RS works fine but SQL does not: If I call "exec survey > > @surveyid, > > @questionid, @type, @subtype, @category,0" with all or only one Question > > ( -> > > @questionid) it works. But when I select e.g 3 questions, SQL brings that > > message: > > "Procedure has to many arguments specified" The parameter @questinid looks > > this: "403a,487b,382c". SQL apparently cannot recognize this as one > > parameter > > :-( > > > > Thanks, Roland > > > > "Bruce L-C [MVP]" wrote: > > > >> Going against SQL Server you should be able to select stored procedure as > >> the type and then just put in the name of the stored procedure. RS > >> recognizes the parameters of the stored procedure. > >> > >> If there is not a report parameter named the same it will create one. > >> Then > >> you just switch the report parameter to multi-value. > >> > >> I do this and it will work against stored procedures. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message > >> news:1D7C6922-99E4-45A8-B3EB-E7945CC0797B@microsoft.com... > >> > Hi Bruce, thanks a lot the procedure works!! But one problem reamains: > >> > How > >> > to > >> > call the procedure from RS? "exec proc @mvp" results in an error > >> > (because > >> > through the commata in the @mvp it is interpreted as more parameters). > >> > Is > >> > there a special systax necessary? Thanks, Roland > >> > "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 > >> >> > >> >> "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in > >> >> message > >> >> news:89009B54-C1C6-4E0B-910B-FE1809EAA5E7@microsoft.com... > >> >> > Hi all, I want to submit a MVP from a Report to the SQL Server. 2 > >> >> > Problems: > >> >> > 1: "exec @mvp" does not work because the MVP is recognized as more > >> >> > parameters due to the Commata in the string > >> >> > > >> >> > 2. The procedure cannot work with an "select * where parameter in > >> >> > (MVP)" > >> >> > when as string like 'a','b','c' is submitted to the procedure > >> >> > > >> >> > Does anyone have an idea how to solve this probelm? > >> >> > > >> >> > Thanks, Roland > >> >> > >> >> > >> >> > >> > >> > >> > >
How is @questinid declared?
I have a stored procedure with 4 multi-valued parameters (as well as several other parameters). I declare these parameters as varchar(255) Again, I am not calling the stored procedure they way you are. It could be that is what makes a difference. Go to your dataset declaration. Change the commandtype to stored procedure and then have this in the pane: survey That is it, DO NOT list your parameters. RS automatically gets your parameters from the stored procedure. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message news:3C7500C0-2E19-4C95-8DA1-026FCC0DB78C@microsoft.com... > Hi Bruce, RS works fine but SQL does not: If I call "exec survey > @surveyid, > @questionid, @type, @subtype, @category,0" with all or only one Question > ( -> > @questionid) it works. But when I select e.g 3 questions, SQL brings that > message: > "Procedure has to many arguments specified" The parameter @questinid looks > this: "403a,487b,382c". SQL apparently cannot recognize this as one > parameter > :-( > > Thanks, Roland > > "Bruce L-C [MVP]" wrote: > >> Going against SQL Server you should be able to select stored procedure as >> the type and then just put in the name of the stored procedure. RS >> recognizes the parameters of the stored procedure. >> >> If there is not a report parameter named the same it will create one. >> Then >> you just switch the report parameter to multi-value. >> >> I do this and it will work against stored procedures. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in message >> news:1D7C6922-99E4-45A8-B3EB-E7945CC0797B@microsoft.com... >> > Hi Bruce, thanks a lot the procedure works!! But one problem reamains: >> > How >> > to >> > call the procedure from RS? "exec proc @mvp" results in an error >> > (because >> > through the commata in the @mvp it is interpreted as more parameters). >> > Is >> > there a special systax necessary? Thanks, Roland >> > "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 >> >> >> >> "Roland Müller" <RolandMller@discussions.microsoft.com> wrote in >> >> message >> >> news:89009B54-C1C6-4E0B-910B-FE1809EAA5E7@microsoft.com... >> >> > Hi all, I want to submit a MVP from a Report to the SQL Server. 2 >> >> > Problems: >> >> > 1: "exec @mvp" does not work because the MVP is recognized as more >> >> > parameters due to the Commata in the string >> >> > >> >> > 2. The procedure cannot work with an "select * where parameter in >> >> > (MVP)" >> >> > when as string like 'a','b','c' is submitted to the procedure >> >> > >> >> > Does anyone have an idea how to solve this probelm? >> >> > >> >> > Thanks, Roland >> >> >> >> >> >> >> >> >>
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 |