How to call MSSQL Stored Procedure with parameters in Codeigniter

Question

I want to call a stored procedure in my Codeigniter website, but I can't, I tried many solution but I couldn't. I use Microsoft SQL Server 2012 and Codeigniter 3.1 and sqlsrv dirver and iis 8.5 and php 5.6

my stored procedure code:

alter PROCEDURE [dbo].[count_message]
        @username smallint


AS  SET NOCOUNT ON;

return isnull((select count(*) from dbo.message where ms_user=1 or ms_user=@username),0)

[Bad habits to kick : using SELECT or RETURN instead of OUTPUT](https://sqlblog.org/2009/10/09/bad-habits-to-kick-using-select-or-return-instead-of-output) — GarethD, Mar 06 '17 at 13:46
Not an answer to your question but the ISNULL in your query will never return the second argument. This is because your query is a COUNT of rows that meet the predicates in your where clause and if no rows are returned it will return 0. — Sean Lange, Mar 06 '17 at 14:06
Count will ALWAYS return a value. Try this. Select count(*) from sys.tables where name = 'asdfasdfadfasdf'. It will return 0, not null because it is known how many rows meet that criteria. — Sean Lange, Mar 06 '17 at 14:53
without isnull just return 0 in sql server execute stored procedure — M.Ghavam, Mar 06 '17 at 15:09
If you execute count_message in SMS does it return anything besides 0? If you change it to `return @username` (instead of @num) does $result = 2600 so you know CI is passing it right? — ourmandave, Mar 06 '17 at 15:43
I executed count_message in Microsoft SQL Server Management Studio and return number of message but when I use this stored procedure in CI don't get any result $result = $this->db->query("exec count_message @username='2600'"); echo 'row Num: '.$result; — M.Ghavam, Mar 06 '17 at 15:52
Maybe try calling it with [this answer](http://stackoverflow.com/a/30425271/3585500)? Like `$sp = 'count_message'; $params = array('username' => 2600); $result = $this->db->query($sp, $params);` — ourmandave, Mar 06 '17 at 16:14
Meant `$sp = 'count_message ?';` above. (Won't let me edit comment.) — ourmandave, Mar 06 '17 at 16:33
[Or there's this answer where you just drop the `exec`.](http://stackoverflow.com/a/31586592/3585500) As in, `$result = $this->db->query("count_message '2600'");` — ourmandave, Mar 06 '17 at 17:24
I checked $result = $this->db->query("count_message '2600'"); this method execute procedure but dont return any result, for example if i want to update or delete this method is good, but when I want to return a value I can't get any value in CI, please help me — M.Ghavam, Mar 07 '17 at 05:45

score 1 · Accepted Answer · answered Oct 28 '17 at 10:56

1

I Use this code and works very well

$result = $this->db->query("SE_Export_Top_Post N'{$searchId}', N'{$search}'")

answered Oct 28 '17 at 10:56

M.Ghavam

101
4
16

score 0 · Answer 2 · edited Mar 06 '17 at 18:13

0

Try this:

$this->db->query("exec count_message @username='example'")

edited Mar 06 '17 at 18:13

FelixSFD

6,052
10
43
117

answered Mar 06 '17 at 13:44

Dmitry Cat

475
3
11

Not Return Any thing – M.Ghavam Mar 06 '17 at 13:55
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/15434227) – prava Mar 06 '17 at 14:01
@prava A code-only answer might not be a good one, but it's still an answer. I would recommend you this post about the LQPRQ: [You're doing it wrong: A plea for sanity in the Low Quality Posts queue](http://meta.stackoverflow.com/questions/287563/youre-doing-it-wrong-a-plea-for-sanity-in-the-low-quality-posts-queue) – FelixSFD Mar 06 '17 at 18:12

score 0 · Answer 3 · edited Oct 24 '17 at 08:58

0

I changed my codes but I couldn't get any output

I changed my stored procedure:

alter PROCEDURE [dbo].[count_message]
        @username smallint,
        @num Int = NULL output

--AS    SET NOCOUNT ON;
 as
   begin

   set @num = isnull((select count(*) 
                      from dbo.sd_message 
                      where ms_user=1 or ms_user=@username),0)

   return @num

END
GO

and use this code in PHP/Codeigniter:

$result = $this->db->query("exec count_message @username='2600'");
echo 'row Num: '.$result;

edited Oct 24 '17 at 08:58

Abdul Rasheed

6,486
4
32
48

answered Mar 06 '17 at 14:08

M.Ghavam

101
4
16

1

If you have an Update to the question, please **edit** the question instead of writing an answer. – Fildor Oct 24 '17 at 08:06
$result here will be an stdClass, which is converted to an empty string when you try to concatenate it. – Sergei Kovalenko Dec 04 '17 at 14:05

How to call MSSQL Stored Procedure with parameters in Codeigniter

3 Answers3