Normally the parameterized SQL works great for CRUD, but I have one instance where I want to use a stored procedure to do an insert. My HBM file has the following (along with a bunch of other properties, bags, etc) <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="MyProject.Entities" assembly="MyProject"> <class name="...
I have a search page, which has Date text field. The user may or may not populate this field. But the field is a parameter of a SQL stored proc that gets called to execute the search query. When I walk through the code (and the Date field is blank), I get an error that the value could not be converted to DateTime How can I convert a nu...
I have an application that needs to return search results from a SQL Server 2008 database. I would like to use a single stored procedure to return the results but I am finding that as I build the stored procedure it is full of many Else .. Else If statements with the query repeated over and over with slight variations depending on the us...
Has anyone tried to create stored procedures using the H2 database? ...
I want to get multiple resultsets from a storedProc using sql to linq. I was not able to generate it from designer so I wrote below code in designer.cs file. But whenever I add something to designer, it refreshes the designer with the markup in .dbml file and hence it removes the below code every time I add something. I have to copy it e...
We have a stored procedure where we want to dynamically let the user choose which column to sort the result by, and in which order (ascending/descending). Currently, we have the following ORDER BY clause, but I'm sure it can be optimized. How? ORDER BY CASE WHEN @OrderBy = 'salvnummer' AND @OrderByDirection = 'DESC' THEN salvnummer ...
Hi, I'd like to get your opinion on 2 ways of implementing the same stored procedure. Any advice would be greatly appreciated. Implementation 1 CREATE PROC GetFileSize(@Path varchar(500) = NULL, @FileID int = NULL) AS IF @Path IS NULL ' Find the file by @FileID and return its size ELSE ' Find the file b...
I have a table that is used to store an incrementing numeric ID (of type INT). It contains a single row. The ID is incremented using a query: UPDATE TOP(1) MyTable WITH(TABLOCKX) SET NextID = NextID + 1 I would like to move this into a stored procedure that returns the value that was in the NextID column before it was incremented, but...
I have a stored procedure that works in the mysql> terminal, but when I try to run it through my application it doesn't work. I don't get any error messages or anything. If I try to execute the sp through MySql Query Browser the response is simply : "Query canceled." This particular SP is just a simple update command, but I have other...
I have a stored proc that is called from my asp.net page. The field "RecordUpdated" returns TRUE or FALSE. I need that column to return YES (if true) or NO (if false) How would I do that? SET @SQL = 'SELECT RecID, Vendor_Number, Vendor_Name, Invoice_Number, Item_Number, RecordAddDate, RecordUpdated FROM Vendor_Invoice_Log' SET @SQL = @...
I have a Data intensive problem which requires a lot of massaging and data manipulation and I'm putting this out there to see if anyone has an idea as to how to approach it. In simplest form. I have a lot of tables which can be joined together to give me a price listing for dentists and how much each charges for a procedure. so we hav...
hi, i'm learning EF4 and would like someone to recommend the best practice on how to search for entities using complex criteria in a stored procedure and return entities and their navigation properties. So lets say i have a table for Customer and a table for Contact. A customer can have many contacts. The customer table contains the Cu...
Hi, I am trying to debug a long stored procedure in Visual Studio 2008. I have followed all the steps on this link: http://www.sqlteam.com/article/debugging-stored-procedures-in-visual-studio-2005 but I still get the same error... 'Canceled by user' and it never hits the breakpoint. Visual Studio 2008 and SQL Server 2005 are runnin...
Hi All, I have a stored procedure, I want to know the name of the tables and views use in that stored procedure, can any one suggest how can I do so. Thanks in advance. ...
I need help in resolving a complex SQL query. I am trying to build up the query one vlock at a time. One issue is: If a parameter for @PubNum is NULL, the query shows "..... where PubNum = '' which is an issue. What I need is if the parameter is NULL, then PubNum should not be in the where clause. A second issue is: If @StartDate IS ...
I want to write a generic logging snip-it into a collection of stored procedures. I'm writing this to have a quantitative measure of our front-user user experience as I know which SP's are used by the front-end software and how they are used. I'd like to use this to gather a base-line before we commence performance tunning and afterward ...
Can i use insert into tables in a procedure (on oracle) ? example: procedure my_procedure (aa1 number ,aa2 number ) is begin insert into lam_table values(aa1,aa2,null) ;(*ofcourse depending on the tables ) ... ... end ; ** note i tried it and it worked but there were a message in the bottom that said (successfully compiled n...
I use DB2 9.7 for Linux. The stored procedure is implemented in PL/SQL (Oracle's programming language), so, the record set is an output parameter (SYS_REFCURSOR). CREATE OR REPLACE PROCEDURE TEST_CURSOR ( CV_1 OUT SYS_REFCURSOR ) IS BEGIN OPEN CV_1 FOR SELECT 1 COLUMN FROM DUAL; END TEST_CURSOR; I don't know how to dec...
Can anyone point me to a list/description of standard @RETURN_VALUES in SQL Server 2005 (if such a thing exists)? I have searched multiple times and can't find any reference. Interestingly, I was debugging a stored procedure earlier today using Visual Studion 2008 and received this in the results--"@RETURN_VALUE = -6". Wondering what ...
Im trying to call a Oracle stored proc from a C# application using the following code Connection conn = new Connection(); Recordset rs = new Recordset(); conn.Open("Provider=MSDAORA;User Id=username;Password=password;Data Source=DB;", null, null, 0); ; rs.Open("sproc 'abc', 'xyz'", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockType...