Differences

This shows you the differences between two versions of the page.

--- v5:userguide:stored_procedure [2018/06/07 09:26] – [Step 2: Setting Parameters] add wrap to highlight requirement to initialize variables; formatting and rewording dregad
+++ v5:userguide:stored_procedure [2019/01/22 01:11] (current) – Updated recordset retrieval example mnewnham
@@ Line 23: / Line 23: @@
   SET v_PARAM2=PARAMETER2;
   SELECT MAX(ACTNO) INTO v_INT FROM act;
-  INSERT INTO act (ACTNO,ACTKWD,ACTDESC) SELECT v_INT+10,v_PARAM1,v_PARAM2 FROM SYSIBM.SYSDUMMY1;
+  INSERT INTO act (ACTNO,ACTKWD,ACTDESC)
+         SELECT v_INT+10,v_PARAM1,v_PARAM2 FROM SYSIBM.SYSDUMMY1;
   SET PARAMETER3=v_INT+10;
   END
@@ Line 89: / Line 90: @@
 The values of any output parameters are set by the execute statement, and may be accessed without further action.
 ==== Retrieving Recordsets ====
- If the stored procedure was one that returned a recordset, that can be accessed via the result of the execution, in the normal way.
+ If the stored procedure was one that returned a recordset, that can be accessed via the result of the execution, in the normal way. This is an example using the SQL Server demo database **Northwnd**.
+<code php>
+/*
+* SQL Server connection assumed
+*/
+/*
+* Because the procedure name has spaces in it,
+* we must surround the procedure name using [ ] . You can also use " "
+*/
+$procedure = $db->prepareSp('[PR_Alphabetical list of products_SelectAll]');
+if (!$procedure)
+    die ('Invalid or inaccessible stored procedure name');
+/*
+* Use the procedure name as the source of the data.
+* You can use any of the ADOdb commands that retrieve recordsets
+*/
+$result = $db->execute($procedure);
+while($r = $result->fetchRow())
+	print_r($r);
+/*
+* Prints
+Array
+(
+    [productid] => 7
+    [productname] => Uncle Bob's Organic Dried Pears
+    [supplierid] => 3
+    [categoryid] => 7
+    [quantityperunit] => 12 - 1 lb pkgs.
+    [unitprice] => 30.0000
+    [unitsinstock] => 15
+    [unitsonorder] => 0
+    [reorderlevel] => 10
+    [discontinued] => 0
+    [categoryname] => Produce
+)
+etc
+*/
+</code>