Sending an array to a stored procedure

Results 1 to 4 of 4

Thread: Sending an array to a stored procedure

  1. #1
    John Lounsbery Guest

    Default Sending an array to a stored procedure

    I want to create a stored procedure that will take a variable size list of ids and do a select on them, If I am coding the SQL in ASP I would just say:<BR><BR>SQLString = "SELECT * FROM items WHERE item_id IN (" & Request.Form("selected_items") & ")"<BR><BR>When I try to mimic this in a stored procedure though, I get an implicit conversion error, trying to convert a string into a int. Does anyone know a way around this, perhaps a split function in T-SQL? Thank you.<BR><BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Sending an array to a stored procedure

    What are the contents of Request.Form("selected_items")? (Do a Response.Write Request.Form("selected_items") to find out.<BR><BR>If it is in the form:<BR><BR>5,1,9,3<BR><BR>then your above SQL statement should work. What does Request.Form("selected_items") look like now? Perhaps we&#039ll have to tinker with it to get it in the suitable form...

  3. #3
    John Lounsbery Guest

    Default RE: Sending an array to a stored procedure

    Thanks for your quick response Scott. The contents of Request.Form("selected_items") come from similarly named checkboxes, so they come in the format 1,2,4. The line of ASP works fine. I want to make it work in a stored procedure though. just passing the 1,2,4 as a parameter, but SQL Server is expecting integers, and won&#039t take my string.<BR><BR>

  4. #4
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Sending an array to a stored procedure

    You can pass it in as a string, then use SQL commands to parse it out. It would be tricky, but plausible. (I think I remember SQL has some nifty string functions you can use, although, honestly, it&#039s been a while!)<BR><BR>I don&#039t think you can pass it an array, since an array isn&#039t a valid SQL datatype.


Posting Permissions

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