Passing multiple values through one parameter in a stored procedure

  • Below is a script to create a table, and one to create and execute a stored procedure on the table, I am trying to build a report that passes multiple values in one parameter, but the stored procedure takes the inputs as a comma delimited list. I came across some sites that gave script for a splitlist function that will individually delimit the list but when I put that into the stored procedure it is not recognized. Does anybody see anything wrong with what I have here and if so, have a solution?

    Thanks a bunch!

    CREATE TABLE [dbo].[Customer](

    [CustomerID] [int] IDENTITY(1,1) NOT NULL,

    [FirstName] [nvarchar](50) NOT NULL,

    [MiddleName] [nvarchar](50) NULL,

    [LastName] [nvarchar](50) NOT NULL

    )

    GO

    INSERT INTO dbo.Customer

    Values( 'John','M.','Lynch'),

    ('Jason','TE','Whitten'),

    ('Jay','OldSchool','Novachek')

    GO

    ------------------------Create stored procedure

    CREATE PROCEDURE Example

    -- Add the parameters for the stored procedure here

    @CustomerID int = 0

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    SELECT CustomerID, LastName

    FROM dbo.Customer

    WHERE CustomerID IN (select * FROM dbo.Splitlist(@CustomerID, ',')) -------Here is the problem, I cannot get the sp to recognize the function unless I delimit the query and set it as a variable

    -----and append the where statement to the end. How do I do this?

    END

    GO

    -----Execute stored procedure for the table

    DECLARE @return_value int

    DECLARE @CustomerId int

    EXEC @return_value = [dbo].[Example]

    @CustomerId = 1---------Would like to be able to execute for IN ('1','2','3')

    GO

  • What you need to do is either use a table valued parameter or change your datatype to a varchar and then split the variable inside the proc.

    If your Split function is using a while loop or xml, I suspect you would gain some good knowledge by reading the article in my signature about splitting strings. You will most likely replace your split function after reading that article.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Here is an example of doing this both ways:

    First is the Delimited string method. The code for the DelimitedSplit8K function can be found at the link in my signature about splitting strings. I added the delimiter parameter to provide flexibility. If you want, you could eliminate that and just use a comma (or whatever delimiter you prefer).

    CREATE PROCEDURE Example

    (

    @CustomerID varchar(8000),

    @Delimiter char(1)

    )

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    SELECT CustomerID, LastName

    FROM dbo.Customer c

    join dbo.DelimitedSplit8K(@CustomerID, @Delimiter) s on c.CustomerID = s.Item

    END

    GO

    exec Example @CustomerID = '1,2,3', @Delimiter = ','

    The table valued parameter approach takes a little more setup and initially looks like overkill. However, it can provide an incredible easy way to use for developers.

    The first thing you have to understand about TVP is that you have to declare the type as user defined table datatype. Because you are only needing to use a list of ints I made this generic so it could be reused by other processes. You can make your tables as complex as needed but when just a simple datatype I like to keep it generic.

    create type IntTable as Table

    (

    val int null

    )

    You can now declare variables and parameters of type IntTable.

    Here is the proc with the table parameter.

    CREATE PROCEDURE ExampleTableParam

    (

    @CustomerID IntTable readonly

    ) as begin

    SELECT CustomerID, LastName

    FROM dbo.Customer c

    join @CustomerID s on c.CustomerID = s.val

    end

    This one is a little more complicated to use from just straight sql because you need your newly defined table type. You also need to populate the rows but this is still pretty straight forward.

    declare @MyTable IntTable

    insert @MyTable

    select 1 union all

    select 2 union all

    select 3

    exec ExampleTableParam @CustomerID = @MyTable

    Now I mentioned how easy this is for developers. Let's say you have datagrid that allows multiple row selection or anything else that can be directly converted to an array of ints. All you have to do is pass list as your parameter. In .NET you can pass a DataTable, combobox items or just about anything. I have been working on an article discussing this but have never found the time to finish it.

    Hope this helps.

    Oh and btw...thanks for posting ddl and sample data in your first post. It makes helping infinitely easier. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you guys very much for the help and the additional reading! Good Stuff!

  • You're welcome. Hope my posts helped. Post back if you run into any issues or need a little help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • That was what I needed to get it to run!

  • sir can u help me to find out?????how can i pass more values in the below parameter???

    SET @cursemcredits = ( SELECT SUM(credit)

    FROM ( SELECT rs.sub_code,

    sm.sem_attended,

    SUM(rs.credit_points) credit

    FROM student_details sd

    INNER JOIN dbo.student_marks sm ON sd.roll_no = sm.roll_no

    INNER JOIN grade g ON g.grade_id = sm.grade_id

    INNER JOIN dbo.regulation_subject rs ON rs.regulation_sub_id=sm.regulation_sub_id

    INNER JOIN dbo.regulation_info ri ON ri.regulation_no = rs.regulation_no

    AND rs.regulationinfo_id = ri.regulationinfo_id

    WHERE sd.roll_no = @roll_no

    AND sm.sem_attended <= @cur_sem

    AND sm.sem_attended = ri.semester

    AND sm.sem_attended <> -1

    AND sm.sem_attended <> 0

    AND rs.sub_name NOT LIKE '%#'

    GROUP BY rs.sub_code,

    sm.sem_attended

    ) a

    )

    sir,it shows all semester credits for that student like 50 but what i need is 3 values like 17,17,16 means single semester credits...what should i add here to gt a o/p like dat?????:-)

  • nitha jen (6/6/2013)


    sir can u help me to find out?????how can i pass more values in the below parameter???

    SET @cursemcredits = ( SELECT SUM(credit)

    FROM ( SELECT rs.sub_code,

    sm.sem_attended,

    SUM(rs.credit_points) credit

    FROM student_details sd

    INNER JOIN dbo.student_marks sm ON sd.roll_no = sm.roll_no

    INNER JOIN grade g ON g.grade_id = sm.grade_id

    INNER JOIN dbo.regulation_subject rs ON rs.regulation_sub_id=sm.regulation_sub_id

    INNER JOIN dbo.regulation_info ri ON ri.regulation_no = rs.regulation_no

    AND rs.regulationinfo_id = ri.regulationinfo_id

    WHERE sd.roll_no = @roll_no

    AND sm.sem_attended <= @cur_sem

    AND sm.sem_attended = ri.semester

    AND sm.sem_attended <> -1

    AND sm.sem_attended <> 0

    AND rs.sub_name NOT LIKE '%#'

    GROUP BY rs.sub_code,

    sm.sem_attended

    ) a

    )

    sir,it shows all semester credits for that student like 50 but what i need is 3 values like 17,17,16 means single semester credits...what should i add here to gt a o/p like dat?????:-)

    Two things.

    1) You should start your own thread for this instead of jumping onto another thread.

    2) In order to help we will need a few things:

    -- Sample DDL in the form of CREATE TABLE statements

    -- Sample data in the form of INSERT INTO statements

    -- Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply