I'm need to write a stored procedure for SQL Server 2008 for performing a large select query and I need it to filter results with specifying filtering type via procedure's parameters. I found some solutions like this: create table Foo( id bigint, code char, name nvarchar(max)) go insert into Foo values (1,'a','aaa'), (2,'b','bbb')...
SP_A is a stored procedure that calls SP_B, then does a SELECT, and then UPDATES the same records/column that SP_B just UPDATED. SP_A has a TRANSACTION around the SELECT and UPDATE statements followed by a COMMIT. Now, everything works perfectly when I call SP_A from a MySQL command prompt. However, when I call it from C#, it times ou...
Hi guys, I'm struggling with getting the result OUT variable from a MySQL stored procedure. I get the following error: java.sql.SQLException: Parameter number 3 is not an OUT parameter The stored procedure looks like this: CREATE DEFINER=`cv_admin`@`localhost` PROCEDURE `CheckGameEligibility`( IN gID INT(10), IN uID INT(10), ...
This is a multi-part question. I have a table similar to: CREATE TABLE sales_data ( Company character(50), Contract character(50), top_revenue_sum integer, top_revenue_sales integer, last_sale timestamp) ; I'd like to create a trigger for new inserts into this table, something like this: CREATE OR REPLACE FUNCTION add_cont...
Which is faster in SQL, While loop, Recursive Stored proc, or Cursor? I want to optimize the performance in a couple of spots in a stored procedure. The code I'm optimizing formats some strings for output to a file. ...
Hey guys! I created .NET-based AEP for ADS v10 beta. Here is a procedure code. .NET side: public int TestSP(int connectionId, int connection, ref int numRowsAffected) { AdsConnection cnn; lock (_connections) cnn = _connections[connectionId]; using (var cmd = cnn.CreateCommand()) { cmd.CommandText = "INS...
Problem statement: A table contains an item_id, a category_id and a date range (begin_date and end_date). No item may be in more than one category on any given date (in general; during daily rebuilding it can be in an invalid state temporarily). By default, all items are added (and re-added if removed) to a category (derived from outsi...
Using LINQ 2SQL , How can i call a stored procedure which returns either 1 or 0 when being executed.My procedure has 3 input params.I want to know the return value from procedure in my code too. ...
I am using CONTEXT_INFO to pass a username to a delete trigger for the purposes of an audit/history table. I'm trying to understand the scope of CONTEXT_INFO and if I am creating a potential race condition. Each of my database tables has a stored proc to handle deletes. The delete stored proc takes userId as an parameter, and sets CON...
Hi, i have an SP like BEGIN DECLARE ... CREATE TEMPORARY TABLE tmptbl_found (...); PREPARE find FROM" INSERT INTO tmptbl_found (SELECT userid FROM ( SELECT userid FROM Soul WHERE .?.?. ORDER BY .?.?. ) AS left_tbl LEFT JOIN Con...
Is there a statement that can drop all stored procedures in MySQL? Alternatively (if the first one is not possible), is there such thing as temporary stored procedures in MySQL? Something similar to temporary tables? So far, for both tasks, I seem not to find any answers although there were people asking for those in other forums. Than...
Hi I have this SP USE [TestDB] GO /****** Object: StoredProcedure [dbo].[sp_test] Script Date: 06/12/2010 11:47:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_test] @id uniqueidentifier AS BEGIN select * from TestTbl where ProductId= @id END I then went to the SP with ms sql 20...
How to write stored procedure in SQL Server 2008 that generates 3 million random no in two columns in integer datatype. ...
I'm looking to write a Oracle stored procedure where I would pass in (from ColdFusion) an array of structures and loop over each iteration to insert the bits and pieces within the structures to the DB., I haven't written this type of procedure / package before. I am planning to do an sp / package similar to what is sketched out in the...
I've written a stored procedure in MySQL to take values currently in a table and to "Normalize" them. This means that for each value passed to the stored procedure, it checks whether the value is already in the table. If it is, then it stores the id of that row in a variable. If the value is not in the table, it stores the newly inserted...
I want to add column to table by stored procedure and the name of column should be parameter.'s value. ...
Hello, I was hoping to find an easy way to get a parameter list of a storec procedures parameters. If the proc has 3 params, I want a list like that: param1 param2 param3 It would be best to be able to do that in C# Code, but SQL would suffice as well. Ideas? Thanks!! :-) ...
Hi, I have a stored procedure that, ending with a SELECT, returns a recordset. I can call it within anoher stored procedure like this: EXEC procedure @param How to get the returning recordset? Thanks ...
I am working with an insert trigger within a Sybase database. I know I can access the @@nestlevel to determine whether I am being called directly or as a result of another trigger or procedure. Is there any way to determine, when the nesting level is deeper than 1, who performed the action causing the trigger to fire? For example, w...
Would this be better as a stored procedure or leave it as is? INSERT INTO `user_permissions` ( `user_id`, `object_id`, `type`, `view`, `add`, `edit`, `delete`, `admin`, `updated_by_user_id` ) SELECT `user_id`, $object_id, '$type', 1, 1, 1, 1, 1, $user_id FROM `user_permissions` WHERE `object_id` = $object_id_2 AND `t...