从PHP Codeigniter调用存储过程 [英] Call stored procedure from php codeigniter

问题描述

寻找了一个星期，但没有找到一些我可以用来执行存储过程的内容。
方案：

Looking for one week and did not found some content that I can use to execute a stored procedure. Scenario:

我已在Profiler窗口中复制了在SQL Server 2014管理上运行的代码。

I have copied the code that runs on SQL Server 2014 management in the Profiler window.

我打开了SQL Studio和HeidiSQL，然后运行查询，它运行正常，并向我返回了预期的结果。

I opened the SQL Studio and HeidiSQL and run the queries and it runs ok and returns me the expected result.

当我尝试粘贴并修改此内容为得到的结果与我在SQL客户端上获得的结果相同，但没有像在SQL客户端上那样运行。
我该如何实现？

When I tried to paste and modify this content to get the same result as I have on the SQL clients it did not run like it does in the SQL clients. How can I achieve this?

例如：在我的SQL中，我必须声明128个变量来运行存储过程，但为了简单起见，会记入sintaxe与一个或128个变量是相同的吗？

eg: In my SQL I have 128 vars that I have to declare to run the stored procedure but to be more simple I will post only one var having in mind that sintaxe is the same to one or 128 vars right?

declare @var01 varchar(1000) set @var01='.$var01.'

EXEC
stored_procedure_y
@var01 output

SELECT
@var01 AS any_value

我尝试了许多方法来执行SQL命令，但均未成功。
我尝试运行的一些示例：

I have tried many ways to execute the SQL commands with no success. Some examples that I tried to run:

• last

• last

$declare_vars = 'declare @var01 varchar(1000) set @var01='.$var01.';
$exec_sp = 'EXEC operacao_sitef @var01 output;
$select_retorno = 'SELECT @var01 AS any_value;

$queryDeclare = $this->db->query($declare_vars);

$queryExec = $this->db->query($exec_sp);
    $queryRetorno = $this->db->query($select_retorno);

    return $queryRetorno;

不工作。

其他

$query = $this->db->query('
declare @var01 varchar(1000) set @var01='.$var01.'
EXEC
operacao_sitef
@var01 output
SELECT
@var01 AS any_value
 ');
 return $query;

也无效。
任何帮助将不胜感激。我是SP的新手，不知道如何使用codeigniter调用它并收集结果以完成操作。

Also did not work. Any help would be appreciated. I'm newbie with SP's and don't know how to call this using codeigniter and collect the results to finalize the operation.

推荐答案

高兴地说我会继续工作。

Glad to say that I will maintain my job.

这里是解决我的问题的职位。
记住我正在使用PHP和mssql数据库上的codeigniter：

Here goes the post that solved my problem. Remembering I'm working with codeigniter on php and mssql database:

在模型中：

public function execute_sp($var1 = NULL,$var2 = NULL, $var3 = NULL, $var4 = NULL, $var5 = NULL, $var6 = NULL, $var7 = NULL, $var8 = NULL, $var9 = NULL, $var10 = NULL){
$sp = "stored_procedure_name ?,?,?,?,?,?,?,?,?,? "; //No exec or call needed

//No @ needed.  Codeigniter gets it right either way
$params = array(
'PARAM_1' => NULL,
'PARAM_2' => NULL,
'PARAM_3' => NULL,
'PARAM_4' => NULL,
'PARAM_5' => NULL,
'PARAM_6' => NULL,
'PARAM_7' => NULL,
'PARAM_8' => NULL,
'PARAM_9' => NULL,
'PARAM_10' =>NULL);

$result = $this->db->query($sp,$params);

在控制器中：

$var1 = 'value';
$var2 = 'value';
$var3 = 'value';
$var4 = 'value';
$var5 = 'value';
$var6 = 'value';
$var7 = 'value';
$var8 = 'value';
$var9 = 'value';
$var10 = 'value';

$this->model->sp($var1, $var2, $var3, $var4, $var5, $var6, $var7, $var8, $var9, $var10);

它起作用了！

有用的资源这么多：将执行存储过程从PHP发行到用户@Ulises Burlando的Microsoft SQL SERVER 答复

source that helped so much: Issue executing stored procedure from PHP to a Microsoft SQL SERVER reply from user @Ulises Burlando

这篇关于从PHP Codeigniter调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！