2
голосов
1ответов
438 просмотров

Using Same Stored Procedure for Both Insert and Update in Entity Framework

I have a stored procedure that does both the insert and the update in one fell swoop (if the id == 0 then it's an insert, otherwise, update). I'd love to use this for both the insert and the update methods in Entity Framework, but this isn't looking feasible. Am I correct that I'll have to split ...

1
голосов
3ответов
1278 просмотров

Why does stored procedure invalidate SQL Cache Dependency?

After many hours, I finally realize that I am working correctly with the Cache object in my ASP.NET application but my stored procedures stops it from working correctly. This stored procedure works correctly: CREATE PROCEDURE [dbo].[ListLanguages] @Page INT = 1, @ItemsPerPage INT = 10, @OrderBy...

3
голосов
1ответов
743 просмотров

Calling a store procedure with nHibernate

How do you call a stored procedure with nHibernate? Specifically there are two cases where I am using store procedures: to return a scalar value and to return a set of results mapped to entities.

5
голосов
5ответов
8397 просмотров

C#/SQL Get all Stored Procedures and their Code

How can I obtain all stored procedures and their code from SQL Server? I need to show data that is similar to what SQL Server Management Studio shows, incl. allowing to browse through SP's with code, tables and indexes. Any hints on how to get this information out of SQL Server? P.S., I'm usin...

3
голосов
2ответов
5663 просмотров

Throwing specific error messages in PLSQL Oracle...catching in hibernate?

Is it possible to throw a specific error message in a PL/SQL oracle stored procedure and catch it in Hibernate when it gets invoked?

1
голосов
3ответов
2250 просмотров

SQL Server: Concatenating WHERE Clauses. Seeking Appropriate Pattern

I want to take a poorly designed SQL statement that's embedded in C# code and rewrite it as a stored procedure (presumably), and am looking for an appropriate means to address the following pattern: sql = "SELECT <whatever> FROM <table> WHERE 1=1"; if ( someCodition.HasValue ) { ...

1
голосов
1ответов
579 просмотров

Stored Procedure Usage in Adobe LiveCycle ES

We have a number of stored procedures in production that we would like to use with LC. I reviewed the JDBC types and I don't see any way to get LC to accept the results of a call to a stored procedure, the stored procedure service only has an output for the number of rows affected. In fact the ...

0
голосов
2ответов
420 просмотров

SQL select groups of distinct items in prepared statement?

I have a batch job that I run on a table which I'm sure I could write as a prepared statement. Currently it's all in Java and no doubt less efficient than it could be. For a table like so: CREATE TABLE thing ( `tag` varchar, `document` varchar, `weight` float, ) I want to create a new ta...

1
голосов
4ответов
1815 просмотров

mssql_bind empty string converting to NULL

I am currently using SQL Server 2000 Stored Procedures with PHP. Following the PHP doc, I use mssql_bind to assign value of parameters and then execute the Stored Procedure. The problem is that I got this bug which prevents me to bind empty strings to parameters (they are converted to NULL when ...

2
голосов
1ответов
414 просмотров

MySQL call a stored procedure every x minutes

Is there a way in MySQL to call a stored procedure from within SQL every x minutes? I want to use this in a session-environment, where the database keeps track of all the sessions and automatically deletes sessions older than x minutes.

0
голосов
1ответов
141 просмотров

Limit data manipulation from user only through application layer for multi-database solution

I'm starting the development of a new software that must be compliant to SQL Server (2005 and above), Oracle (10r2 and above) and Sybase (12.5 and above). I'm planning on implementing the database abstraction using stored procedures, so I can use more performatic code implementing database specif...

3
голосов
3ответов
8677 просмотров

Issue calling a MySQL stored procedure (with params) via a linked server (SQL Server 2005) using OPENQUERY syntax

I'm having issues when trying to call a MySQL (5.0.77) stored procedure with parameters, via a linked server (SQL Server 2005) using the OPENQUERY syntax. The MySQL stored procedure returns a result set, and when I use the 'EXEC ... AT ...' syntax the call works fine, e.g... EXEC('CALL my_store...

0
голосов
2ответов
116 просмотров

Is nHibernate intended to replace database objects?

I've not yet found a clear answer to this and to clarify: With nHibernate and SQL server are you expected to disregard or migrate your business logic stored in your stored procedures, views and triggers into HQL or application code?

0
голосов
1ответов
1903 просмотров

Stored procedure search using entity framework

I want to implement a search logic that I have seen and use. It works like this: There is stored procedure that is loaded in entity framework and it is used as a method in C#, it takes parameters in order to perform search. That method is returning list of views (DB View). The views in list ha...

0
голосов
1ответов
1441 просмотров

Testing Stored Procedures with MySQL

What is the best way to test MySQL stored procedures? How do you test stored procedures with output parameters in the MySql GUI?

0
голосов
5ответов
698 просмотров

Return DataTable based on boolean param

I have a table that has a field, "IsActive," that indicates whether a record has been "deleted" or not. Currently, I retrieve this info like so: public DataTable GetContractors(bool IsActive) { SqlParameter paramIsActive = new SqlParameter("@IsActive", SqlDbType.Bit); pa...

2
голосов
2ответов
1346 просмотров

Can I update the result (sys_refcursor) of an Oracle stored procedure (in Java)?

Given the following function: create or replace FUNCTION "GETADDRESSES" RETURN sys_refcursor IS address_cursor sys_refcursor; BEGIN OPEN address_cursor FOR SELECT * FROM Address; RETURN address_cursor; END; I would like to be able to make changes to this result set in ...

0
голосов
4ответов
2526 просмотров

Working with a QueryString and placing value in Stored Procedure?

I am working with ASP.NET and SQL Server 2005. I know how to create a stored proceudre but i do not know what to place in a stored procedure when i need to make use of a QueryString. My SQL Statement in CODE = "SELECT * FROM TableName WHERE ID = '" + Request.QueryString("ID") + "'" Now what...

0
голосов
2ответов
3702 просмотров

MySQL Syntax Error

When executing: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 1; END; DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; SELECT 1; END; -- delete all users in the main profile table that are in the MaineU18 by em...

18
голосов
8ответов
54867 просмотров

SQL : in clause in stored procedure:how to pass values

I want to write a SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter. How to do this ? I can pass the user ids as a string separated by comma. So that I can use the select ...

5
голосов
2ответов
9938 просмотров

Entity Framework Error: "The container 'XXXX' specified for the FunctionImport could not be found in the current workspace."

I am executing this EntityConnection entityConnection = (EntityConnection)context.Connection; EntityCommand command = entityConnection.CreateCommand(); command.CommandText = "Genesis.AL_Insert"; command.CommandType = CommandType.StoredProcedure; //Par...

2
голосов
3ответов
1401 просмотров

MySQL Prepared Statements vs Stored Procedures Performance

I have an old MySQL 4.1 database with a table that has a few millions rows and an old Java application that connects to this database and returns several thousand rows from this this table on a frequent basis via a simple SQL query (i.e. SELECT * FROM people WHERE first_name = 'Bob'. I think the ...

0
голосов
2ответов
550 просмотров

SQL Server StoredProc vs UDF inline table

I have written a complex query that will return me a list of IDs. Now I want to re-use this query so that I join the results with another query. For that I plan to put this into a Stored Proc or an UDF and then use it to insert into a temp table. Something like below 1) Put the query in a Store...

2
голосов
1ответов
250 просмотров

Qualifications for answering: Are the days of stored procedures numbered

Looking at all the answers to the question: "Are the days of stored procedures numbered". It appears as if some people are jumping in with strong opions based on what I perceive to be a narrow range of software development experiences. I would like to re-submit the question, but first I need he...

1
голосов
2ответов
108 просмотров

Stored Procedure Error

I'm getting the following error when I run this code: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'. Any ideas on how to fix this? Any help would be appreciated. Declare @Month int Declare @Year int Declare @newYear int Declare @EndYearMonth int Declare @PreviousMonthInt int ...

4
голосов
4ответов
2349 просмотров

T/F: Using IF statements in a procedure produces multiple plans

In responses to this question, KM said if you are on or above SQL Server 2005, you can use IFs to have multiple queries in the same procedure and each will have an query plan saved for it (equiv to a procedure for each on older versions), see the article in my answer or this link to proper se...

2
голосов
6ответов
462 просмотров

Deleting from 2 tables at the same time?

I'm using asp.net and sql server. i have 2 tables: Categories and Products. in the products table i have categoryId as FK. what i want to do is: when i delete category from the category table, i want that all the products from that category will be deleted in the products table. how can this be d...

0
голосов
4ответов
572 просмотров

Swapping sql query for stored procedure and confused about return value

This is a bit of code in one of the pages I am reviewing and trying to update. What I am trying to do is swap the inline query for stored procedures. This is the bit of code: numOfDelRec = 1 Do While (numOfDelRec > 0) cm.CommandText = "DELETE Folder WHERE FolderID IN (SELECT ch.FolderI...

7
голосов
4ответов
210 просмотров

Where should database queries live?

Should queries live inside the classes that need the data? Should queries live in stored procedures in the database so that they are reusable? In the first situation, changing your queries won't affect other people (either other code or people generating reports, etc). In the second, the querie...

6
голосов
3ответов
4899 просмотров

Stored procedures in entity framework

I am trying to use a stored procedure in the entity framework that returns nothing. The stored procedure is purely for logging. I added a function (right click -> add -> function import) which works ONLY when the return value is set to one of the entities. When I change the return type t...