stored-procedures

[ADO] Why can't I get the recordset directly from the stored procedure?

I have a SP as follows. When I execute it with ADO, the first returned recordset is closed. I have to call the NextRecordset method to get the recordset I want. Why is that? How can I avoid the trouble? Thank you. create proc GetTeamCurrentPlan @ReleaseID varchar(30) as set nocount on declare @CurrentSp smallint set @CurrentSp=...

Mapping of stored procedures is all-or-nothing in Entity Framework? (FunctionMapping)

I got the error Cannot find the InsertFunctionMapping for EntityType 'xxx' in the mapping file. Which is fair enough because it is true. But that is because I am happy with EF doing inserts for me. I simply want to override the delete function. I thought that was one of the improvements with EF4? Or is it just that it will bu...

How update Stored Procedure has Optional Parameter?

How can I alter Update Stored Procedure so if developer want to set only Size then he/she does not requireed to pass TimeStamp. Moreover, then what will be execute code for this procedure? Scenario: Update TimeStamp = getdate() field whose SizeID = 1 AND Size =Large Note: This field Parameters and condition parameters must be dynamic ...

Sql server stored procedure

Some one please explain me how does this query works SELECT 'WRITEBOARDCOMMENT' AS Type, wbc.CommentText AS Content, wb.WBId AS Id, null AS ToDoListName, null AS DueDate, u.FirstName + ' ' + u.LastName AS ActivityBy, wbc.[Date] as Date, u.FirstName + ' ' + u.LastName as PartyName, comp.CompanyId AS CompanyId, comp.CompanyName AS Company...

Insertion stored procedure values in java not get stored in sql table.

Joined: Jun 07, 2010 Posts: 1 [Post New]posted Today 12:53:46 AM Quote Edit Help me, AM using struts2 frame work, am sending the values to db method and i have written code, my try block executed no err msg in console, I get the msg Stored procedure exe succ, but my values are not get insert into table. In sql stored procedure 4 input ...

In C# Using System.Data.OracleClient.OracleParameter String Containing Single Quotes is Cut

I can't understand why but there seems to be a different behavior with my varchar parameters according to whether I'm in a transaction or not. I want to pass an OracleParameter, type VARCHAR, containing a single quote, such as: Dante's Lament When I'm NOT in a transaction, the string is passed correctly. When I'm in a transaction, ev...

mysql 5.1: how can i use benchmark() command to test a call to a stored procedure ?

Hello I'm trying to benchmark a stored procedure. select benchmark(100000000,(select 1)); this benchmark works but the following benchmark doesn't: do benchmark(1000,(call test_login_user('a'))); it produces the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your...

Executing a stored procedure over ODBC

I have an application that allows the user to enter an SQL string with a placeholder for certain values my application produces. The application will the replace the placeholders with values and execute the SQL string through various database backends. For the ODBC backend, I call SQLExecDirect() on the SQL strin which works nicely on r...

Quickest way to identify most used Stored Procedure variation in SQL Server 2005

Hey, all. I am trying to figure out if there's a way to identify a "version" of a SP that gets called the most. I have an SP that gets called with a bunch of different parameters. I know that the SP is causing some issues and trying to pin point the problem. Besides capturing calls to the SP and manually sifting through the results, is ...

SQL Error 1630: Function SUBSTRING does not exist.. huh??

Right. So I've created a stored procedure in a MySQL DB which happens to use SUBSTRING. Running the procedure via a query gives me: SQL Error 1630: Function mydatabase.SUBSTRING does not exist Beg your pardon? ...

Stored Procedure return updated record count ?

Hello fellows, I am very new to stored procedures. I am trying to make a stored procedure which would perform an update accepting 2 input parameters and return the number of records updated (in my case always 1). I have managed till this so far but I don't understand how to return the variable after opening the cursor. Is the cursor nec...

Insert or Update without a loop?

I have table with two columns: ItemMaster (Item INT, Quantity INT) If an item is already there, then I should update the Quantity. Otherwise, I have to insert a Record in this table. Is this possible without Loop? I'm using SQL Server 2005. ...

T-SQL: How to return 0 rows in from stored procedure, and how to use XACT_ABORT and TRY/CATCH

I'm writing a stored procedure and I want to return 0 records when something fails. I can't seem to figure out how to just return 0 rows? I've used SELECT NULL but this returns 1 row with a NULL in row 1 col 1. I have also tried not specifying any SELECT statements in my error code path but when testing the value of @@ROWCOUNT after the ...

MySQL Stored Procedures

Hi, I have a few questions regarding stored procedures. After hours of googling, I really can't find any really simple query examples. CREATE PROCEDURE test1(IN uid INT) BEGIN SELECT USER_NAME FROM USERS WHERE USER_ID = uid; END // this query takes 'uid' as input and selects the username for that uid, Can someone please explain h...

capture output parameter from stored proc in LINQ To SQL ExecuteQuery

Is it possible to get an output parameter back from the LINQ To SQL DataContext when you execute a stored procedure? IEnumerable<Address> result = ExecuteQuery<Address>(((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount); where address, pageIndex and pageSize are input parameters, and ...

Performance of a SETOF returning function which returns results of another SETOF function?

I have written a function that takes two arguments and returns a SETOF result. CREATE FUNCTION foo(parentId bigint, childId bigint) RETURNS SETOF bar AS ... I would like to write two "wrappers" for this function that make it simpler to call: CREATE FUNCTION foo_parent(parentId bigint) RETURNS SETOF bar AS ... BEGIN RETURN ...

How reliable are mySQL stored procedures?

This not a go/no go question. I've used all kinds of 4GLs (PL/SQL, T-SQL, PGSQL etc.) for database back-end coding and find very useful to establish data layer "closest" to database engine. Now MySQL stored procedures got my attention and very interested in coding in this brand new 4GL platform. I would like to start using it in one o...

MySQL, stored-function, using STRING variable as QUERY

I was wondering if it is possible in a mysql stored-function or stored-procedure compose a mysql query as a string variable you can execute later? I have a stored-function get_district_part ((district_id INT,county_id INT,city_id INT,zip_id INT)) RETURNS INT that references the table: CREATE TABLE IF NOT EXISTS `modx`.covereage_distric...

Accept Multiple Data Types in Stored Procedure

I'll avoid any explanations and just get to the question. Is there a way to accept multiple data types for one parameter of a MySQL stored procedure? I want to be able to accept both int and varchar-typed data for an IN parameter of the procedure. Is this possible? I know this is not good practice (I'd never choose to do it myself) but...

How can you tell if a stored procedure does not return results when using vb.net and linqtosql?

I have a linqtosql dbml where I dropped a stored procedure into the designer interface. Stored procedure name: GetUser(@userid int) Select * from users_tbl where userid=@userid Now in the code I want to do something like this: Dim db as new UserDataContext Dim myuser as users_tbl = db.GetUser(1) How can I tell if GetUser returned...