I have a table, call it TBL. It has two columns,call them A and B. Now in the query I require one column as A and other column should be a comma seprated list of all B's which are against A in TBL. e.g. TBL is like this 1 Alpha 2 Beta 1 Gamma 1 Delta Result of query should be 1 Alpha,Gamma,Delta 2 Beta This type of ...
I see Oracle procedures sometimes written with "AS", and sometimes with "IS" keyword. CREATE OR REPLACE Procedure TESTUSER.KILLINSTANCE (INSTANCEID integer) **AS** ... vs. CREATE OR REPLACE Procedure TESTUSER.KILLINSTANCE (INSTANCEID integer) **IS** ... Is there any difference between the two? Edit: Apparently, there is no funct...
I have a table that has a column with a default value: create table t ( value varchar(50) default ('something') ) I'm using a stored procedure to insert values into this table: create procedure t_insert ( @value varchar(50) = null ) as insert into t (value) values (@value) The question is, how do I get it to use the defaul...
Do you prefer to use stored procedures or parameterized queries? What is your argument supporting your choice? Is one method really any better than the other in terms of performance, security, maintainability... etc? ...
I’m listening to the Hanselminutes Podcast; "StackOverflow uses ASP.NET MVC - Jeff Atwood and his technical team". During the course of the Podcast they are speaking about SQL server and say something along the lines of 'The days of the Stored Procedure are over'. Now I'm not a DBA but this has taken me a bit by surprise. I always assu...
In SQL Server 2005, I want a user, called LimitedUser, to only be able to run one proc: GRANT EXEC ON [usp_RunETL] TO [LimitedUser] However, that proc needs to be able to do everything -- UPDATE, DELETE, INSERT, EXEC.. everything. How do I do that without having to give all those permissions to LimitedUser? ...
I have a report that renders data returned from a stored procedure. Using profiler I can catch the call to the stored procedure from the reporting services. The report fails stating the report timed out yet I can execute the stored procedure from SSMS and it returns the data back in five to six seconds. Note, in the example test run o...
I have seen various rules for naming stored procedures. Some people prefix the sproc name with usp_, others with an abbreviation for the app name, and still others with an owner name. You shouldn't use sp_ in SQL Server unless you really mean it. Some start the proc name with a verb (Get, Add, Save, Remove). Others emphasize the entit...
I am writing a C# Windows Forms application which calls Oracle stored procedures. I chose to use typed datasets in the application, these correctly populate various datagrids, but I am having trouble when invoking the UpdateCommand or the InsertCommand. I have manually coded these commands because a) I am using Oracle stored procedures...
Lets say on MySQL database (if it matters). ...
Can I call a stored procedure in Oracle via a database link? The database link is functional so that syntax such as... SELECT * FROM myTable@myRemoteDB is functioning. But is there a syntax for... EXECUTE mySchema.myPackage.myProcedure('someParameter')@myRemoteDB ...
I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a million records to a dataset using a data adapter, and dataset, and the dataadapter.Fill(dataset) method on the results from the stored proc. ...
How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause? This is what I am trying to achieve. I do not know how to declare in PLSQL for passing a variable list of primary keys of the rows I want to update. FUNCTION EXECUTE_UPDATE ( <parameter_list> value IN int) R...
One of the "best practice" is accessing data via stored procedures. I understand why is this scenario good. My motivation is split database and application logic ( the tables can me changed, if the behaviour of stored procedures are same ), defence for SQL injection ( users can not execute "select * from some_tables", they can only call ...
Besides the syntactic sugar and expressiveness power what are the differences in runtime efficiency. I mean, plpgsql can be faster than, lets say plpythonu or pljava? Or are they all approximately equals? We are using stored procedures for the task of detecting nearly-duplicates records of people in a moderately sized database (around 1...
I have an unmanaged C++ application that uses the COM ADOConnection object. The application makes use of the BeginTrans/CommitTrans methods on the object to control transaction behavior. I recently added a CLR stored procedure to do some Regex stuff (and got some wicked fast processing, much better than pulling the data back to C++, pr...
I am calling a SQL proc that has 3 OUTPUT params. After the call to the proc one of the params does not return a value when the other two do. Profiler shows that all 3 values are being returned. The params are declared as follows in the proc... @UsrVariableID INT OUTPUT, @OrganisationName NVARCHAR(256) OUTPUT, @Visible bit OUTPUT and...
When I try to run a particular stored procedure on my MS SQL 2005 database, I get an error like the following: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression The SP in query is very long and calls other SPs. This error is obviousl...
We seem to have a few developers here who think creating stored procedures that spit out HTML or Javascript code is a legitimate thing to do. In my mind this is the ultimate abuse of the separation of concerns model. Is doing something like this people have often seen people doing? ...
How do I find a stored procedure in a Sybase database given a text string that appears somewhere in the proc? I want to see if any other proc in the db has similar logic to the one I'm looking at, and I think I have a pretty unique search string (literal) Edit: I'm using Sybase version 11.2 ...