none
How to execute a simple stored procedure RRS feed

  • Question

  • I'm executing simple stored procedure same to:

    create procedure [dbo].[usp_procedure]
    @var1 int=null output,
    @var2 varchar(25)=null output,
    @var3 varchar(25)=null output,
    @var4 varchar(25)=null output,
    @var5 varchar(25)=null output,
    @action char(1)=null output
    as
    begin
      set nocoutn on;
    if @action = 'S'
    begin
       select * from t_table order by @var1
    end

     in my script I call to this stored procedure but, this send me a error. my script is

    $action="S";

    // Set up T-SQL query.
    $tsql = "call dbo.usp_T_Usuarios (@action=?)";

    // Assign parameter values.
    $params = array($action);

    // Specify types for parameters.

    $stmt = sqlsrv_query( $conn, $tsql, $params);
    echo "<br><br>Executed.....<br><br>";
    // Create and execute the statement. Display any errors that occur.
    if(!$stmt)
    {
         echo "Error executing the sp...<br>";
         die( print_r( sqlsrv_errors(), true));
    }
    else
    {
        echo "<br>Execute sp....<br><br>";
        echo $stmt;
    }

    and the error is:

    conected...

    The conection was succefully.

    Executed.....

    Error executing the sp...
    Array ( [0] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Índice descriptor no válido [message] => [Microsoft][SQL Native Client]Índice descriptor no válido ) [1] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Número de parámetros no válido [message] => [Microsoft][SQL Native Client]Número de parámetros no válido ) )

    I need help me. some body tell me what is my error. please.
    Monday, March 24, 2008 3:12 PM

