Send multiple variables to a stored procedure to be used in an IN clause?

Question

I have a few textboxes on a web page gui that I need to send to a stored procedure. The user can enter a list of names, account numbers, companies separated by commas. I would then do some parsing and send the list of objects to my stored procedure. My Textboxes would look like this

Name: [ Peter, Paul, Ryan, Julie ]
Account Numbers: [ 001, 002, 003, 004 ]
Companies: [ New Company, Old Company,  ]

So I have a profile class that holds a list of names, accounts etc that are mapped to the view model above.

I have a few textboxes on a gui that I need to send to a stored procedure. The user can enter a list of names, account numbers, companies separated by commas. I would then do some parsing and send the list of objects to my stored procedure. My Textboxes would look like this

Name: [ Peter, Paul, Ryan, Julie ]

Account Numbers: [ 001, 002, 003, 004 ]

Companies: [ New Company, Old Company, ]

So I have a profile class that holds a list of names, accounts etc that are mapped to the viewmodel above.

List profiles = new List();

profiles.Names = names;

profiles.AcctNumbers = acctnums;

profiles.Companies = companies;

Then I assign each profile property to a SQL Parameter and pass it to the stored procedure.

My stored procedure would look like this

ALTER PROCEDURE      [dbo].[GetUsers]

    @Names         varchar(100),

    @AcctNums   varchar(100),

    @Companies varchar(100)

   

    Select * from users

    where Name in @Names

    and AccountNumber in @AcctNums

    and Company in @Companies

in theory you would think this would work but it doesn't. The stored procedure returns nothing How can I get my idea to work? Or should I completely revamp my approach towards this issue?

Then I assign each profile property to a SQL Parameter and pass it to the stored procedure.

My stored procedure would look like this

ALTER PROCEDURE      [dbo].[GetUsers]

    @Names         varchar(100),

    @AcctNums   varchar(100),

    @Companies varchar(100)

   

    Select * from users

    where Name in @Names

    and AccountNumber in @AcctNums

    and Company in @Companies

in theory you would think this would work but it doesn't. The stored procedure returns nothing How can I get my idea to work? Or should I completely revamp my approach towards this issue?

EDIT: This is not the same as this answer Not the Same Issue. I need to pass in multiple values into a variable which resides in a stored procedure.

If I use the suggestion I get "Procedure or function GetUsers has too many arguments specified."

That answer is using a simple select statement which is not the same as a stored procedure with variables

You need to look into 'table valued parameters'. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters — wablab, Oct 30 '17 at 20:42
@wablab What if the user enters in data like Peter, Paul, which will on its own row. Then they enter in an account num of 100 which belongs to mary. Wouldnt the row have Peter, 100, and Paul 100? So the in clause would return no data? Im a bit confused — CodeMan03, Oct 30 '17 at 20:43
Did you delete you previous question on this exact topic that I saw earlier today? — Sean Lange, Oct 30 '17 at 20:44
Possible duplicate of [Passing array to a SQL Server Stored Procedure](https://stackoverflow.com/questions/24636669/passing-array-to-a-sql-server-stored-procedure) — Murat Seker, Oct 30 '17 at 20:45
@CodeMan03, check the link in my first comment for an example of doing what you're asking about. In short, use a `DataTable`. — wablab, Oct 30 '17 at 20:45
The real meat and potatoes of this is that you are assuming your users are going enter delimited data in the correct order. This is a pipe dream. Users can't type in a single scalar value correctly 90% of the time. There is next to no chance they can get multiple pieces of data across multiple inputs right. Sounds like what you really need to look at is a better front end for capturing the users request. — Sean Lange, Oct 30 '17 at 20:46
Sean you make an excellent point. Do you have any idea of better front end design for capturing user data? I am at a loss here — CodeMan03, Nov 01 '17 at 02:50

score 3 · Accepted Answer · answered Nov 01 '17 at 05:11

Alter your stored procedure like below

ALTER PROCEDURE      [dbo].[GetUsers]

    @Names         varchar(100),

    @AcctNums   varchar(100),

    @Companies varchar(100)



    DECLARE @tmpNames TABLE (ID INT) 
    DECLARE  @XML AS XML, @p as int

    SET @XML = CAST(('<X>'+REPLACE(@Names,',' ,'</X><X>')+'</X>') AS XML)

    INSERT INTO @tmpNames SELECT N.value('.', 'Varchar') AS Name FROM @XML.nodes('X') AS T(N)

    Select * from users

    where Name in (select * from @tmpNames)
      and AccountNumber in @AcctNums

    and Company in @Companies
    --or use join
      --Select * from users
      --JOIN @tmpNames ON users.username=@tmpNames.name

Send multiple variables to a stored procedure to be used in an IN clause?

1 Answers1