Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2011
    Posts
    4

    Unanswered: Help formatting a variable's contents for a SQL Select statement

    Here's a bit of background. I have a SSRS report running that has the ability for users to select multiple values. When SSRS passes this parameter to my SQL sp, it passes a single string that is enclosed in quotes (as oposed to enclosing each value in this string in quotes, its a shortcoming of SSRS). Hence the following string:

    'Annual Giving 2008, Annual Giving 2009, Annual Giving 2010'

    This value is used in the following statement in my sp:

    Code:
    SELECT * FROM #Fundraising
    WHERE Fund IN (@Fund)

    SQL will not return any results since @Fund does not have quotes around every value. In order for this to work correctly, @Fund needs to have the following value:

    'Annual Giving 2008','Annual Giving 2009','Annual Giving 2010'

    I hope that makes sense, let me know if I haven't clarified something. I'm looking for a way to get from point a to b, i.e., enclosing each of the values in quotes as ooposed to just having a pair of quotes around the whole string.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Try: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/1ef72267-f727-417f-80d8-593bcffb45bd.htm

    Single and Multivalued Parameters
    You can define a multivalued parameter by selecting the Multivalued option in the Report Parameters dialog box. If this checkbox is not selected, the parameter is single-valued. Specifying the Multivalued option adds checkboxes to the available values in a parameter drop-down list in the published report. Users can check the values they want (for example, in the Sales Reason Comparisons sample report, users can select multiple products to view the combined sales data for those products). A (Select All) option is added to the available values list automatically for parameters that are defined as multivalued.

    When specifying the available values, you can provide a static list of strings or use a query to retrieve available values from a data source. A multivalued parameter must include at least one value. Null values are not allowed.

    Writing Queries that Map to Multivalued Report Parameters
    You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:

    The data source must be SQL Server, Oracle, or Analysis Services.


    The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.


    The query must use an IN statement to specify the parameter. <--
    Last edited by corncrowe; 01-17-11 at 05:45.

  3. #3
    Join Date
    Oct 2009
    Posts
    27
    I don't know much about SSRS,

    But you can try this query,

    CREATE TABLE #Fund(FundName VARCHAR(100))
    INSERT INTO #Fund(FundName)
    SELECT LTRIM(Keyword)
    FROM f_Split(@Var,',')

    SELECT * FROM #Fund

    SELECT * FROM #Fundraising
    WHERE Fund IN (SELECT FundName FROM #Fund)

    DROP TABLE #Fund

    Here is the f_Split function.


    CREATE FUNCTION [dbo].[f_Split]
    (
    @Keyword VARCHAR(8000),
    @Delimiter VARCHAR(255)
    )
    RETURNS @SplitKeyword TABLE (Keyword VARCHAR(8000))
    AS
    BEGIN
    DECLARE @Word VARCHAR(255)
    DECLARE @TempKeyword TABLE (Keyword VARCHAR(8000))

    WHILE (CHARINDEX(@Delimiter, @Keyword, 1)>0)
    BEGIN
    SET @Word = SUBSTRING(@Keyword, 1 , CHARINDEX(@Delimiter, @Keyword, 1) - 1)
    SET @Keyword = SUBSTRING(@Keyword, CHARINDEX(@Delimiter, @Keyword, 1) + 1, LEN(@Keyword))
    INSERT INTO @TempKeyword VALUES(@Word)
    END

    INSERT INTO @TempKeyword VALUES(@Keyword)

    INSERT @SplitKeyword
    SELECT * FROM @TempKeyword
    RETURN
    END

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    croncrowe's solution sounds like the best option. But if you have to do it in SQL, you can use the REPLACE function.
    Code:
    WITH TEST_TAB(COL1)
      AS (SELECT '''ABC DEF, GHIJKL, MN OP QR''')
    SELECT COL1
         , REPLACE(COL1, ', ', ''', ''')
    FROM TEST_TAB
    The only thing you have to watch out for is spaces before and/or after the comma separating items. And a single space before and/or after the comma can be dealt with by nesting the REPLACE function.
    Code:
    REPLACE(REPLACE(REPLACE(COL1, ', ', ','), ' ,', ','), ',', ''',''')

  5. #5
    Join Date
    Jan 2011
    Posts
    4
    I ended up going with corncrowe's statement. Having the function created in SQL was eating into processing time for the report and was annoying because of the number of adjustments I had to make to get the string to be passed back to the procedure correctly. So I dropped my procedure and function, and instead used a query embedded in the SSRS report.

    It works like a charm, without the need for any gymnastics in SQL. The string is passed through perfectly. I've learnt to try not to use stored procedures for multi value parameters in future.

    Thanks for all the input, it is much appreciated

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by mpartridge View Post
    ...annoying because of the number of adjustments I had to make to get the string to be passed back to the procedure correctly.......

    It works like a charm, without the need for any gymnastics in SQL. The string is passed through perfectly. I've learnt to try not to use stored procedures for multi value parameters in future.

    Thanks for all the input, it is much appreciated
    I found building the sql 'where' clause much easier to do in the gui than sql, but not to say it can't be done. Whenever possible, I use the application layer to do this type of work 'cause the sql sproc gets really messy with lots of string manipulations. Besides, sometimes the datatype isn't a string...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •