[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2658
  • Last Modified:

Insert a list of values using a stored procedure

I have a list of checkbox values that I need to insert into a table.  Is there a way to send a list to a stored procedure and then loop over the values to do an insert?
0
monica73174
Asked:
monica73174
  • 5
  • 3
  • 3
  • +4
1 Solution
 
Jinesh KamdarCommented:
Hi Monica, how do u intend to send these values to the stored procedure? You could pass them as separate parameters or you could concatenate them into a single string and then split them within the stored procedure to build the INSERT statement.
0
 
maradamCommented:
Yes, there are some ways to do that.
1. Pass any value to the procedure as subsequent parameter. It is easy to implement but needs the procedure to be rewritten if new checkbox is added
2. Pass a list of checkbox states as one parameter. Then parse passed value and insert parsed values to a table. The problem here is the need of parsing and possibly rewriting the procedure to support new parameters.
3. Insert all the checkbox states to an intermediate table, and then reinsert them to target table in stored procedure. The intermediate table can look like:
create table TmpCheckboxvalues (session_id int default @@SPID, checkboxname nvarchar(100), value tinyint, primary key @@SPID, checkboxname)
Then application should do something like that:
delete from TmpCheckboxValues where session_id = @@SPID
insert TmpCheckboxValues (checkboxname,value) values (<from application>)
exec InsertStatesProc
The problem in this solution is that you must enter your values twice. Once with simple inserts to intermediate table and twice with stored procedure into target table.
0
 
Richard QuadlingSenior Software DeveloperCommented:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=897&lngWId=5

details how to use ...

INSERT INTO table
SELECT 'Val1a', 'Val1b'
UNION ALL
SELECT 'Val2a', 'Val2b'


So, what handles the list of checkboxes? You could create the single statement and execute that.

Obviously making sure the values are clean for inserting into an SQL statement.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
lexiflexCommented:
Another possibility is the use of an XML-parameter. You can put an XML string into the stp and disect it using the OPENXML command.

This is a simple example:


CREATE PROCEDURE dbo.stp_DisectXML
    @xmlDocument   XML
AS
BEGIN
   DECLARE @docHandle int
 
   CREATE TABLE #cb (cbName VARCHAR(20), cbValue VARCHAR(20))
 
   -- Open de XML-document
   EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
 
   -- Use OPENXML to populate the temp-table.
   INSERT #cb
   SELECT * 
   FROM OPENXML(@docHandle, N'/ROOT/cb')
      WITH #cb
 
   -- Close the XML document
   EXEC sp_xml_removedocument @docHandle
 
   SELECT * FROM #cb
END
 
-- Usage
DECLARE @XMLDocument XML
SET @xmlDocument = N'<ROOT>
   <cb cbName="Checkbox1" cbValue="Green"></cb>
   <cb cbName="Checkbox3" cbValue="Red"></cb>
   <cb cbName="Checkbox4" cbValue="Blue"></cb>
   <cb cbName="Checkbox11" cbValue="Banana"></cb>
   </ROOT>'
 
EXEC  dbo.stp_DisectXML @xmlDocument

Open in new window

0
 
monica73174Author Commented:
Basically I am providing a list of checkboxes.  A user can select any combination of check boxes.  Checkboxes can be added or removed based on a database query so the list is dynamic.  I am looping through the checkboxes and added any checked values to a string that I am using as a list.  The list is comma deliminted.  Does that make this a little more clear?
0
 
Jinesh KamdarCommented:
Yes, it does clarify a lot. I believe you will be passing this list string as a parameter to the SP. How many max. no. of check-boxes are we dealing with here ?
0
 
monica73174Author Commented:
The number of check boxes can vary, I am thinking no more than possibly 20 values.  
0
 
Jinesh KamdarCommented:
And ur INSERTed table would have a column for each check-box or each check-box selection would INSERTed as a separate row ?
0
 
monica73174Author Commented:
Each checkbox would be a seperate row.
Example.
AccountID - RegionID (the regionid is the checkbox value being passed)
0
 
Jinesh KamdarCommented:
Ok. Here goes a sample, assuming that p_selected_checkbox_list has the string of comma separated ids for the selected checkboxes.
CREATE OR REPLACE PROCEDURE insert_proc (p_acct_id IN NUMBER, p_selected_checkbox_list IN VARCHAR2)
AS
v_str VARCHAR2(4000);
BEGIN
v_str := p_selected_checkbox_list;
WHILE INSTR(v_str, ',') <> 0 LOOP
      INSERT INTO table_name(account_id, region_id) VALUES(p_acct_id, SUBSTR(v_str, 1, INSTR(v_str, ',') - 1);
      v_str := SUBSTR(v_str, INSTR(v_str, ',') + 1);
END LOOP;
INSERT INTO table_name(account_id, region_id) VALUES(p_acct_id, v_str);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
     ROLLBACK;
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END insert_proc;

Open in new window

0
 
Anthony PerkinsCommented:
jinesh_kamdar,

Psst!  The questioner is using MS SQL Server and not Oracle.
0
 
Jinesh KamdarCommented:
@acperkins: Oops, didn't notice that after the first post :-| Please step in to help here.
0
 
Anthony PerkinsCommented:
I like lexiflex approach.  That is the way I would do it.
0
 
Anthony PerkinsCommented:
Award points to lexiflex
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now