stored-procedures

How can i learn Stored Procedure and Trigger?

Hello I want to learn quickly writing Stored Procedure and Trigger? Can you suggest any link, eBook, etc? ...

Custom properties in entity framework

Hi all, I'm wondering if this is feasible and how. I've an entity that is a simple 1 to 1 mapping against a database. I'd like to add a custom property to this entity that is the result of a stored procedure call. This SP returns a collection of the same entity (hierarchical data). Any ideas? Thanks in advance, Fabian ...

MySql syntax question

Why does this work perfectly: SET @columnQuery = 'INSERT INTO ColumnNames SELECT ORDINAL_POSITION, COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=''residents'' ORDER BY ORDINAL_POSITION;'; PREPARE STMT FROM @columnQuery; EXECUTE STMT; but this does not: DECLARE TableName VARCHAR(50); SET @TableName = 'residents'; SET ...

Nested procedures with transactions

Hi! I have the following problem. I have to stored procedures (debug messages double-indended): CREATE PROC innerProc AS BEGIN SELECT 'innerProc 1',@@TRANCOUNT BEGIN TRAN SELECT 'innerProc 2',@@TRANCOUNT ROLLBACK SELECT 'innerProc 3',@@TRANCOUNT END GO ----------------------------------------- CREATE...

MySql syntax question regarding CONCAT and strings

Environment is MySql 5.1.5. This is a snippet from a larger stored procedure. Assume the variables are properly declared and set before this code is reached. When I run the following loop, something seems to be failing in the CONCAT. @columnName and @xmlQuery are both VARCHARs. When I "SELECT @xmlQuery" at the end of the procedure, ...

Calling Oracle package procedures which return ref cursors in straight PL/SQL

I've got an Oracle 10g database which is accessed from an ASP.NET application. Although I've used SQL Server heavily in many different aspects and Oracle for querying and reporting, this is my first time using Oracle as the OLTP database for an application. The database-level procedures in the packages are typically of the form: -- TY...

PHP MySQLi Stored Procedures return call

I cannot figure a way to retrieve the return variable in PHP. It works just perfect in MySQL, but this from PHP doesn't for some reason. '; if ($this->multi_query("CALL testOut(@outvar); SELECT @outvar;")) { do { if ($result = $this->store_result()) { ...

postgresql union

I have this data in postgresql id | rate | hrv | activity | orientation | timestamp | user_id | status ----------+-----------+-----+----------+-------------+------------------------+---------+-------------- 66764728 | 72 | 1 | 0 | 90 | 2010-06-10 18:54:54+00 | 397 | t 66764729 | ...

Which DAL libraries support stored procedure execution and results materialisation

I'm used to EF because it usually works just fine as long as you get to know it better, so you know how to optimize your queries. But. What would you choose when you know you'll be working with large quantities of data? I know I wouldn't want to use EF in the first place and cripple my application. I would write highly optimised stored ...

Using ROWTYPE input parameters in TADOStoredProc

We have a stored procedure which I'd like to call from my client: PROCEDURE p_my_proc(p_id IN my_table.id%TYPE, p_record IN my_table%ROWTYPE); In the client I want to call this procedure using TADOStoredProc, but haven't found a way to assign the record to a parameter. I have a TDataSet with the record in i...

Problem to debug stored procedure

There is a SP which gets over 96 items as parameters. I want to debug this sp, how can I do it? It says: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0." System.Data.Common.DbException {System.Data.SqlClient.SqlException} Please tell me a way t...

How to retrieve data from a SP in EF4

I'm using EF4 to call a stored procedure which returns the results of a SELECT statement at the end of the SP. However, some of the column names in the returned results contain underscore chars, or spaces, which prevents me from mapping them to a complex type. How can I retrieve the results of the SP from EF? ...

how do I capture and return a scalar result from a my-sql prepared statement?

So I wrote this nice little stored procedure that accepts 3 parameters, assembles a query string, executes a query, and retrieves a single row of data from the specified table. It then converts the single row into well-formed XML. The call to EXECUTE query2; is where the XML is returned. When running it in the Toad MySQL Editor, obvio...

ms sql call stored procedure with date time parameter

how do one call a stored procedure that has date input . spName getDate() does not work. the question is about calling within ms sql managment studio. ...

SQL Server stored procedure and Linq-to-SQL

I'm working on a project right now where we have to implement all communications to the database via stored procedures. We are using Linq-to-SQL, so all the database entities are in place. My question is how you could write a stored procedure that returns an entity, if this is possible. Example, we validate a user when he's logging in w...

Preventing SQL Injection attacks: the differences between mySql and SQL Server 2008

Is there any reason to migrate from MySql to SQL server 2008 if one's main concern is the blocking of SQL injection attacks? Does Linq2Sql or EF provide additional protection? ...

Catch the error from Stored Procedure in C#

I have a stored procedure that is called to validate a user during login. If success it returns the user entity, and that works good! My question is if it doesn't work, I'll raise an error in the SP, How do I catch this error and use it in the best way? Right now I'm getting nullrefference, this is the code: Store procedure: ALTER PRO...

Is it possible to update a variable field in a stored procedure?

I have a simple query that updates a variable column. This query is later string.Formatted and passed to SqlCommand (c#) (TableId is a column in SomeTable): "UPDATE SomeTable set {0}=@Value where TableId=@TableId" I need to convert this query to a stored procedure. Is it possible at all? ...

Searching in SQL Management Studio 2005

Is there a way to search for text within stored procedures? For example I want to find out if a particular table is being referenced by any stored procedure. ...

Run Stored Procedure in SQL Developer?

Hi, I am trying to run a stored procedure that has multiple in and out paramaters. The procedure can only be viewed in my Connections panel by navigating Other Users | | Packages | | If I right click , the menu items are "Order Members By..." and "Create Unit Test" (greyed out). The ability to "Run" the procedure does not seem possi...