stored-procedures

SQL Server: what can a stored procedure do that a user defined function cannot?

Can you tell me what is the need for a stored procedure when there is UDF? ...

Is there a Query-Mapper like iBATIS.NET but with dirty tracking, lazy-loading and cascading updates?

The problem: A DBA-controlled database with a Stored Procedure-only mandate. An expectation that the domain be defined in POCO's. So I decided that I need an ORM designed for stored procedures and/or legacy databases. Ideally the ORM should allow me to declaratively (or fluently) map domain objects to CRUD stored procedures. Some f...

Problem retrieving time from date field in oracle stored procedure

I'm having problems retrieving the time from a date field in a SP. If I run the query: select TO_CHAR(HOR_HST_ATN,'YYYY/MM/DD HH24:MI:SS') AS HOR_HST_ATN FROM AAM0_DT_RSTCN ; It returns the date and time ok. But in the SP, the code: SELECT FEC_DSD_ATN, FEC_HST_ATN, NOM_SEM_DSD_ATN, NOM_SEM_HST_ATN, TO_DATE(HOR_DSD_ATN ,'DD/MM...

stored procedure column value determined by value of another column

I have a table of data something like this. date, amount, price 2009-10-12, 20, 15.43 2009-10-13, -10, 6.98 I need to write a stored procedure that will return these column as well as create a new column that indicates whether the amount was positive or negative. So the end result of the procedure would look something like this. dat...

LINQ to SQL stored procedures with multiple results in Visual Studio 2008

I'm using visual studio 2008 and I've created a stored procedure that selects back two different result sets. I drag the stored proc on to a linq to sql dbml datacontext class, causing visual studio to create the following code in the cs file: [Function(Name="dbo.List_MultiSelect")] public ISingleResult<DataAccessLayer.DataEntities.Lis...

Using Stored Procedure for reading records from Database in NHibernate?

Hi, Is reading the database records from the Database using Stored Procedures in NHibernate a good approach?If yes then why or if No, then also why? The application has only the feature of reading values from the database using NHibernate in the data access layer no updates and no inserts just only retrieval. ...

Replacing a specific substring with another substring in a string in SQL stored procedure

Hello all, I've the following requirement. I'm passing 'ABC DEF' as a command parameter @MyString to a stored procedure. In the stored procedure level i need to replace the substring DEF with XYZ to get the string 'ABC XYZ'. How can i do that? Thank you. NLV ...

Getting limited amount of records from hierarchical data

Let's say I have 3 tables (significant columns only) Category (catId key, parentCatId) Category_Hierarchy (catId key, parentTrail, catLevel) Product (prodId key, catId, createdOn) There's a reason for having a separate Category_Hierarchy table, because I'm using triggers on Category table that populate it, because MySql triggers work...

get column data in stored procedure when schema is reversed

In a SQL 2008 DB there are 2 tables of data that I need to use to build a result in a stored procedure. Table 1 looks something like this. date name hour amount price ------------------------------------------ 2009-10-12 tom 12 20 15.43 2009-10-13 fred 16 -10 6.98 Table 2 looks like this. Note ...

How to do regular database cleanup in a JPA application?

Short background: I have a software that regularly downloads files. The Statistics of these downloads are stored in the database as a DownloadResult entity, which is in turn associated to the entity representing the download job. Now I want to make sure, that only a fixed number of the n latest downloads are preserved in the database. A...

Executing a VB.NET DLL From SQL Server

Objective: we want to write a DLL using VB.NET 2008 using .Net 3.5 that performs an HTTP request we then want to call the DLL From SQL Server 2005 (in a trigger) and pass the DLL some variables and get a return value. Where im at in the process. I have created the DLL 'HTTPDLL.dll' Source: Imports System Imports System.Configurati...

Stored procedure output parameters in SQL Server Profiler

I've got a stored procedure with an int output parameter. If I run SQL Server Profiler, execute the stored procedure via some .Net code, and capture the RPC:Completed event, the TextData looks like this: declare @p1 int set @p1=13 exec spStoredProcedure @OutParam=@p1 output select @p1 Why does it look like it's getting the value of th...

SPROC hangs in SQL Server 2005

I get a problem with SQL Server 2005, where a stored procedure seems to randomly hang/lock, and never return any result. What the stored procedure does is to call a function, which in turn makes a union of two different functions – returning the same type of data, but from different criteria. Nothing advanced. I don’t think it’s the fu...

MYSQL nested loop stored procedure issue

I am trying to do a simple stored procedure in mysql which has a nested loop. The idea is to check to see if the table has any values and if not then insert them. below is the code of the stored proc. I have tested all parts of the code and if i comment out the nested loop it will loop through all the values for the _my_curs_ fine. But w...

SPROC T-SQL Syntax to return results if rows exist on multiple days

what I need to test for on my table is if there are rows for a given user id and order id on two separate days (DATETIME field for a timestamp). I'm pretty sure I'd need a having clause and that's why I'm here...that frightens me terribly. ...

Securing a database from Java clients

The concept I have uses a central MySql database which has many Java clients running and using this database (connecting directly). The clients would be publically available, so security becomes an issue. As Java can be decompiled, I cannot put the security part of this system into the client application. I'll need to have an initial us...

Using Decimal parameters in Firebird stored procedures

I have a Firebird stored procedure that accepts Decimal(9,6) values for Latitude and Longitude parameters. It's used to create a contact profile for a user. When I try to create and use these decimal parameters, I get a conversion error: Value was too large or too small for an Int32. Here's how I create the ADO.Net connection: Fb...

Cannot set return value in variable in SQL Proc

Hi, I created a simple stored proc which returns a string create proc im.mmtest as select 'testvalue' go then I call the SP and set its return value to a variable. This block is executed by selecting all three lines and pressing F5. I do see the value when the exec statement is executed, but I do not get the string value back in sel...

mysql @@identity vs sql-server last_insert_id()

Am I correct in understanding that mysql's LAST_INSERT_ID() function doesn't reset between insert attempts (as @@identity does in sql-server)... that if the immediately preceding insert operation fails, LAST_INSERT_ID() will return the pk of whatever the pk of that connection's last insert to any table with an auto-incrementing primary k...

Preferred way of retrieving row with multiple relating rows

I'm currently hand-writing a DAL in C# with SqlDataReader and stored procedures. Performance is important, but it still should be maintainable... Let's say there's a table recipes (recipeID, author, timeNeeded, yummyFactor, ...) and a table ingredients (recipeID, name, amount, yummyContributionFactor, ...) Now I'd like to query like...