July 7, 2009 at 7:57 pm
I have a table, OrderLines with 4 columns - custID, prodID, qty, status. I also have a stored procedure I need to call to create orders in another process. It accepts 3 variables. I can execute this stored procedure manually with - exec createOrder 3257,'23490644804',16.
What I want to do is to have another procedure to check the table and pass the values of the first 3 columns to the createOrder procedure for any rows that have a status of 0. I can then execute this new procedure every minute or so, since there won't be an excessive number of lines inserted into the table. Finally, I need to update the status of the row in question to a 1.
I can add columns to this table - like to add a sequential ID, if that will make the status change easier.
Can anyone provide specific advice on how to proceed, or point me to a web resource for this?
Thanks!
July 7, 2009 at 9:12 pm
If you can modify the createOrder proc then I would first investigate whether it can be changed to read the rows directly, or populate the rows into a temp table and read that table in createOrder.
If you cannot change createOrder, I would build up a statement from the query and execute it dynamically - try looking at http://rjdudley.com/blog/post/Is-Dynamic-SQL-in-Your-Stored-Procedures-Vulnerable-to-SQL-Injection.aspx
I believe http://www.sommarskog.se/dynamic_sql.html will give you even more detail but the site appears to be down at the moment.
July 7, 2009 at 10:26 pm
add another column as flag (values 0 or 1) in the main table .
-> Create a cursor that inserts the values from you main table to the temp table having columns CustID, ProdID and Qty where Flag = 0
-> create 3 variables on these 3 columns and set them to the temp table values one by one (this is how cursor works )
-> use these variables in the SP
-> set the flag to 1 in the main table (from with in the cursor)
-> Drop the temp table later .
I could have doen this for you , but 1) i have less time and 2) you will gain knowledge if you do it yourself .
Regrards.
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 8, 2009 at 4:02 pm
If this process is the only way that new records should be inserted into the destination table, then you could use a trigger on the INSERT that would update the source table. You'd just need to add some kind of unique identifier to the OrderLines table, so that you could join it using that unique ID to the "INSERTED" table you get access to in the trigger. You also need to include that ID in the INSERT so it's accessible there.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 8, 2009 at 6:39 pm
hi_abhay78 (7/7/2009)
add another column as flag (values 0 or 1) in the main table .-> Create a cursor that inserts the values from you main table to the temp table having columns CustID, ProdID and Qty where Flag = 0
-> create 3 variables on these 3 columns and set them to the temp table values one by one (this is how cursor works )
-> use these variables in the SP
-> set the flag to 1 in the main table (from with in the cursor)
-> Drop the temp table later .
I could have doen this for you , but 1) i have less time and 2) you will gain knowledge if you do it yourself .
Regrards.
Heh... this is just so wrong in so many ways. The proper thing to do would be to repair the CreateOrder stored procedure so that it will process in a non RBAR fashion.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply