Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Pass multi-values in a single parameter for store procedure!

    Hello all,

    I got headache issue when passing multiple values to a single parameter. Here is my stored procedure as following:

    CREATE PROCEDURE [dbo].[School]
    @Grade AS varchar(50),
    @Class As varchar(50)
    AS
    BEGIN
    SELECT Name, Grade, Class
    FROM School
    WHERE Grade = (IsNull(@Grade, Grade)) AND Class IN (IsNull(@Class, Class ))
    END

    In the front end, I got multiple values for Subject parameters such as Math, English, Reading, etc... in a specified class. How do I can modify my above stored procedure to receive multiple values for a single parameter. I really your help with expertise knowledge. Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look up table-valued parameters
    Available in 2008 onwards from memory
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    My SQL Server database is 2005 not 2008.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Long parameter list

    *got headache issue when passing multiple values to a single parameter.
    Read a book, any book, on RDBMS. Pay attention to First Normal Form (1NF). This is the foundaiton of RDBMS, and a good SQL programmer will not do this! THIS IS BASICS!

    You can use a proprietary table parameter, but I like the long parameter list idiom.



    CREATE PROCEDURE [dbo].[School]
    @Grade AS varchar(50),
    @Class As varchar(50)*
    AS
    BEGIN*
    SELECT Name, Grade, Class*
    FROM School*
    WHERE Grade = (IsNull(@Grade, Grade)) AND Class IN (IsNull(@Class, Class ))*
    END

    The simplest example of the long parameter list technique is just to use them: Pass what you need and leave the rest of he list to default to NULLs.

    CREATE TABLE Zoo
    (sku CHAR(11) NOT NULL PRIMARY KEY,
    animal_name VARCHAR(25) NOT NULL)

    INSERT INTO Zoo (sku, animal_name)
    VALUES
    ('39634-62349', 'Horse'),
    ('74088-65564', 'Cow'),
    ('16379-19713', 'Pig'),
    ('39153-69459', 'Yak'),
    ('17986-24537', 'Aardvark'),
    ('14595-35050', 'Moose'),
    ('40469-27478', 'Dog'),
    ('44526-67331', 'Cat'),
    ('93365-54526', 'Tiger'),
    ('22356-93208', 'Elephant');

    CREATE PROCEDURE Animal_Picker
    (@p1 CHAR(11) = NULL,
    @p2 CHAR(11) = NULL,
    @p3 CHAR(11) = NULL,
    @p4 CHAR(11) = NULL,
    @p5 CHAR(11) = NULL)

    SELECT sku, animal_name
    FROM Zoo
    WHERE sku IN (@p1, @p2, @p3, @p4, @p5);

    EXEC Animal_Picker '39153-69459', '17986-24537', '99999-99999';

    If you had a long list, there would be a lot of NULLs in the IN() predicate list. It would be a good idea to clean them out. But you cannot do that with a simple IN() predicate list. We need to get the data into a column in a derived table first. This is another simple text edit problem.
    CREATE PROCEDURE Animal_Picker
    (@p1 CHAR(11) = NULL,
    @p2 CHAR(11) = NULL,
    @p3 CHAR(11) = NULL,
    @p4 CHAR(11) = NULL,
    @p5 CHAR(11) = NULL)
    AS
    SELECT sku, animal_name
    FROM Zoo
    WHERE sku
    IN (SELECT in_sku
    FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X(in_sku)
    WHERE in_sku IS NOT NULL);

    EXEC Animal_Picker '39153-69459', '17986-24537', '99999-99999';

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For a long list, submitting the values as a CSV string and then parsing it is not elegant, but it is functional and commonly implemented.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Here are the details

    This will give you the details as to why CSV splitters are dangerous, slow, etc.

    https://www.simple-talk.com/sql/lear...rameter-lists/

    https://www.simple-talk.com/sql/lear...sts---part-ii/

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry, but if I saw someone actually implement your
    Code:
    CREATE PROCEDURE Animal_Picker
    (@p1 CHAR(11) = NULL,
     @p2 CHAR(11) = NULL,
     @p3 CHAR(11) = NULL,
     @p4 CHAR(11) = NULL,
    .
    .
    .
     @pN CHAR(11) = NULL)
    method, it would be instant face-palm time.

    Your statement that "The iterative, XML and CLR solutions are purely procedural and are not really SQL." is incorrect. The CSV can be immediately converted to a temporary table, and from there on out every transaction against it is set-based. And it scales completely.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not much enthused about unstructured data like a CSV being stored in a table, but it works nicely as a transport mechanism.

    I actually prefer XML as a transport mechanism, but I'm still not enthused about using it for storage within a database. It is a bit "noisier" in terms of adding bytes to the data stream, but just about every tool that I use can cope with XML.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd wouldn't be so generous as to say it "works nicely" as a transport mechanism. As I already stated, this is not en "elegant" solution. Until we can pass table variables from the application layer there won't be an elegant solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Did you read the articles?

    Your statement that "The iterative, XML and CLR solutions are purely procedural and are not really SQL." is incorrect. The CSV can be immediately converted to a temporary table, and from there on out every transaction against it is set-based. And it scales completely.
    Did you read the two articles? When you jump out of SQL into XML or CLR, there is overhead, impedance mismatch and different rules for casting.

    For fun, try to write a CSV splitter that raises exactly the same errors as a stored procedure parameter would. I get all the power of the T-SQL compiler without any extra code. This means that two commas or an alpha in the CSV has to give an error and not cast to zero.

    Now, pass rows and not just columns to your splitter, say a list of (start_date, end_date, sales_cnt). Detect improper dates. Check that (start_date <= end_date) and that (sales_cnt >=0). Make use all three values are present.

    You hear the same objections from adjacency list users who do not want to add the constraints to assure no cycles, single superior nodes, non-repeated children, ordered siblings, etc. which I get for free in the nested sets model.

    Why is loading bad data into a staging table then trying to clean it with IF-THEN logic a good idea?

    80-95% of the real work in SQL is in the DDL.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    Did you read the two articles?
    Yes. I quoted from one, remember?

    Quote Originally Posted by Celko View Post
    For fun, try to write a CSV splitter that raises exactly the same errors as a stored procedure parameter would.
    I have other hobbies.

    Quote Originally Posted by Celko View Post
    This means that two commas or an alpha in the CSV has to give an error and not cast to zero.
    There's nothing preventing this logic from being implemented in a splitter.

    Quote Originally Posted by Celko View Post
    Now, pass rows and not just columns to your splitter, say a list of (start_date, end_date, sales_cnt).
    That's a totally different issue than the one presented which was simply a question of how to pass an array to a stored procedure.

    Quote Originally Posted by Celko View Post
    Why is loading bad data into a staging table then trying to clean it with IF-THEN logic a good idea?
    Where did I say it was?
    And how is IF-THEN logic required in order to clean it? You've lost me on this point. The parsing function I wrote doesn't use IF-THEN statements. It's set-based, and uses a CTV.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Hi avt2k6

    My solution is awful but should give the answer for your question.
    Create function for split values from single parameter.

    Code:
    CREATE FUNCTION dbo.CutParam
    
    	
    (@v_str nvarchar(1000))
    
    returns @tbl table (vout nvarchar(25))
    as
    begin
        set @v_str = @v_str + ','
        declare @v_cnt int
        declare @v_len int
        declare @v_curr_char nvarchar(1)
        declare @v_char nvarchar(25)
        declare @v_num nvarchar(25)
        set @v_len = len(@v_str) 
        set @v_cnt = 1
        set @v_curr_char = ''
        set @v_char = ''
    
        -- loopit
        while @v_cnt <= @v_len 
        begin
            set @v_curr_char = substring(@v_str, @v_cnt, 1)
            if (@v_curr_char <> ',')
            begin
                set @v_char = @v_char + @v_curr_char         
            end
            else
            begin  
                set @v_num = ( @v_char)         
                insert into @tbl values(@v_num)
                set @v_char = ''
            end
           set @v_cnt = @v_cnt + 1
        end
        return
    end
    Modify your procedure.

    Code:
    create procedure dbo.School_tst
    @Grade AS varchar(50),
    @Class AS varchar(500)
    AS BEGIN
    SELECT Name, Grade, Class 
     from dbo.School
    WHERE Grade = (ISNULL(@Grade,Grade)) AND Class IN (SELECT vout from dbo.CutParam (@Class))
    END
    Try to call the class as single parameter from front end.

    Code:
    EXEC dbo.School_tst 'Your_value','Math,Reading'
    Thanks
    Mike
    Last edited by Mikefox1207; 04-19-13 at 06:56.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's one that will work with SQL Server 2000:
    Code:
    create function ParseString2000(@String varchar(500), @Delimiter char(1))
    returns @Results table (Ordinal int NOT NULL IDENTITY (1, 1), StringValue varchar(500))
    as
    
    begin
    
    --function ParseString2000
    --blindman, 12/11/2009
    --Parses a string based upon a given single-character delimiter,
    
    --test parameters
    --declare	@String varchar(500)
    --declare	@Delimiter char(1)
    --set		@String = 'rnbqkbnr/pp1ppppp/8/2p5/4P3/5N2/PPPP1PPP/RNBQKB1R'
    --set		@Delimiter = '/'
    --set		@String = 'Abracadabra!, Shazam!, Presto!'
    --set		@Delimiter = ','
    
    if right(@String, 1) <> @Delimiter set @String = @String + @Delimiter
    
    declare	@SubString varchar(500)
    
    while len(@String) > 0
    begin
    	set @SubString = left(@String, charindex(@Delimiter, @String)-1)
    	set @String = ltrim(right(@String, len(@String) - len(@SubString) - 1))
    	insert into @Results (StringValue) values (@SubString)
    end
    
    return
    
    end
    ...and one for later versions which allow CTEs:
    Code:
    create function ParseString(@String varchar(500), @Delimiter char(1))
    returns table
    as
    
    --function ParseString
    --blindman, 5/20/2008
    --Parses a string based upon a given single-character delimiter,
    --without using loops or a tally table.
    
    --test parameters
    --declare	@String varchar(500)
    --declare	@Delimiter char(1)
    --set		@String = 'rnbqkbnr/pp1ppppp/8/2p5/4P3/5N2/PPPP1PPP/RNBQKB1R'
    --set		@Delimiter = '/'
    --set		@String = 'Abracadabra!, Shazam!, Presto!'
    --set		@Delimiter = ','
    --;
    
    return
    (
    with Results as
    		(select	1 as Ordinal,
    				ltrim(left(@String, charindex(@Delimiter, @String + @Delimiter)-1)) as StringValue,
    				convert(varchar(500), right(@String + @Delimiter, len(@String) - charindex(@Delimiter, @String+@Delimiter) + 1)) as Remaining
    		UNION ALL
    		select	Ordinal+1,
    				ltrim(left(Remaining, charindex(@Delimiter, Remaining)-1)),
    				right(Remaining, len(Remaining) - charindex(@Delimiter, Remaining))
    		from	Results
    		where	len(Remaining) > 0)
    select	Ordinal,
    		StringValue
    from	Results
    )
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Unhappy magnetic tape files are not set-oriented or relational

    I have other hobbies {write a CSV splitter that raises exactly the same errors as a stored procedure }.
    So does everyone else :-) So I let the compiler save me and them the trouble and give us the right answers.

    There's nothing preventing this logic { two commas or an alpha in the CSV} from being implemented in a splitter.
    Is this now a hobby for you? You cannot have it both ways; either the splitter handles input errors or your decide to throw out data quality. The logic is harder than you might first think. Remember your compiler writing class?

    that's a totally different issue than the one presented which was simply a question of how to pass an array to a stored procedure.
    My point was that the long parameter list is easy to generalize from passing a single scalar column to passing rows with a little cut&paste text editing.

    And how is IF-THEN logic required in order to clean it? You've lost me on this point. The parsing function I wrote doesn't use IF-THEN statements. It's set-based, and uses a CTV.
    Perhaps I should have said “Classic Structured Programming” (iteration, selection, sequence and recursion). XML and the 40+ CLR languages depend on left-to-right, sequential processing. XML is hierarchical, so data element get meaning from when and where you read them in the sequence. You seem to think that if I can load data into a table from a magnetic tape, this makes magnetic tapes set-oriented and not sequential.

    I never understood why someone would write a CSV in a string, but not be willing to write exactly the same text in the parameter list of an EXEC statement. I got this trick from some internals of DB2 which can handle 32K parameters.
    __________________

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    I never understood why someone would write a CSV in a string, but not be willing to write exactly the same text in the parameter list of an EXEC statement. I got this trick from some internals of DB2 which can handle 32K parameters.
    Because there is a fundamental difference between code and data. That's like saying "I've never understood why people will eat food off of a plate, but won't eat the plate."
    Code should be efficient and scalable. A piece of code that contains 100 parameter is not scalable to a situation where more than 100 parameters may be required. A piece of code which contains 32,000 parameters is, prima facie, ridiculous.

    Quote Originally Posted by Celko View Post
    Perhaps I should have said “Classic Structured Programming” (iteration, selection, sequence and recursion). XML and the 40+ CLR languages depend on left-to-right, sequential processing.
    I guess you should have. But the fact remains that this “Classic Structured Programming” is only required to parse the string, and from there on it's set-based logic all the way. (And as I pointed out, my parsing function actually uses set-based logic.) Now, with N parameters you still need “Classic Structured Programming”, but you need it on the application side. It is only dogmatic puritism that insists sql be kept clean of any procedural programming rather that analyzing the problem and coming up with the best solution given the limitations at hand (the primary limitation being that table-valued parameters can't be passed from the application).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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