I have pairs of tables in the format TABLE and TABLE_TWIN now TABLE is the main table with lots of data TABLE_TWIN is a table with the exact same fields with a little data (different data) Now I would like to copy all rows from TABLE_TWIN to TABLE using a stored procedure. I have many such tables and could like the stored procedure t...
I'm having a rather strange problem with MySQL. Trying to create a procedure to update some fields in the database (the code is below). The problem is with the line that is currently commented. It seems that if no SELECT statements get executed during the procedure MySQL query browser will return an error code of "-1, error executing SQ...
I have a stored proc that I use to create an xls file and then write data from queries to it. I can't take credit for this, here's the source http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/ . I can write varchar, txt, etc without issue. I can't seem to get any datetime fields to write and I have guessed at al...
Hi, I intend to execute a stored procedure using enityspaces and wrote the following method in Custom file. public BizUnitCollection loadby_bizunitname(string description) { BizUnitCollection rtn_Ary = new BizUnitCollection(); esParameters parameters = new esParameters(); parameters.Add(new esParameter("name", description))...
I have a couple of stored procedures on SQL Server 2005 that I've noticed will suddenly take a significantly long time to complete when invoked from my ASP.NET MVC app running in an IIS6 web farm of four servers. Normal, expected completion time is less than a second; unexpected anomalous completion time is 25-45 seconds. The problem doe...
I have a requirement to update a column with multiple values. The query looks like below. Update table1 set column1 = ( select value from table2 where table1.column0 = table2.coulmn ) Is there any generalised stored procedure for a requirement like the above? ...
I'm using If Condition Srore procedure... But .. I need if stored procedure with where in if else condition and concatenate with to and condition in Query.. Examble create procedure [dbo].[Sp_Name] @code int, @dCode int As select <.........> from <tablename> Where empcode=@code if (@dCode != 0) Begin And dptcode=@ dCod...
UPDATE: I've answered my question below, take a peek and let me know if you have a better way of doing this executive summary: when calling my sqlserver function from SQL Management Studio I get a list with results {1, 2, 3}. when calling the method from a code using NHibernate I get this list {1, 1, 1}. ('1' is an entire result row, ...
Hi, I have a SP using FTS (Full Text Search). I want searches across multiple fields, 'intelligently' ranking results based on the weights I assign. Consider a search on a view fetching data from tables: Book, Author and Genre. Now, I want the searcher to be able to do: "Ludlum Fiction", "Robert Ludlum Bourne", "Bourne Ludlum", etc....
How can i exit in the middle of a stored procedure? i have a stored procedure where i want to bail out early (while trying to debug it). i've tried calling RETURN and RAISERROR, and the sp keeps on running: CREATE PROCEDURE dbo.Archive_Session @SessionGUID uniqueidentifier AS print 'before raiserror' raiserror('this is a raised error'...
I'm new here, and relatively new to stored procedures, so please bear with me! I've checked related questions on here and can't find anything that works in this instance. I am trying to build a stored procedure (MS SQL Server 2005) that takes a number of passed in values and in effect dynamically builds up the SQL as you would with inli...
Can you not do the following w/ MySQL stored procedures?/ DROP PROCEDURE IF EXISTS `test`; DELIMITER // CREATE PROCEDURE TEST (team varchar(30)) BEGIN SELECT * FROM TEAMS WHERE TEAM_ID = @team; END // Where @team (or team) is the variable passed to the stored procedure? ...
Hi guys, In coldfusion, how can I send an anonymous block to oracle and get some response from oracle? I tried cfquery, but it doesn't work. Great thanks. @Antony, I know i can write anonymous block in cfquery. Such as: <cfquery name="queryName" datasource="oracle11ghr" result="queryName_meta"> BEGIN INSERT INTO npr_t_reservation...
I am very new to SQL Server Stored Procedures, This is My SP : CREATE PROCEDURE [dbo].[spGetBonusRunDetails] ( @StartDate as DATETIME, @EndDate as DATETIME, @PageNumber int, @PageSize int, @Status int ) AS ;WITH DataCTE AS ( SELECT ROW_NUMBER() OVER(Order by Id) as RowNumber ,[StartDateTime] ,[EndDate] ...
Hi I'm building a stored procedure which is rather stretching my experience. With the help of people who responded to this thread http://stackoverflow.com/questions/1863527/nested-if-statements-in-mssql-stored-procedure-select-statement I think I'm most of the way there :) In short, the SP takes a series of paramterised inputs to dynam...
I'm passing a comma delimited list of ids to a stored procedure as a varchar(MAX). The problem is varchar caps at 8000 characters and the list could potentially be larger. Is there a sql datatype where size doesn't matter? No pun intended. ...
Is there any way that I can make sure that a stored procedure completely finishes before another instance of it is started? I have to do 3 things in the procedure and if two instances are running at the same time it will mess up a boundary case. Example: Count rows, if < X insert a row, return calculated Y if multiple instances of ...
I have Visual Studio .Net project which uses ADO connection to call stored procedure of SQL Server database. It is possible to set a breakpoing at stored procedure and debug it. I have local SQL Server and SQL Enterprise Edition installed. ...
Hey guys, We are having some major performance issues with SELECT queries out one of our databases. Please see the simple procedure and associated code below. In the code the ExecuteReader() method is executing in around 10 seconds on a query returning 30K records. Iterating the Reader takes 2 minutes (even when I am not pumping the ...
I have two tables, a patient table an and appointment table. I'm attempting to retrieve the information from both tables depending on what doctor is logged in at the time. My stored procedure is as follows: ALTER PROCEDURE dbo.sprocGetAllAppointmentsForUser @UserID varchar(50) AS SELECT Appts.appt_id, Appts.patient_id, Appts.dr_id, A...