stored-procedures

How do I use the results of a stored procedure from within another?

I have a stored procedure that I want to call from within another, and then loop through the results. Sort of like using a cursor with a stored procedure rather than a SQL select statement. I can't quite figure out how to do it. I can get the whole result like this: DECLARE @result int; EXEC @result = sp_who; PRINT @result; Interesti...

What good tutorials you know about MySQL Stored Procedures?

I want to write a stored procedure to increment the value of an int column by one. Looks like a very simple task, but not for someone without ANY experience with stored procedures. To do that I looked for tutorials and code samples, and found a few, but sure there are better ones out there. Do you know any? The ones I found: http://d...

track all the versions of stored procedure which is modified multiple times.

If a stored procedure is modified 10 times , i want to log or track it in a table in a column in 10 rows from initial stored procedure to the latest that is 10 different versions of the same stored procedure , could any one suggest how to do this? ...

How to wrap an Oracle stored procedure in a function that gets executed by a standard SELECT query?

I am following these steps, but I continue to get an error and don't see the issue: 1) Create a custom Oracle datatype that represents the database columns that you want to retrieve: CREATE TYPE my_object AS OBJECT (COL1 VARCHAR2(50), COL2 VARCHAR2(50), COL3 VARCHAR2(50)); 2) Create another datatype...

MySQL : When stored procedure parameter name is the same as table column name

Hello, Let's say a have a stored procedure SetCustomerName which has an input parameter Name, and I have a table customers with column Name. So inside my stored procedure I want to set customer's name. If I write UPDATE customers SET Name = Name; this is incorrect and I see 2 other ways: UPDATE customers SET Name = `Name`; UPDATE cu...

MySQL : When stored procedure parameter name is the same as table column name [continue]

Hello, Let's say a have a stored procedure SetCustomerName which has an input parameter Name, and I have a table customers with column Name. So inside my stored procedure I want to set customer's name. If I write UPDATE customers SET Name = Name; this is incorrect and I have to write (for example) UPDATE customers SET `Name` = Name;...

Invalid object name error when trying to execute stored procedure?

Not sure what the deal is I have the stored procedure named exactly what I am calling however it always gives me this invalid object error. Here is the connection code, the error is thrown on the second to last line there. SqlConnection cnstr = new SqlConnection(ConfigurationManager.ConnectionStrings["darconn"].ConnectionString); ...

Unit Testing the Data Access Layer - Testing Update Methods?

I'm looking into adding some unit tests for some classes in my data access layer and I'm looking at an update routine that has no return value. It simply updates a row based on the id you provide at whichever column name you provide. Inside of this method, we collect the parameters and pass them to a helper routine which calls the st...

getting returning value from a stored procedure in postgres

Hi. I have a stored procedure in pqsql: CREATE OR REPLACE FUNCTION dosmth() RETURNS boolean AS $BODY$BEGIN RETURN FALSE; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE From ado.net i need to retrieve the return value. I try to do the following. DbCommand cmd = DBTemplate.CreateStoredProcedureCommand(dbConnection, "dosmth"); cmd.Pa...

What's the best practice of naming stored procedure for t-sql?

I have worked with several big databases and the names of stored procedures were very different: SP_PrefixXXX PrefixYyyXxx Prefix: Rep, Act What's the best practice of naming? How can I organize them in a proper way? ...

Mysql too many while loops inside one stored procedure

Hi! I have one stored procedure in while I am getting comma separated values in parameters. I have three parameters which has comma separated values. and i need to put them in table's columns so I am using while loop. but i am scared when too many(say lakhs of users) users will connect to my website then my procedure will have performanc...

Should my delete stored procedure account for cascading to FK tables?

Say I have a Employees table (PK is employeeID) and a sales table, where the sales table has a FK column for employeeID. Now when designing my deleteEmployee stored procedure, should I first delete rows in the Sales table or should I create seperate stored procedures to delete in each table, and then worry about that in my business logi...

MySQL: Initialize a summary table with placeholders for non-existent data

I'll try to give this as generically as I can so it's reusable. I am running a site with a fairly large MySQL database which has grown to need some summary/rollup tables initialized. For the example's sake, let's say it's soccer statistics. Since I handle multiple soccer leagues in the same database, many of them play games of differe...

Oracle/PLSQL performance

Is there any difference in performance when you break down a stored procedure into multiple procedures instead of having a big procedure?! wich one is faster?! for example: mainSP   callSP1   callSP2   callSP3 end; rather than SP .... ..... .... Thanks guys. ...

I want to back up stored procedure in mysql

I use mysqldump with mysql 5.0 and I back it up every day, but do not understand the method that only stored procedure backs up. How can I back it up? ...

How can I call a stored procedure from Crystal Reports?

I have a stored procedure that takes a user ID and calculates their balance with a really simple query and returns it. I want to add this to a crystal report in my application. The only problem is, Crystal Reports wants me to set a value for the procedure, and setting a single value for it would be useless to me. I have everything group...

Running Sql Server stored proc in context of caller

This is making me nuts and I'm sure the answer is SO easy. I have multiple schemas that each have a view named "Task". I want to make a single stored proc that users running in multiple default schemas can execute successfully -- that stored proc does a select on the Task view. So say I have these objects: View: fr.Task (users with d...

How can I use more than one Stored Procedure in a crystal report?

I have two stored procedures I wish to use in my stored procedure, but once I do that it fails to load with the error: "Invalid Argument provided, no rowset retrieved." If I remove either one of them it starts working again. I have the crystal report set up something like this: Report: Group By Tenant.ReferedBy stored proc here t...

Import DBF files into Sql Server

I need a little help figuring this out because I'm new to stored procedures. I am trying to import a .DBF table into Sql Server 2008 using this store procedure. CREATE PROCEDURE spImportDB -- Add the parameters for the stored procedure here AS BEGIN -- Insert statements for procedure here SELECT * into Products FROM OPENROWSET('vfp...

How do I execute a stored procedure once for each row returned by query?

I have a stored procedure that alters user data in a certain way. I pass it user_id and it does it's thing. I want to run a query on a table and then for each user_id I find run the stored procedure once on that user_id How would I write query for this? SQL SERVER ...