All replies

  • I assume your stored pocedure has 6 parameters but you supply only 1 in tsql. Try this:

     

    $tsql = "call dbo.usp_T_Usuarios (?, ?, ?, ?, ?, ?)";

    One more thing... @action char(1)=null output seems to be actually input parameter not output.
    Monday, March 24, 2008 10:15 PM
    Moderator
  • Well, I try with these code but send me:
    Array ( [0] => Array ( [0] => 07002 [SQLSTATE] => 07002 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Campo COUNT erróneo o error de sintaxis [message] => [Microsoft][SQL Native Client]Campo COUNT erróneo o error de sintaxis ) )

    and my var in sql i need some time how output because I declare it in this way.

    when i use mssql_init,mssql_bind and  mssql_execute this stored procedure is executed successfully but I have more steps, and I can't do a connection function in a class, same a msqli function connection, for example:

    class Base
        {
            protected $conexion = null;
           
            function __construct()
            {
                if($this->conexion == null)
                {
                    $this->conexion = new mysqli("host","user","pass","database") or die("no se pudo conectar a la base de datos");
                }
            }

    }

    I try to do a class same but with mssql function isn't possible. And I try with the sqlsrv_ function but yet I don't get to connect.
    Tuesday, March 25, 2008 12:22 AM
  • I feel you're doing something wrong with SP parameters. As far as my Spanish goes I understand that "COUNT field incorrect or syntax error".

     

    You can read about calling SP with parameters here: http://technet.microsoft.com/en-us/library/ms131364.aspx

     

    You can either pass parameters explicitly or via "?" markers. You can't omit a single parameter. If your SP has 6 parameters you must have 6 values in the params array.

     

    Please post your complete code.

    Tuesday, March 25, 2008 1:08 AM
    Moderator
  • As Oleg said, there is a problem with your stored procedure. So going up, I find two problems in your stored procedure:

     

     

    create procedure [dbo].[usp_procedure]
    @var1 int=null output,
    @var2 varchar(25)=null output,
    @var3 varchar(25)=null output,
    @var4 varchar(25)=null output,
    @var5 varchar(25)=null output,
    @action char(1)=null output
    as
    begin
      set nocoutn on;
    if @action = 'S'
    begin
       select * from t_table order by @var1
    end

     

     

    First problem: "set nocoutn on" should be "set nocount on" (this is a syntax error)

    Second problem: You are trying to execute a dynamic SQL (order by @var1). This is not supported the way you are doing, you probably want something like

     

    begin

       declare @sql varchar(50)
       set @sql  ='select * from t_table order by ' + @var1

      exec @sql
    end

     

    Note that for security reasons (SQL Injection potential), this pattern is not recommended. You should at the very least validate that @var1 is a valid field, and nothing else; and even better, just get rid of this dynamic sql.

    Tuesday, March 25, 2008 11:24 PM
    Moderator
  • Sorry for my english but i try to communicate my idea. Ok, you are right, but I wrote the script of stored procedure only for example really my stored procedure is writed with this line:

    select * from t_table order by var1

    Really my stored procedure work well whit the next code or when I execute on the Management Studio:

                    $link = mssql_connect("host", "user", "pass") or die("No se pudo conectar a $servidor");
                    $selectdb = mssql_select_db("db", $link) or die("No se pudo abrir la bd $nombreDB");
                    $sp = mssql_init("db.dbo.usp_T_Usuarios", $link);
                   
                    $Accion = "S";
                    mssql_bind($sp, "@Accion", &$Accion, SQLCHAR, true) or die("Unable to bind $sp_nameEmbarrassedkey<br>".mssql_get_last_message());
                   
                    $result=mssql_execute($sp);
                    echo "Resultado<br>"; 
        /*            while ($row = mssql_fetch_assoc($result))
                    print_r($row);*/
                    while($row=mssql_fetch_array($result))
                    {
                        for($i=0;$i<count($row)-1;$i++)
                            echo $row[$i]."<br>";
                        echo "<hr>";
                    }
       
                    mssql_close($cn);


    How I told with this connection I don´t make a class connection, because I try whit SQLSRV for make a class connection, the problem is in this code:

    $serverName = "host";
    $connectionInfo = array( "UID"=>"user",
                             "PWD"=>"pass",
                             "Database"=>"db");
    echo "conectando...<br><br>";
    if( !($conn = sqlsrv_connect( $serverName, $connectionInfo)))
    {
         echo "No se pudo conectar.....<br>";
         die( print_r( sqlsrv_errors(), true));
    }
    else
    {
        echo "La conexion fue exitosa.";
    }
    $Accion="S";
    $salida=null;

    // Set up T-SQL query.
    $tsql = "{call usp_T_Usuarios(?,?,?,?,?,?,?)}"; // with your suggest *******************

    // Assign parameter values.
    $params = array($Accion); // maybe here is the error because I only add one parameter, and I don't need more parameters, or how can I add null parameters?? ********************

    // Specify types for parameters.

    $stmt = sqlsrv_query( $conn, $tsql, $params);
    echo "<br><br>Realizado.....<br><br>";
    // Create and execute the statement. Display any errors that occur.
    if(!$stmt)
    {
         echo "Hubo un error ejecutando el procedimiento.<br>";
         die( print_r( sqlsrv_errors(), true));
    }
    else
    {
        echo "<br>La se realizo la consulta.<br><br>";
        echo $stmt;
    }

    I read your suggest and see the posible problem I mark this thing with ***************** in the code, ok I am working with my before code but I try once, I think that is posible. And thanks for help me.
    Wednesday, March 26, 2008 4:44 PM
  •  

    We have a known issue in our last CTP with parameter bidning and complex statements. You saying this works with mssql also proves that this is likely the error in the driver. The issue will be fixed in our next CTP. If you provide a full repro I can verify your case is actually will be fixed in the next CTP.

    Muchas Gracias,

    Mugunthan

    Wednesday, March 26, 2008 5:38 PM
  • There are a few problems with your code:

    1. You must pass 6 parameters in $tsql. For test sake your could pass anything you want for other parameters except action. It's not necessarily to pass NULL.

    2. Logics error: $Action must be the last item of the $tsql

     

    Please let me know if it helps.

    Thursday, March 27, 2008 6:19 PM
    Moderator
  • Looking at the procedure:

     

    create procedure [dbo].[usp_procedure]
    @var1 int=null output,
    @var2 varchar(25)=null output,
    @var3 varchar(25)=null output,
    @var4 varchar(25)=null output,
    @var5 varchar(25)=null output,
    @action char(1)=null output

     

    Do you really need all those output parameters? If so, you should assign all of them, even if they are null (but maybe you would prefer to change their values to an empty string '' )

     

    As it has been said above, you should remove the Output from Action, if it is really an input parameter

     

    Trying your code removing the unused parameters should validate if you are hitting a driver problem, or if it is just your code; add parameters one at a time if you need them to test.

     

    Friday, March 28, 2008 11:19 PM
    Moderator
  • you can refer this example : http://www.varindersandhu.in/2011/11/17/sql-server-stored-procedure-with-output-parameters/
    If you think my suggestion is useful, please rate it as helpful.

    If it has helped you to resolve the problem, please Mark it as Answer.

    Varinder Sandhu www.varindersandhu.in
    Wednesday, November 23, 2011 6:04 AM