stored-procedures

Php mysqli and stored-procedure

I have a stored procedure: Create procedure news(in dt datetime,in title varchar(10),in desc varchar(200)) Begin Insert into news values (dt,title,desc); End Now my php: $db = new mysqli("","","",""); $dt = $_POST['date']; $ttl = $_POST['title']; $desc = $_POST['descrip']; $sql = $db->query("CALL news('$dt','$ttl','$desc')"); if($s...

How Can I do this??

I have a table records(ID, ParentID) containg this data ID ParentID 1 null 2 1 3 2 4 2 5 3 6 null 7 6 If you draw this table in hierarchy as a family 1,2,3,4,5 will be related to each other. I want to find a way, where I can pass an ID like 3 it give me the others family members using SQL or us...

How to write stored procedures to separate files with mysqldump?

The mysqldump option --tab=path writes the creation script of each table in a separate file. But I can't find the stored procedures, except in the screen dump. I need to have the stored procedures also in separate files. The current solution I am working on is to split the screen dump programatically. Is there a easier way? The code I...

advanced select in Stored Procedure

Hey i got this Table: CREATE TABLE Test_Table ( old_val VARCHAR2(3), new_val VARCHAR2(3), Updflag NUMBER, WorkNo NUMBER ); and this is in my Table: INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('1',' 20',0,0); INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 20',0,0); I...

Linq vs Stored Procedure

which one is preferable for Enterprise CMS development. LINQ or SP ...

.NET: Writing DataAccess dll

I would like to write all data relations processes (general functions regarding with DataAccess via .NET) in dll and I want to use it repeatedly. What kinds of functions should have in that dll? Some want to use Stored Procedures , Some with Statements. Can you all suggest me? Please guide me! Thanks all! ...

Please tell me difference between running query directly and executing it using exec

Please tell me what is difference ==> if i write query directly in storedprocedure ==> and write query in string variable and than run it in exec in stored procedure. i am using ms sql server 2005 ...

exit from a stored procedure

i hv some loop and a condition..if codition is match then i want to stop or exit from the stored procedure..how to do that? while @@fetch_status=0 begin if x=0 'exit stored procedure end ...

How can I exit an inner loop and continue an outer loop?

This is a follow-up to my previous question (Thanks for the answer, BTW!) If I have two loops: while @@fetch_status=0 begin set y=y+1 set x=0 while @@fetch_status=0 begin x=y+1 if y = 5 'exit the second do while and back to the first do while --> y=y+1 end end ...how can I exit from the...

Delete all but 5 newest entries in MySQL table

I currently have PHP code that handles the logic for this because I do not know how to handle it in SQL. I want to create a stored procedure that will delete all the rows except for the 5 newest for a given config_id. IE config_id = 5 gets passed to the SP so it knows which config_id it is looking to clean up. CREATE TABLE `TAA`.`RunHi...

Association in Linq To SQL showing as an EntitySet<>, why?

I am having a lot of trouble coming up with the Linq equivalent of this legacy stored procedure. The biggest hurdle is it doesn't seem to want to let me add a second 'clause' on the join with tblAddress. I am getting a Cannot resolve method... error. that tblBusiness.tblAddress is seen as an EntitySet<tblAddress> See bottom for curre...

ADO.NET parameters from TextBox

I'm trying to call a parameterized stored procedure from SQL Server 2005 in my C# Winforms app. I add the parameters from TextBoxeslike so (there are 88 of them): cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar, 100).Value = CustomerName.Text; I get the following exception: "System.InvalidCastException: Failed to convert parame...

Checking for Error during execution of a procedure in oracle

create or replace procedure proc_advertisement(CustomerID in Number, NewspaperID in number, StaffID in Number, OrderDate in date, PublishDate in date, Type in varchar, Status in varchar, Units in number) is begin insert into PMS.Advertisement(CustomerID, NewspaperID, StaffID, OrderDate, PublishDate, Type, Status, Units) valu...

Improve my Zend Stored Procedure calling code.

Hi, I'm wondering how i can improve my Zend code that calls a stored procedure. At the moment i'm using a MySQL DB, and the action function in my controller below works, but it seems nasty. public function callSPAction() { $param = $this->_request->getParam('param', 0); $bootstrap = $this->getInvokeArg('bootstrap'); $confi...

MySQL: How to perform optimization on all tables with a stored procedure?

I need to perform check table, optimize table and analyze table statements on all tables in a database. And I want to do it inside a stored procedure. Is it possible? I can't figure out how to iterate through the table names and then use those names to perform the 3 statements. ...

MySQL Procedures : Standard/Best Place For Code Documentation?

I'd like to put a paragraph or so for documentation on each of my MySQL procedures: date, author, purpose, etc. Is there a standard, accepted, or best place to put this kind of documentation? For example, In MSSQL, some IDE's will generate a template for you to start coding your procedure; it comes with a little place for code documenta...

Mysql: cannot call decode in stored procedure

hello. when create any stored procedure the function decode dont work delimiter $$ create procedure guardausuario( varKusuario varchar(8), varNombre text, varPwd BLOB) begin declare varValor varchar(128); select encode(varPwd,varKusuario) into varValor; insert into usuarios (k_usuario, nombre, pwd) values (varKusuario,varNombre,v...

oracle display for every stored procedure the execution time

Hi all. I'm working on a stored procedure. Inside this one, there are many call to the other stored procedures. There are a bunch of them. I was wondering if there is a option to be able to have the execution time of every stored procedure involved, every function (with a start and end time, ior something like that). The idea is that ...

Having a problem inserting into database

I have a stored procedure: CREATE PROCEDURE busi_reg (IN instruc VARCHAR(10), IN tble VARCHAR(20), IN busName VARCHAR(50), IN busCateg VARCHAR(100), IN busType VARCHAR(50), IN phne VARCHAR(20), IN addrs VARCHAR(200), IN cty VARCHAR(50), IN prvnce VARCHAR(50), IN pstCde VARCHAR(10), IN nm VARCHAR(20), ...

use a variable for table name in mysql sproc

I'm trying to pass a table name into my mysql stored procedure to use this sproc to select off of different tables but it's not working... this is what I"m trying: CREATE PROCEDURE `usp_SelectFromTables`( IN TableName varchar(100) ) BEGIN SELECT * FROM @TableName; END I've also tried it w/o the @ sign and that just tells me ...