SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth Source : Appropriate Uses For SQLite
Your SP is not actually returning multiple tables, its returning a selection of columns and rows from your tables, therefore there is no ‘table name’, and hence why they are named table1, table2 etc. If its important, you could return an extra column for each selection, and in that column fill it with the desired … Read more
To return a value with a stored procedure: For example: SQL : CREATE DEFINER=`user`@`localhost` PROCEDURE `ProcedureName`(IN `Input_Value` INT, OUT `Out_val` INT) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ” BEGIN // Your SQL Code SET Out_val= Your Value; SELECT Out_val; END PHP Code: $insert = “CALL ProcedureName(:Input_Value, @Out_val)”; $bdd = new PDO(‘mysql:host=localhost;dbname=db-name’, … Read more
An SP cannot be optimized with a dynamic table name, so many DBs, MySQL included, don’t allow table names to be specified dynamically. One way around this is to use Dynamic SQL. CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN serviceName VARCHAR(10),IN newsInfoTable VARCHAR(100)) BEGIN SET @sql = CONCAT(‘SELECT COUNT(*) FROM ‘,newsInfoTable,’ WHERE newsServiceName=?;’); PREPARE s1 from @sql; SET … Read more
In playing around with the procedure, I can get a a return -6, if I insert a null into foo.KeyCol and remove the RAISERROR in the inner catch. This is something SQL Server is doing, and is documented here: Return Values from Stored Procedures.
There is a set of commands that are builtin to the mysql client. They’re documented under “mysql Commands.” These include DELIMITER, SOURCE, HELP, CONNECT, USE, QUIT, etc. The \. (or SOURCE) command is one of these builtins. You can’t execute these builtin commands programmatically, nor from within a stored procedure. It’d be like trying to … Read more
You can concatenate the records with a User Defined Function (UDF). The code below can be pasted ‘as is’ into a standard module. The SQL for you example would be: SELECT tbl.A, Concatenate(“SELECT B FROM tbl WHERE A = ” & [A]) AS ConcA FROM tbl GROUP BY tbl.A This code is by DHookom, Access … Read more
‘Rules/limitations for using stored procedures’ in hibernate documentation states that “The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.” (reference … Read more
I don’t think you can with a procedure. Edit: Thanks to DCookie for simplifying my original answer. But as a work-around you can write a function that calls the procedure and then invoke that using SQL. e.g. create or replace function callmyproc return sys_refcursor IS rc sys_refcursor; BEGIN myproc(rc); return rc; END; Which you can … Read more
I tested this using the following stored procedure in SQL Server 2008 R2: CREATE PROCEDURE [dbo].[breakfast] @person varchar(50) = ‘nobody’, @food varchar(50) = ‘tofu’ AS BEGIN SET NOCOUNT ON; SELECT @person + ‘ likes to eat ‘ + @food END The Bad News (“CALL”) I found that sql = “””\ { CALL breakfast (@food=?, @person=?) … Read more