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


Post New Web Links

DECLARE CURSOR in Transact-SQL

Posted By: Amit Mehra     Posted Date: May 15, 2010    Points: 2   Category :Sql Server
 
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.


View Complete Post


More Related Resource Links

Transact-SQL Built-In Stored Procedures

  
To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Renaming an Object

Sending Email

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.

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


Must Declare a Scalar Variable @ActName

  
It is a stored procedure and I am trying to qualify O.FullName using a parameter but I am getting error message "Must Declare a Scalar Vaiable @ActName" Not sure why No idea what is wrong on this. The parameter is correct as @ActName SELECT A.AccountID, A.AccountGID, AT.Name AS Type, M.MarketSegmentID, L.LeadSourceGID, O.EmployeeID, O.FullName AS Owner, CASE WHEN Adr.City IS NULL OR Adr.City = ' ' THEN 'BLANK CITY' ELSE Adr.City END AS City, CASE WHEN Adr.Country IS NULL OR Adr.Country = ' ' THEN 'BLANK COUNTRY' ELSE Adr.Country END AS Country, CASE WHEN Adr.StateProvince IS NULL OR Adr.StateProvince = ' ' THEN 'BLANK STATE' ELSE Adr.StateProvince END AS State, CASE WHEN Adr.AddressLine1 IS NULL OR Adr.AddressLine1 = ' ' THEN 'BLANK ADDRESS' ELSE Adr.AddressLine1 END AS Address1, CASE WHEN Adr.Postal IS NULL OR Adr.Postal = ' ' THEN 'BLANK POSTAL' ELSE Adr.Postal END AS Postcode, CASE WHEN A.Name IS NULL OR A.Name = ' ' THEN 'BLANK ACCOUNT NAME' ELSE A.Name END AS ActName, CASE WHEN A.PhoneNumber IS NULL OR A.PhoneNumber = ' ' THEN 'BLANK PHONE #' ELSE A.PhoneNumber END AS Phone, CASE WHEN SIC.Name IS NULL OR SIC.Name = ' ' THEN 'BLANK SIC CODE' ELSE SIC.Name END AS SIC, CASE WHEN M.Name IS NULL OR M.Name = ' ' THEN 'BLANK MKT SEG' ELSE M.Name END AS MktSeg, CASE WHEN A

Transact-sql - tricky query to get a list of people from a table where i belong too.

  
I've no idea how to manage and optimize this query: I've four table. - User (userId, name) - Member (memberId, name, ownerId) The ownerId = user.userId - PresentPeople (activityId, presentId, ...) so presentId = memberId - Activity (activityId, ownerId, startDate...) and ownerId = user.userId So here for an user, I need members created by this user, and for this members I need the list of presentPeople for the activity that theyjoined. But I don't know how to get this list because they are in the list. Not sure if i'm clear there, Big help would be very helpfull :)) thanks

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! 

Must Declare Variable @tableName Error ?

  
create Procedure dummy ( @tablename nvarchar(20) ) AS Begin select ID,employeename from @tablename End I dont know why SqlServer is not able to search a table name so thought of getting some help from sql Gurus . Any help would highly be appreciated . Thanks .Rajkumar Yelugu

exec sql and transact

  
Hello guys. i've a problem, in a stored procedure i have a sql (SELECT * FROM TABLA -is only an example, actually, my sql statment is more complex) if i execute the sql statment the time for delay results is 6 seconds. but if i execute the stored procecure (EXEC spexample that has the same statmen) the results is in 2 minutes. is very slowly exec te store procedure, but the statmen is fast. excuse me for my english!!!

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!  

Using equal operator in transact-SQL for ntext datatype column

  
Hi, I've a problem with using equal operator in transact-SQL for ntext datatype column. (SQL Server 2000) I'm using the following SQL command text. use NorthwindSelect * from Categorieswhere Description ='Seaweed and fish' If I use 'like' operator intead of '=' then the qurey retuns correct value. Any idea about this? Any help is appreciated. Regards, Julia

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

How to declare a global connection object?

  
Following the post on declaring a global variable 'theDate' with a defined date format to be used across the web pages, I now encounter another tricky question: Is there a way to declare a global connection object so that I can avoid repeating the same statements (SqlConnection myconn = new SqlConnection(Class1.conn); myconn.Open();) in all the coding page that require a database connection? Thanks.

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
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