Inside MySQL I have written a Procedure where I want to run a loop like this: While (Cond) Do ...(Body1) ... If (Condition2) continue ; ...(Body2) ... end while ; Under the while loop I want the full body to run in case where Condition2 is not met (ie Body1 and Body2). Currently, when Condition 2 is met, it just execut...
Anyone know a simple query to grab a list of all stored procedures in a SQL 2005 database ordered by createdate? ...
I am able to view the Estimated Execution Plan (Management Studio 9.0) for a query without a problem but when it comes to stored procedures I do not see an easy way to do this without copying the code from the ALTER screen and pasting it into a query window, otherwise it will show the plan for the ALTER and not the procedure. Even after ...
I'm trying to load some java stored procedures into an Oracle 10g database through JDBC. The statement I'm executing is - CREATE OR REPLACE JAVA SOURCE NAMED "test.Test" AS package test; public class Test { public static String myMethod(String a) { return a; } }; Running this through TOAD works just fine, but when running...
I use database scripts where I check for the existence of a Stored Procedure then drop it then create it. Which of the following would be more efficient for checking and dropping SPs Option 1 IF EXISTS(SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[myStoredProc]',N'P')) DROP PROCEDURE dbo.myStoredProc; Option 2 IF ...
Is it possible to execute a stored procedure in a view? I.e. CREATE VIEW [dbo].[v_ReportInvoiceClientsThisMonth] AS EXEC [dbo].[GetInvoiceClients] @startDate = '2009-03-01', @endDate = '2009-04-01' (doesn't work) The reason I need this is that I need a way to access the SP in Excel (in an idiot safe way, i.e. without VBA). ...
Why opensource database like Postgresql and Mysql don't have encrypted stored proc? Is it because of their innate open source philosophy? What are the compelling reasons to encrypt the stored procs? ...
With the following example stored procedure DECLARE Variable DOUBLE; DECLARE Variable2 DOUBLE; SELECT Something FROM Somewhere INTO Variable; SELECT Something FROM SomewhereElse INTO Variable 2; SELECT (Variable + Variable2); If either Variable or Variable2 are NULL then the final SELECT will return null, what I would like is that i...
I have a column in SQL Server 2005 that stores a simple chunk of XML. At a later point processing is performed and I need to merge some processing info into the XML. While I can do this at an intermediate point I would much prefer to keep this method centraliazed within the stored procedure that is responsible for updating other fields...
I was looking at the source of sys.sp_dbcmptlevel in SQL Server 2005. In the source, there is this line I do not understand how it works. EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @input_cmptlevel) It doesn't appear that DatabaseEx is a stored procedure. -- does not return any result select * from sys.procedures whe...
we need to limit for how long a stored proc can run, currently we check the current timestamp against the input parameter timestamp for process to end. but has anyone used asutime limit and what are its effects/advantages? ...
Howdy! I'm still not getting my SP's generated in SubSonic 2.2 against an Oracle 10g DB. The Tables and Views generate up perfectly. Also this product is multiplatform so we're gen'ing up SubSonic libraries against SQL 2K5 and that works great for Tables/Views and SP's. I recall on the old forums there was a bug in earlier versions o...
Hi. I'm trying to SELECT from the dba_tab_cols view from within a stored procedure. It's not working and I don't know why. If I execute the following SQL as a query: SELECT t.data_type FROM dba_tab_cols t WHERE t.table_name = 'ACCOUNTTYPE' AND t.column_name = 'ACCESSEDBY'; it works fine. However if I copy it into a stored p...
Can't find any answers on the net, has anyone tried this? My stored proc processes deletion of tokens found across multiple tables and it is set to run for a specified number of "cpu service units" by using the ASUTIME LIMIT integer stored proc definition/characteristic. When this limit is met, will the stored proc commit or rollback th...
I am designing a fairly complex database, and know that some of my queries will be far outside the scope of Django's ORM. Has anyone integrated SP's with Django's ORM successfully? If so, what RDBMS and how did you do it? ...
I have a stored procedure which when run from SQL Server Management Studio consistently takes 5 seconds to run when called like this. exec dbo.MyStoredProc '2009-04-30 00:00:00', '2009-04-30 20:00:00' When called from an excel spreadsheet via VBA it takes 6 minutes plus (not including the time taken to copy the recordset to a sheet. ...
I'm running a website for a jewellery wholesaler. The prices on all their products are calculated using the current bullion metal fixes that are updated every night. Currently, for the website, the calculations are worked out via a php include function, which works fine under current circumstances. There are around 10,000 products, bu...
I have a stored procedure.. Proc: spMyStoredProcedure Takes a Param: @List varchar(255) The @List would be a comma separated list of values i.e... @List = '1,2,3' (clarity.. a single value of 1 would mean all records with col1 = true) I have a table with these columns: ID int, col1 bit, col2 bit, col3 bit, col4 bit. ID | col1 | ...
I am trying to update a column in my table which was last inserted. I tried creating this stored procedure: CREATE PROCEDURE [dbo].[msp_AssociateEvent] ( @EventId int ) AS UPDATE tblFoodMenus set EventID = @EventId Where FoodMenuID = IDENT_CURRENT(tblFoodMenus) but it gives me this error: Invalid column name tblFoodMenus. Am ...
Hi there, Can anyone help? I have been using the entity framework and its going well :-) But i have just added a new record using an entity class like so this._entities.AddToUsers(user); The AddTo is created automatically .. and users is my table... All great but how do i return the identity column? The sounds like just what i need ...