+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MS SQL, Stored Procedures and rows_affected (/showthread.php?tid=17214)
MS SQL, Stored Procedures and rows_affected - El Forum - 03-29-2009
[eluser]RBrowne[/eluser]
This has been driving me absolutely crazy for the last day and a half.
I have a stored procedure on my SQL db that updates a table with the passed data. I use the simple method of executing the stored procedure within CI of:
Code:
$oQuery = $this->db->query("EXEC store_procedure_name @p_1=" . $var1 . ", @p_2=" . $var2);This works great. It does as it should and makes the changes to the database. If I echo the query text, copy it into MSSQL manager and execute it from there, it tells me the correct number of rows that have been affected by it.
IF, however, I use $this->db->affected_rows() it returns 1. Always. This makes my life extremely difficult, as I was hoping to use the affected_rows() as a quick an easy method of seeing whether it was successful in making the changes.
I've searched everywhere I can think of for a solution to this, and unfortunately I can't seem to find anything. I have two questions that I would really appreciate the answer to either:
1) How can I make the affected_rows() return the correct number of rows affected?
2) How else could I check to see whether the query was successful or not?
I'm assuming it's something to do with the fact that I'm using: query() rather than the exec() and bind().
Any help on this matter would be greatly appreciated. Thanks in advance.
Rich
MS SQL, Stored Procedures and rows_affected - El Forum - 03-29-2009
[eluser]TheFuzzy0ne[/eluser]
I think your stored procedure needs to make a call to ROW_COUNT(), or at least I think this is the case for MySQL.
MS SQL, Stored Procedures and rows_affected - El Forum - 03-29-2009
[eluser]RBrowne[/eluser]
Thanks for such a quick response.
When you say need to return ROWCOUNT, do you mean it needs to use the return @@rowcount or SELECT @@rowcount?
Rich
MS SQL, Stored Procedures and rows_affected - El Forum - 03-29-2009
[eluser]TheFuzzy0ne[/eluser]
I'm just guessing, but I'd say SELECT it. I edited my wording, as I realised it sounded wrong after I posted.
MS SQL, Stored Procedures and rows_affected - El Forum - 03-29-2009
[eluser]TheFuzzy0ne[/eluser]
This forum thread might help you: http://www.ozgrid.com/forum/showthread.php?t=79766
It's a bit of a read, but I know nothing about MSSQL, so I can only offer my Google searching abilities.
MS SQL, Stored Procedures and rows_affected - El Forum - 03-29-2009
[eluser]RBrowne[/eluser]
Thanks for the link - I'd passed that one a couple of times, but it's not what I was after. However you weren't that far off with your original thought.
The easiest solution is indeed to edit the stored procedure to 'SELECT @@rowcount' which will return the number of rows affected by the query. Then when you execute the stored procedure as detailed in my initial post, it will return a result which you can use as with any standard result.
So to perform the check it is:
Code:
$aRes = $oQuery->row_array();
if ($aRes[0] != 0) {
// Success.
} else {
// Failed.
}Thanks for the help.
Rich