Can't figure out how to pass a comma delimeted value?

I am trying to pass a comma delimited value from an input box "attempting to
allow for multple entries within a string" and have the results returned from
the report. The stored procedure that I created will allow me to get what I
want but when I try to make the report make use of the sproc from within a
parameter I get nothing returned if I enter multiple values seperated by a
comma into the input box. If I put in just a single entry, it works.
Again, when I run the stored procedure within the database it works with
multiple values, when I run and test within the data view in the report it
runs and accepts multiple entries when prompted. Only when I run the report
does it fail?
Here is the sproc...
USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[usp_TestRprtArray]
@.subscriberarray varchar(500)
as
declare @.sql varchar(8000)
set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
+ "))"
exec (@.sql)
--
Anthony E. Castro - MCDBAIf the comma seperated value is passed from thr UI screen of a web
page.. try url encoding the page...
acmcdba68 wrote:
> I am trying to pass a comma delimited value from an input box "attempting to
> allow for multple entries within a string" and have the results returned from
> the report. The stored procedure that I created will allow me to get what I
> want but when I try to make the report make use of the sproc from within a
> parameter I get nothing returned if I enter multiple values seperated by a
> comma into the input box. If I put in just a single entry, it works.
> Again, when I run the stored procedure within the database it works with
> multiple values, when I run and test within the data view in the report it
> runs and accepts multiple entries when prompted. Only when I run the report
> does it fail?
> Here is the sproc...
> USE [mydatabase]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[usp_TestRprtArray]
> @.subscriberarray varchar(500)
> as
> declare @.sql varchar(8000)
> set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
> set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
> EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
> + "))"
> exec (@.sql)
> --
> Anthony E. Castro - MCDBA|||try putting in a textbox in your report that shows what parameter RS is
sending your sproc. that can be helpfule in seeing just what is passing in
the back end. Are you using a Multi-value parameter box? that format is
usually '<value>,<value>,...'
What works for me is to loop through the string, parsing out each value and
inserting the results into a temp table that can then be sorted, filtered,
etc as you like.
"acmcdba68" wrote:
> I am trying to pass a comma delimited value from an input box "attempting to
> allow for multple entries within a string" and have the results returned from
> the report. The stored procedure that I created will allow me to get what I
> want but when I try to make the report make use of the sproc from within a
> parameter I get nothing returned if I enter multiple values seperated by a
> comma into the input box. If I put in just a single entry, it works.
> Again, when I run the stored procedure within the database it works with
> multiple values, when I run and test within the data view in the report it
> runs and accepts multiple entries when prompted. Only when I run the report
> does it fail?
> Here is the sproc...
> USE [mydatabase]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[usp_TestRprtArray]
> @.subscriberarray varchar(500)
> as
> declare @.sql varchar(8000)
> set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
> set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
> EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
> + "))"
> exec (@.sql)
> --
> Anthony E. Castro - MCDBA