Hi all, could you point me to a good place to start with Oracle stored procedures syntax/usage? I can't seem to find any good place there. I'm fairly proficient in (java, C/C++) programming and I do know enough SQL for my needs right now, but I've been suggested to use stored procedures to do my business, which is: Take results from a q...
Hi, I currently have the following stored procedure; CREATE PROCEDURE web.insertNewCampaign ( @tmp_Id BIGINT, @tmp_Title VARCHAR(100), @tmp_Content VARCHAR(8000), @tmp_Pledge DECIMAL(7,2), --@tmp_Recipients BIGINT, @tmp_Date DATETIME, @tmp_Private BIT, @tmp_Template BIGINT, @tmp_AddyBook BIGINT ) AS ...
I have a client with a hosted MySQL database whose developer keeps asking me to add really simple stored procedures. I look at a stored procedure like this, and I don't see any reason why it would be implemented as a stored procedure and not implemented within application code. Am I correct that this is really strange use of stored pro...
Helo, My question is I have one Stored Procedure in SQL Server that returns counts of a field. I want to store the results of this Stored Procedure in a variable (scalar?) of a different stored procedure. sp_My_Other_SP: CREATE PROCEDURE [dbo].sp_My_Other_SP @variable int OUTPUT -- The returned count AS BEGIN -- SP SET NOCOUNT ON; ...
How do I delete a stored procedure in postgresql? ...
Hi, what is the short cut to get entire text of the stored proc in query analyzer. I know that I could highlight table name and hit <alt><f1> and get the entire table structure. ...
How do I write a simple stored procedure in postgres that doesn't return a value at all? Even with a void return type when I call the stored procedure I get a single row back. CREATE FUNCTION somefunc(in_id bigint) RETURNS void AS $$ BEGIN DELETE from test_table where id = in_id; END; $$ LANGUAGE plpgsql; ...
I'm calling Sqlcommand.ExecScalar() - stepping through the stored proc works fine, right under RETURN @RecordNum @RecordNum correctly contains a bigint, as scoped. When I step into the RETURN.. I get an exception thrown, that visual studio does not seem to be able to capture. The stored procedure works fine when executed directly, a...
Can I use a MySQL function or procedure to return table names that can be used in a query? Something like SELECT * FROM get_db_table_name(2342352412); where get_db_table_name() is a user defined function which accepts a user_id as a parameter and returns the db.table where that user resides. I have not been able to do this this way be...
For some reason my stored procedures are all executing twice! I have a static function that runs an SP given its name and the parameters and fills a datatable. Public Shared Function RunSP(ByVal spName As String, ByRef spParams As Dictionary(Of String, String), ByRef pDataTable As DataTable) As Integer Dim cmd As New SqlCommand ...
I write a simple procedure. I try to store selection result in variable. I use "SELECT INTO" query but I can not doing this. Example: DECLARE v_employeeRecord employee%ROWTYPE; BEGIN SELECT * INTO v_employeeRecord FROM Employee WHERE Salary > 10; END; ...
I create a simple type: create or replace TYPE SIMPLE_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20)); Simple test: DECLARE TYPE ObjectList IS TABLE OF SIMPLE_TYPE; tmp SIMPLE_TYPE := SIMPLE_TYPE(1, 'a'); o ObjectList := new ObjectList(SIMPLE_TYPE(2, 'a'), SIMPLE_TYPE(3, 'a')); BEGIN IF tmp.EXISTS(tmp) THEN dbms_out...
I have a SQL Server 2008 database with a bunch of stored procedures. When I use the ActiveRecord Template provided with Subsonic 3.0.0.3, it generates methods for all of my stored procedures, but they do not have any parameters. I am dbo on the server and can execute the stored procedures with no issue from Management studio. Example St...
An Activity occurs at a location on a specific date, but each activity may be repeated at the same or different locations on different dates. I have created an entity framework model, and wish to populate it with relevant Activities which occur between two dates, ordered by the locations distance from a specified location. I therefore ...
Hi, I am working on ASP.NET 2.0 application with c# language. Can anyone tell me which is the best way of the following : scenario: I need to get data from the database and bind it to the grid view. case 1: I can use a stored procedure(for iteraing the result which is obtained from the basic query and do operations on this result set)...
I write simple procedure. DECLARE connection_id LINE.CONNECTION_ID%TYPE := 11009; tmp_integer INTEGER; BEGIN SELECT COUNT(*) INTO tmp_integer FROM LINE WHERE LINE.CONNECTION_ID = 11009; DBMS_OUTPUT.PUT_LINE(connection_id); DBMS_OUTPUT.PUT_LINE(tmp_integer); END; Result of the launch: 11009 3 It is good result. I have only...
I have a sql table called UsersInAuthentication like ----------------------- | AuthUserId | UserId | | 1 | 4 | | 1 | 5 | | 1 | 8 | ----------------------- I wish to get all users in format "(4,5,8)" as string with a single stored proc and If possible I also wish to insert,update,delete operat...
This page from SQL Server 2008 BOL, talks about CLR Stored Procedures and has a section labelled, "Table-Valued Parameters", which talks about how they can be advantageous. That's great - I'd love to use TVPs in my CLR procs, but unfortunately this seems to be the only reference in the universe to such a possibility, and the section does...
I wrote a stored-procedure in Oracle and now, I want to launch it in Java code. I will describe a problem. I have a object type: TYPE PERSON_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20)); And table type: TYPE PERSON_TYPE_TABLE AS TABLE OF PERSON_TYPE; My procedure looks like this: PROCEDURE EVALUATE_PERSON_PROC(P_PERSON_ID IN ...
Where I work it is a requirement for us to go through stored procedures as a mechanism to access our data through code. I am using LINQ2SQL to minimize this pain so that I can work with objects instead of ADO.NET directly. I have a situation Linq2SQL is consuming one of my stored procedures an generating code where the return type from...