.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Asad Ali
Post New Web Links

CURSOR and var

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server
 

hi all

i have a question about an sql statement and i have no solution for it ...

i have update sp for users table ,,, i want to store in "Transactions Table" which exactly the fields updated and what is the old value and new value ,, just the updated !!

 

look at this sp code :

 

CREATE PROCEDURE [dbo].[spUpdateUser]
	-- Add the parameters for the stored procedure here
	@user_id int, 
	@user_name nvarchar(50), 
	@user_real_name nvarchar(100), 
	@user_email nvarchar(50), 
	@user_password nvarchar(100)

   Insert statements for procedure here
  DECLARE Cnames_cursor CURSOR
			FOR SELECT column_name FROM information_schema.COLUMNS WHERE table_name LIKE 


View Complete Post


More Related Resource Links

Cursor Functions

  
A cursor allows looping through a record set and performing a certain operation on each record within the set. SQL Server supports three functions that can help you while working with cursors: @@FETCH_STATUS, @@CURSOR_ROWS and CURSOR_STATUS. Cursor functions are non-deterministic.

DECLARE CURSOR in Transact-SQL

  
Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Win32 Resources: Using C++ to Programmatically Retrieve a Global Cursor's Shape and ID

  

Getting global cursor information is useful when developing software that drives or gathers information about other UI-based applications, including information about a remote machine. This article describes a way to programmatically identify the current cursor's ID and bitmap at any point in time. The first technique described is based on polling for information and shows how to get the handle of the current global cursor. This handle will then allow you to get information about the cursor. You can also monitor WinEvents for changes to the global cursor.

Dmitri Klementiev

MSDN Magazine October 2001


looking for CURSOR replacement

  
I got below solution from christa with cursor...could some one help me out without cursor solution.. here we are devloping WHERE clause... CREATE TABLE X (UID INT IDENTITY(1,1), COL_N VARCHAR(50), COL_V VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES ('S_NAME', 'AYAZ', 'VARCHAR(50)') INSERT INTO X VALUES ('S_ROLL', '10', 'DECIMAL(15, 0)') INSERT INTO X VALUES ('S_MARKS', '99', 'INT') INSERT INTO X VALUES ('S_SCHOOL', 'ZINQ', 'VARCHAR(100)') --SELECT * FROM X /*CURSOR*/ declare @n varchar(50), @v varchar(50), @d varchar(50) declare @sql varchar(max) set @sql = ' where ' declare mycur cursor for select col_N, col_V, col_D from X order by uid open mycur fetch next from mycur into @n,@v,@d while @@fetch_status = 0 begin set @sql = @sql + @n + ' = ' if @d like '%char%' set @sql = @sql + '''' + @v + '''' else set @sql = @sql + @v set @sql = @sql + ' and ' fetch next from mycur into @n,@v,@d end close mycur deallocate mycur set @sql = LEFT(@sql, len(@sql) - 4) print @sql --WHERE S_NAME = 'AYAZ' AND S_ROLL = 10 AND S_MARKS = 99 AND S_SCHOOL = 'ZINQ'

is there alternate solution for CURSOR?

  
/* CREATE TABLE X (UID INT, COL_N VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES (3, 'S_NAME', 'VARCHAR(50)') INSERT INTO X VALUES (5, 'S_ROLL', 'DECIMAL(15, 0)') INSERT INTO X VALUES (8, 'S_MARKS', 'INT') INSERT INTO X VALUES (9, 'S_SCHOOL', 'VARCHAR(100)') SELECT * FROM X CREATE TABLE Y (UID INT IDENTITY(1,1), S_NAME VARCHAR(50), S_ROLL DECIMAL(15, 0), S_MARKS INT, S_SCHOOL VARCHAR(100)) INSERT INTO Y VALUES ('AYAZ', 10, 99, 'ZINQ') SELECT * FROM Y CREATE TABLE Z (UID INT, COL_N VARCHAR(50), COL_D VARCHAR(50), COL_V VARCHAR(50)) */ DECLARE @vSQL NVARCHAR(MAX) DECLARE @UID INT DECLARE @COL_N VARCHAR(50) DECLARE @COL_D VARCHAR(50) DECLARE cur CURSOR FOR SELECT UID, COL_N, COL_D FROM X ORDER BY UID OPEN cur FETCH cur INTO @UID, @COL_N, @COL_D WHILE(@@FETCH_STATUS=0) BEGIN SET @vSQL = 'INSERT INTO Z (UID, COL_N, COL_D, COL_V) SELECT ' + CAST(@UID AS VARCHAR) + ',' + '''' + @COL_N + '''' + ',' + '''' + @COL_D + '''' + ',' + @COL_N + ' FROM Y ' EXEC SP_EXECUTESQL @vSQL PRINT @vSQL FETCH cur INTO @UID, @COL_N, @COL_D END CLOSE cur DEALLOCATE cur SELECT * FROM Z

Hide HoverMenuExtender when cursor is in an associated TextBox

  
Hi.I use HoverMenuExtenders on TextBoxes to display some info in certain cases (typically; "you did something wrong here" - messages). Thing is, I want the HoverMenuExtender to disappear when the cursor is in its associated TextBox (and the mouse is outside its borders). Right now the HoverMenuExtender is still visible when the user have placed the cursor in a TextBox to write even though the mouse pointer is outside the TextBox.Is this possible? Thanks for any input! 

Could not complete cursor operation because the table schema changed

  
Microsoft SQL Server  2000 - 8.00.2039 Got this error: Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000 Is this a known issue?  I suspect the application logic may cause this error. Please advise. Thanks a lot!  

fetch data from cursor(what wrong?)

  
hi there i have a SP that send mailbut in the cursor does not appears no one record, i written PRINT'-------' , and it seems no recordwhere is wrong here?the query returns 200 records here, but not in cursoralter PROCEDURE [dbo].MAIL (@date date) AS BEGIN declare @MonthlyTimeInterval int declare @WorkingDaysBeforeOpen int declare @WorkingDaysBeforeClose int ----valorizzazione parametri MAIL--------- select @MonthlyTimeInterval=MonthlyTimeInterval, @WorkingDaysBeforeOpen=WorkingDaysBeforeOpen, @WorkingDaysBeforeClose=WorkingDaysBeforeClose FROM ParameterData --------market in apertura------------- DECLARE @getMktOpen CURSOR declare @PeriodEND date declare @PeriodStart date declare @Market Varchar(300) declare @Status bit declare @MarketID int DECLARE @MarketPeriodID INT SET @getMktOpen = CURSOR FOR SELECT MAX(Period.[Desc]) AS PeriodEND, MIN(Period.[Desc]) AS PeriodStart, Market.[Desc] AS Market, MarketPeriod.Status, Market.MarketID, MarketPeriod.MarketPeriodID FROM MarketPeriod INNER JOIN Period ON MarketPeriod.PeriodID = Period.PeriodID INNER JOIN Market ON MarketPeriod.MarketID = Market.MarketID GROUP BY Market.[Desc], MarketPeriod.Status, Market.MarketID, MarketPeriod.MarketPeriodID -- HAVING (MarketPeriod.Status = 0) -- AND (MIN(Period.[Desc]) = CONVERT(CHAR(11),@date,111) ) OPE

Changing Mouse Cursor into Crossed Circle in ListBox-ScrollBar/ScrollViewer(on Drag and Drop).

  
Hi All,   I have a ListBox with 10 items, the automatic Scrollbar/ScrollViewer is appearing. If i'm dragging an item from the listbox and moving the mouse cursor on top scrollbar/scrollviewer the cusor have to change into CROSSED CIRCLE(means its not a location to drop). Can any one help to do the same, or please let me know if more info needed.   Thanks, Karthikeyan Manickam.

Advanced T-SQL - want to get rid of a large Cursor

  
Hi all, I am importing & processing large amounts of data (100MB+ xml files), and the biggest bottleneck is a step involving one huge cursor.  In this step, I have say 300,000 rows, that I need to split space-delimited values into their own individual rows (10 Million rows total after the 300k are fn_split). Presently I am loading them all into a Cursor, fetch next into a group of local vars, then combine fn_split with insert.  It takes 10-15 minutes to do. Meanwhile, my server has 24 processors (cores + hyperthreading), and only ONE is in use due to this cursor.  Talk about a waste of resources! I have tried many alternatives - @id=1 WHILE @id<max BEGIN select @values=cols fn_split Insert @id=@id+1 END, and they all seem to take 2-3 times as long to execute as the Cursor. (the While loop also uses less processing %age than the cursor!) There has to be a way to optimize this thing... I would love to find a way to engage all 24 processors at 40-60% to crank through this thing, it'd be done in 2-3 minutes.  Anybody have ideas? Thanks- Jason

Oracle stored proc optionally returning a ref cursor.

  
Hi, I would like to use a technique for exception handling on our database stored procs whereby, each proc exposes a p_error_code output parameter of type int. 0 indicates no error, and other values indicate application specific exceptions. Now, where a stored proc will return a ref cursor if no exceeptions occur, we would like to check the p_error_code and if not 0, then use the ref cursor. If an exception does occur, then the stored proc may or may not have populated the ref cursor, but the dotnet code will almost definately not read the contents. Now, we have done this with the following code:            OracleCommand command = CommandFactory.CreateCommand(transaction, "RTS.p_get_audit_trail");             command.Parameters.Add(ParameterFactory.CreateParameter("p_doc_id", RTSId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_rev_id", RevisionId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_c_item_id", ContentItemId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_error_code", OracleType.Number, ParameterDirection.Output));          &nbs

Oracle ref cursor or temp table?

  
I have to return a large amount of data from a complicated query to an asp.net web page. It is currently doing it by running a procedure to fill an Oracle temp table and pulling all of the data from the temp table for display. I was wondering if using a ref cursor would be faster and more efficient. Does anyone know?

Cursor problem with Stored Procedure and PHP

  
I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(

Problem with cursor and Rebuild indexes

  
(SQL Server 2008 SP1) Hi I have a stored procedure that runs frequentely (uses a cursor), that SP never have errors on execution, but when runs at the same time with a rebuild index task (Database maintenance task on sundays), the stored procedure show the following error: "Could not complete cursor operation because the table schema changed after the cursor was declared" My question: Is there any way to avoid this problem running the two task at the same time? Is this a bug of the SQL Server 2008?JAC

Cursor Position not working on Form_Load

  
This is the only line of code in my Form_Load event and the cursor does not move to the submit button.I've tried to point it at other controls on the form and I get the same result...the cursor moves but to an area on the form (somewhat bottom left) where no control exits. System.Windows.Forms.   Cursor.Position = new Point(btnSubmit.Location.X, btnSubmit.Location.Y);Paul Reed

Showing thousands of rows in datagrid, need cursor to wait while loading rows

  
I´m creating DataGrid columns dynamicly in C# and loading data into a list at the same time. Cursor is waiting at the same time. Now, after columns are created and data is loaded into a list. I set the myDataGrid.Itemssouce = mylist; Cursor=Curosrs.Arrow; Andafter that, I set the Cursor to arrow again as you can see. But when the list contains thousands of rows it takes about 5-10 seconds for the datagrid to show the rows. Its ok but I need the cursor to be in waiting stage allso while data is not shown but the cursor is not waiting, its arrow. Any suggestions, please?

Move cursor position during drag drop operation

  

Hi,

I know how to change to a custom cusor or visual instead of a standard cursor during drag and drop operations.  I know how to get the current mouse position during drag and drop methods (often there is a GetPosition method, or alternatively I can use p/invoke or System.Windows.Forms.Cursor.MousePosition getter).

My question is, how can I (or is it not possible) to change the mouse position programmatically during the drag/drop operation, ideally just before the drop occurs so that items are dropped at the point of the programmatically set cursor position? 

I tried to to use the System.Windows.Forms.Position setter and SetCursorPos both in the source's PreviewQueryContinueDrag handler and the target's PreviewDrop handler but neither affected the position of where the items were dropped.  In the case of PreviewQueryContinueDrag, the mouse visually moved to the programmatic location after the items were dropped, even though the PreviewQueryContinueDrag handler was called before the item was dropped (and before PreviewDrop).

Any ideas?

Thanks,

Notre


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend