SQL SERVER – Simple Example of Cursor

UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2

This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE
@getAccountID CURSOR
SET
@getAccountID = CURSOR FOR
SELECT
Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
@AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE
@getAccountID
DEALLOCATE @getAccountID

Reference: Pinal Dave (http://www.SQLAuthority.com), BOL

197 thoughts on “SQL SERVER – Simple Example of Cursor

  1. Thanks for the simple demonstration! I was shown how to use cursors in a class years ago but never found an effective use for them as a VB.NET programmer (when you can make your own recordsets and twist them as you please). Now that I’m in an environment where I can’t use the nifty .NET data objects, a cursor was the only thing I could use to accomplish what I needed.

    There are a lot of other demos on the web that are a lot more difficult, but this really explains just the guts of how cursors work.

    Like

  2. Hi Pinal,
    1. How can we manage SQL Server in Clustered Environment?
    2. How can i stop and restart SQL Server services and instances in clustered environment?
    3. How can i stop and restart SQL server instances/services from windows ?

    If you have any documentation of managing SQL Server in clustered environment, please let me know.

    Thanks
    Abi

    Like

  3. hi,
    pinal

    For database and query optimization,
    what can i check that database is ok and query is ok.
    what kind of necessory command is required to find out these things.
    Please help me out

    Thanks & regards
    Dhirendra kumar
    mumbai

    Like

  4. Nice work. I keep finding this cursor as a good example for referencing.

    The Snow flake affect on you web page is interesting, but a little distracting.

    Like

  5. This is most excellent. I’ve been looking for just the bare bones of a cursor. This is a huge help to me. Did I mention it was huge?? :-)

    Thanks,
    Hosmerica

    Like

  6. Hi
    This is most excellent. I’ve been looking for just the bare bones of a cursor. This is a huge help to me. Did I mention it was huge??

    Regards
    Sunil

    Like

  7. Pingback: SQL SERVER - Simple Example of Cursor - Sample Cursor Part 2 Journey to SQL Authority with Pinal Dave

  8. Hi, That was really a good example for cursor. But as I am new to SQL server. So can you tell me what changes should be required if we need to get multiple columns.

    Like

  9. Hi Pinal,

    I have a function like below:

    ALTER function dbo.GetPhoneNumber (
    @CustId char(100) )
    returns nvarchar(4000)
    as
    begin
    declare @PhoneNum nvarchar(4000)
    declare hC cursor for select rtrim(tellocn), telno from dbo.custtel where custid = @custid and datediscon is null
    declare @telno nvarchar(4000)
    declare @tellocn nvarchar(4000)
    set @PhoneNum = ”
    open hC
    while ( @@fetch_status = 0)
    begin
    fetch next from hC into @tellocn, @telno
    select @PhoneNum = @PhoneNum + (@telno + ‘ ‘)
    end
    close hC
    deallocate hC
    return @PhoneNum
    end

    —————————————————-
    When I execute the function it returns like this:

    420667 08123833136 08123833136

    Why the last number is repeated twice?

    Many thanks

    Like

    • …………
      open hC
      fetch next from hC into @tellocn, @telno
      while ( @@fetch_status = 0)
      begin
      select @PhoneNum = @PhoneNum + (@telno + ‘ ‘)
      fetch next from hC into @tellocn, @telno
      end
      close hC
      deallocate hC
      return @PhoneNum
      end

      This is correct. try it…..

      Like

  10. Sumadrika,
    I think Your answer lies in your query. maybe multiple telno for same customer?

    select rtrim(tellocn), telno from dbo.custtel where custid = @custid and datediscon is null

    Like

  11. Sumadrika,

    You should check for @@fetch_status = 0 after fetching another record.

    So if you replace
    select @PhoneNum = @PhoneNum + (@telno + ‘ ‘)
    by
    if ( @@fetch_status = 0) select @PhoneNum = @PhoneNum + (@telno + ‘ ‘)
    you get the result you want.

    Checking @@fetch_status only in the while loop simply isn’t enough.

    Regards

    Erik

    Like

  12. pinale it is nice.
    but while i am executing it for my program it simply displays ‘command completed successfully’ but it didnt print anything

    Like

  13. Ok nice code BUT
    What is/are the purpose(s) of the cursor ?

    I know you go row by row …

    I want to results output a detail and a master
    I only wish to query to get Master info one time – and query against that result to get detail,
    I want to output both of these results…
    how to do that ??

    Like

  14. Hi ..
    I have parent categories and its related products.
    I want to show top 5 random products for the particular category by use of cursur,,
    can you give me some idea abt this…

    Like

  15. hi pinal on watching your example i have tried the following code what i want is the cursor should fetch the record into variable and the value in that variable is used to fetch the records from another table but i am not getting the result i want so what should i change in my code to get here is the code.

    DECLARE @AccountID INT

    DECLARE @RECORDCOUNT INT

    DECLARE @MINUTECOUNT INT

    DECLARE @COUNTRYNAME VARCHAR(225)

    DECLARE @getAccountID CURSOR

    SET @getAccountID = CURSOR FOR

    SELECT Codes,CountryName

    FROM DailyReports.dbo.CountryCodes

    OPEN @getAccountID

    FETCH NEXT

    FROM @getAccountID INTO @AccountID,@COUNTRYNAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @RECORDCOUNT=(select count(comp_uncomp_calls)as total from DailyReports.dbo.RIL where comp_uncomp_calls=’1′ and dialed_digits like cast(@AccountId as varchar(20)) + ‘%’)

    SET @MINUTECOUNT =(select sum(rounded_dur_secs)/60 as mincount from DailyReports.dbo.RIL where comp_uncomp_calls=’1′ and dialed_digits like cast(@AccountId as varchar(20)) + ‘%’)

    PRINT @RECORDCOUNT
    PRINT @MINUTECOUNT
    PRINT @AccountID
    PRINT @COUNTRYNAME

    IF(@MINUTECOUNT = 0)

    PRINT ‘inserting failed’

    ELSE

    INSERT INTO DailyReports.dbo.Temp(CountryName,TotalMinutes)values(@COUNTRYNAME,@MINUTECOUNT)

    FETCH NEXT

    FROM @getAccountID INTO @AccountID,@COUNTRYNAME

    END

    CLOSE @getAccountID

    DEALLOCATE @getAccountID

    please help me i am new to cursors and stored procedures

    Like

  16. – simple use of cursor print all stored procedures on a sql server 2005

    DECLARE @procName varchar(100)
    DECLARE @getprocName CURSOR
    SET @getprocName = CURSOR FOR
    select s.name from sysobjects s where type = ‘P’ OPEN @getprocName
    FETCH NEXT
    FROM @getprocName INTO @procName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    exec sp_HelpText @procName
    FETCH NEXT
    FROM @getprocName INTO @procName
    END
    CLOSE @getprocName
    DEALLOCATE @getprocName

    Like

  17. Pingback: SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database Journey to SQL Authority with Pinal Dave

  18. I need two (2) examples of the following programs:

    1. Procedure
    2. Function
    3. Cursors
    4. Exceptions

    I hope someone will help me…..

    Tnx…

    Like

  19. hi friend…

    thank u verymuch…..
    its very useful for me and all learners..
    You are doing a good work…
    once again thank u..

    bye

    Ganesaan.M(CHN)

    Like

  20. hi

    i have s salary table

    salid salary

    1 10
    2 20
    3 40
    4 10
    5 50
    6 40

    i want out put like this
    id salary

    2 20
    4 50
    note:-no need to display repated values in above column 10 and 40 is repated values except repated values i wnt to display remaining values

    plz help me

    Like

    • This can be more simpler by this query

      create table TestTable1 ( salid int , salary int)

      insert into TestTable1 values ( 1 ,10)
      insert into TestTable1 values (2 ,20)
      insert into TestTable1 values (3 ,40)
      insert into TestTable1 values (4 ,10)
      insert into TestTable1 values (5 ,50)
      insert into TestTable1 values (6 ,40)

      select max(salid),salary from TestTable1 group by salary having COUNT(salary) = 1

      Thanks
      BM

      Like

  21. # Pramod,

    script 2 can be easily done using while loop, because I wanted to do it with only select statement I did script 2 in a different way.

    Here are scripts for your two posts.

    — Script 1:

    create table TestTable1 ( salid int , salary int)

    insert into TestTable1 values ( 1 ,10)
    insert into TestTable1 values (2 ,20)
    insert into TestTable1 values (3 ,40)
    insert into TestTable1 values (4 ,10)
    insert into TestTable1 values (5 ,50)
    insert into TestTable1 values (6 ,40)

    select A.Salid
    ,A.salary
    from TestTable1 A JOIN (
    select salary
    ,count(*) Counts
    From TestTable1
    group by salary
    having count(*) < 2
    ) B
    ON A.salary = B.salary

    — Script 2
    Create Table TestTable2 ( sex char(1))

    insert into TestTable2 (sex) select ‘m’
    insert into TestTable2 (sex) select ‘m’
    insert into TestTable2 (sex) select ‘f’
    insert into TestTable2 (sex) select ‘m’
    insert into TestTable2 (sex) select ‘f’
    insert into TestTable2 (sex) select ‘f’
    insert into TestTable2 (sex) select ‘m’

    select id = identity (int,1,2) ,sex into #example1 from TestTable2 where sex = ‘m’
    select id = identity (int, 2,2) , sex into #example2 from TestTable2 where sex = ‘f’
    select sex from (
    select id, sex from #example1
    union
    select id, sex from #example2 )X

    drop table #example1,#example2

    Regards,
    IM.

    Like

  22. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  23. I have a table that has a column with comments in it. These comments are associated with a orderNo. For example:

    Orderno Comment

    101 Hello,
    101 How are you

    I would like to have the comments appear on a single row. For example:

    Orderno Comment
    101 Hello, How are you

    Please Help!

    Like

  24. I have one table (LPODTL) with two columns(part_no,indt_no)

    saved records structure is

    part_no indt_no
    P1 i1,i2

    i want output like part_no+indt_no means(p1i1 in one row,p1i2 in second row )

    plz help.

    Like

  25. Hi prasad,

    Here is another solution to your gender table question (2005v required):

    select identity (int,1,1) id, s
    into #t
    from (
    select ‘m’ s union all
    select ‘m’ s union all
    select ‘f’ s union all
    select ‘m’ s union all
    select ‘f’ s union all
    select ‘f’ s union all
    select ‘m’ s) t;

    with cte as (select id – row_number() over (partition by s order by id) diff, s from #t)
    select s from cte group by diff, s;

    drop table #t;

    Regards,
    Blass

    Like

  26. Hi prasad,

    Here is another solution to your gender table question (2005v required):

    select identity (int,1,1) id, s
    into #t
    from (
    select ‘m’ s union all
    select ‘m’ s union all
    select ‘f’ s union all
    select ‘m’ s union all
    select ‘f’ s union all
    select ‘f’ s union all
    select ‘m’ s) t;

    with cte as (select id, id – row_number() over (partition by s order by id) diff, s from #t)
    select s from cte group by diff, s order by max(id);

    drop table #t;

    Regards,
    Blass

    Like

  27. In regards to Mir’s question (I know, a long time ago, but it might help someone), I have created a complete working sample:

    SET NOCOUNT ON;

    DECLARE @OrderComments Table(OrderNo INT, Comment VARCHAR(256))
    INSERT INTO @OrderComments VALUES(101, ‘Hello,’)
    INSERT INTO @OrderComments VALUES(101, ‘my’)
    INSERT INTO @OrderComments VALUES(101, ‘friends.’)
    INSERT INTO @OrderComments VALUES(102, ‘This is another’)
    INSERT INTO @OrderComments VALUES(102, ‘comment’)

    DECLARE @orderNo INT
    DECLARE @comments VARCHAR(4000)
    DECLARE @comment VARCHAR(256)

    DECLARE orderList CURSOR FOR
    SELECT DISTINCT OrderNo FROM @OrderComments

    OPEN orderList
    FETCH NEXT FROM orderList INTO @orderNo

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @comments = ”
    DECLARE commentList CURSOR FOR
    SELECT Comment FROM @OrderComments WHERE OrderNo = @orderNo

    OPEN commentList
    FETCH NEXT FROM commentList INTO @comment

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @comments = @comments + ‘ ‘ + @comment
    FETCH NEXT FROM commentList INTO @comment
    END

    PRINT CAST(@orderNo AS VARCHAR(50)) + ‘: ‘ + @comments

    CLOSE commentList
    DEALLOCATE commentList

    FETCH NEXT FROM orderList INTO @orderNo
    END

    CLOSE orderList
    DEALLOCATE orderList

    Like

  28. @Subramaniam

    Sir, Please have some patience. You asked the same question at-least 5 times in this blog. I understand this issue could be urgent but please ask your question once and have patience, your question will be answered.

    Now My Response to your question.

    There is no straight forward method to do this insert a new column between two existing column in a table.

    Method1: Use SQL Server Management Studio (SSMS, SQL Server 2005).
    Step1: Go to Object Explorer
    Step2: Expand Databases
    Step3: Expand Tables
    Step4: Right Click Table Name Click Design /Modify.
    Step5: Right Click on Table structure and click Insert Column, Check the position where you want to Insert.
    Step6: Click Save.

    Note: Before you Save, make sure you check, Generate Change Script (This will be one of the option in SSMS header). This will show you SQL Script that SQL Server will execute to make this change.

    Method2: Drop and recreate table, In the new table script add the new column.

    ~ IM.

    Like

  29. I have doubt in pivot concept
    I created one table and select that table using pivot concept
    but i cant use where condition,i need only I semester result or II semester result but its show in both semester if we are use where condition mean its should be work but i cant do where condition….any one help me….

    CREATE TABLE TestOne
    (
    CourseName varchar(100),
    Semester varchar(100),
    StudentNumber varchar(100),
    StudentName varchar(100),
    Date datetime,
    AttendanceStatus varchar(20)
    )

    insert into TestOne values(‘BA’,’I Semester’,’BC985233′,’RAJA’,’2009-07-08′,’P’)
    insert into TestOne values(‘BA’,’I Semester’,’AC232233′,’Sathish’,’2009-07-08′,’A’)
    insert into TestOne values(‘BA’,’I Semester’,’BC985233′,’RAJA’,’2009-07-10′,’A’)
    insert into TestOne values(‘BA’,’I Semester’,’AC232233′,’Sathish’,’2009-07-10′,’P’)
    insert into TestOne values(‘BA’,’I Semester’,’BC985233′,’RAJA’,’2009-07-15′,’P’)
    insert into TestOne values(‘BA’,’I Semester’,’AC232233′,’Sathish’,’2009-07-15′,’A’)
    insert into TestOne values(‘BA’,’I Semester’,’BC985233′,’RAJA’,’2009-07-20′,’P’)
    insert into TestOne values(‘BA’,’I Semester’,’AC232233′,’Sathish’,’2009-07-20′,’P’)
    insert into TestOne values(‘BA’,’II Semester’,’BC985233′,’RAJA’,’2009-07-25′,’A’)
    insert into TestOne values(‘BA’,’II Semester’,’AC232233′,’Sathish’,’2009-07-25′,’P’)
    insert into TestOne values(‘BA’,’II Semester’,’BC985233′,’RAJA’,’2009-07-30′,’P’)
    insert into TestOne values(‘BA’,’II Semester’,’AC232233′,’Sathish’,’2009-07-30′,’A’)

    DECLARE @listCol VARCHAR(2000)
    DECLARE @query VARCHAR(4000)
    DECLARE @query1 VARCHAR(4000)

    SELECT @listCol = STUFF(( SELECT DISTINCT ‘],[‘ + convert(nvarchar,date,111) FROM TestOne
    ORDER BY ‘],[‘ + convert(nvarchar,date,111) FOR XML PATH(”)), 1, 2, ”) + ‘]’

    –print @listCol
    SET @query =
    ‘SELECT * FROM
    (SELECT StudentName,StudentNumber,CourseName,Semester,[Date]
    , AttendanceStatus
    FROM TestOne) src
    PIVOT (max(AttendanceStatus) FOR [Date]
    IN (‘+@listCol+’)) AS pvt’

    EXECUTE (@query)

    Like

  30. prasad

    hi

    i have s salary table

    salid salary

    1 10
    2 20
    3 40
    4 10
    5 50
    6 40

    i want out put like this
    id salary

    2 20
    4 50
    note:-no need to display repated values in above column 10 and 40 is repated values except repated values i wnt to display remaining values

    plz help me

    HERE is Query

    select * from test where val in (select val from test group by val having count(val)=1)

    Like

  31. @mahendra

    Just add an aggregate to salid. Because it is always one value, using an aggregate won’t change anything. Also, the HAVING can be just COUNT(*). No reason to mention salary, the GROUP BY on salary takes care of that:

    WITH
    salary(salid, salary)
    AS
    (
    SELECT 1, 10 UNION ALL
    SELECT 2, 20 UNION ALL
    SELECT 3, 40 UNION ALL
    SELECT 4, 10 UNION ALL
    SELECT 5, 50 UNION ALL
    SELECT 6, 40
    )
    SELECT
    MAX(salid),
    salary
    FROM
    salary
    GROUP BY
    salary
    HAVING
    COUNT(*) = 1

    Like

  32. Sumardika…

    You should fetch first record before entering in to the loop. Next fetch statement should come after select @PhoneNum = @PhoneNum + (@telno + ‘ ‘) statement

    That should return you good results

    Like

  33. Hi,
    Is there any way to define RecordSet in Sql 2005.
    I want to generate variable for each and every field in the table, my table has 35 fields, its tedious job to declare every field. I want to define Cursor for same field and need to manipulate field record and want to store all the fields in temp table.
    By using –
    Select top 0 * into #temp from
    I able to generate temp table with same table structure.

    Kindly let me know if any method to generate recordset.
    eg. in Informix we can do
    define record like .*

    Thanks

    Like

  34. create procedure [dbo].[shankar](@parameter nvarchar(100))
    as
    declare @PhoneNum varchar(100)
    DECLARE @eid nvarchar
    DECLARE @getProductID CURSOR
    SET @getProductID = CURSOR FOR
    SELECT emp_id
    FROM empold
    OPEN @getProductID
    FETCH NEXT
    FROM @getProductID INTO @eid
    WHILE @@FETCH_STATUS = 0
    BEGIN

    if ( @@fetch_status = 0)
    begin
    select @PhoneNum = @PhoneNum + (@eid )

    end

    FETCH NEXT
    FROM @getProductID INTO @eid
    END
    return @PhoneNum
    CLOSE @getProductID
    DEALLOCATE @getProductID

    i am getting output
    The ‘shankar’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
    please help me

    Like

    • That’s the way while-loop works. First you fetch something and then you evaluate the comparison clause to see if you need to step out of the loop (or not to get in there in the first place).

      Do-while-loop works other way around. First you enter the loop, then you fetch something and finally you evaluate and check if you need to bail out.

      It’s much simpler when you do things like that even when SQL does not have pure do-while construct.

      Like

  35. i Have to create two tables as follow

    tbl1
    student_ID subject_ID Mark
    ————————————-
    1 1 50
    1 2 67
    1 3 80
    2 1 24
    2 2 57
    2 3 60

    tbl2
    subject_ID Subject
    ——————————-
    1 English
    2 Maths
    3 Science
    i Want the Result as Follows

    student_ID English Maths Science
    ___________________________
    1 50 67 80
    2 24 57 60

    Is It Possible?

    Its Very Urgent

    Pls Mail Me

    Like

    • @Ramesh

      SELECT
      tbl1.student_ID,
      SUM(CASE tbl2.subject WHEN ‘English’ THEN tbl1.Mark END) English,
      SUM(CASE tbl2.subject WHEN ‘Maths’ THEN tbl1.Mark END) Maths,
      SUM(CASE tbl2.subject WHEN ‘Science’ THEN tbl1.Mark END) Science
      FROM
      tbl1,
      tbl2
      WHERE
      tbl2.subject_ID = tbl1.subject_ID
      GROUP BY
      tbl1.student_ID;

      Like

  36. SELECT
    tbl1.student_ID,
    –SUM(CASE table2.subject WHEN ‘English’ THEN Table1.Marks END) English,
    –SUM(CASE table2.subject WHEN ‘Maths’ THEN Table1.Marks END) Maths,
    –SUM(CASE table2.subject WHEN ‘Science’ THEN Table1.Marks END) Science
    sum(CASE tbl2.subject_ID when ‘english’ then tbl1.marks end) enlish,
    sum(CASE tbl2.subject_ID when ‘Maths’ then tbl1.marks end) maths,
    sum(CASE tbl2.subject_ID when ‘science’ then tbl1.marks end) science
    FROM
    tbl1,
    tbl2
    WHERE
    tbl2.subject_ID = tbl1.subject_ID
    GROUP BY
    tbl1.student_ID

    Its Working……
    I am Very happy to ur immediate solution

    Thank You

    and i want another one code for as same.
    the subjects are runtime.
    we dont know the name of the subject
    what can i do now?

    Like

  37. nice for a quick readonly cursor

    declare @Question varchar(100)
    declare @Answer varchar(100)

    declare faq_cursor cursor fast_forward for
    select question, answer
    from faq

    open faq_cursor

    fetch next from faq_cursor
    into @Question, @Answer

    while @@FETCH_STATUS = 0
    begin
    fetch next from faq_cursor
    into @Question, @Answer
    end
    close faq_cursor
    deallocate faq_cursor

    Like

  38. Hi ,i wrote this query but it didn’t work ,plz guide me :(( what’s it’s problem?

    DECLARE
    @Contact_name nvarchar(50),
    @Product_name nvarchar(50),
    @CustomerID nvarchar(50),
    @Row_number nvarchar(50)

    DECLARE cursor_Product CURSOR FOR
    SELECT PurchasedQuantity1.ContactName,PurchasedQuantity1.ProductName,PurchasedQuantity1.ProductPurchasedQuantity
    FROM
    (SELECT Orders.CustomerID,Customers.ContactName, SUM([Order Details].Quantity) AS ProductPurchasedQuantity,ProductName
    FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
    INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
    INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
    GROUP BY Orders.CustomerID,Customers.ContactName, [Order Details].ProductID,ProductName) AS PurchasedQuantity1
    INNER JOIN
    (SELECT CustomerID, MAX(ProductPurchasedQuantity) MaxQuantityPurchased
    FROM
    (SELECT Orders.CustomerID, SUM([Order Details].Quantity) AS ProductPurchasedQuantity
    FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
    GROUP BY Orders.CustomerID,[Order Details].ProductID) AS PurchasedQuantity2
    Group by CustomerID) AS MaxQuantity
    ON PurchasedQuantity1.CustomerID = MaxQuantity.CustomerID AND PurchasedQuantity1.ProductPurchasedQuantity = MaxQuantity.MaxQuantityPurchased
    Order by ContactName

    OPEN cursor_Product FETCH NEXT FROM cursor_Product INTO

    @Contact_name,
    @Product_name,
    @CustomerID

    set @Row_number =0
    WHILE @@FETCH_STATUS = 0
    BEGIN

    set @Row_number=@Row_number + 1;
    PRINT ‘Row NO Customer ID ContactName FAvorite Product Name ‘ +

    ‘ +
    ‘========= ========== ======== ===================’ +

    ‘ +
    @Row_number+’ ‘+ @CustomerID + ‘ ‘+ @Contact_name + ‘ ‘+@Product_name

    END

    CLOSE cursor_Product
    DEALLOCATE cursor_Product

    Like

  39. Hello,
    I’m relatively new to SQL stored procedure and I’m having a requirement.

    I’m having a table in a database with some many rows and i need to display some particulars based on a condition with loops.

    HELP ME OUT

    Like

  40. Hi,
    I have table like below,

    first_nm last_nm Gender
    Raj s F
    Rajp e M
    Kar Sh M
    De re M
    Sw ko F
    Kal la F
    Sa Sa M

    now i need to update my table each row last_nm with another record last_nm within gender(means female-female,male-male)

    example:
    first_nm last_nm Gender
    Rajp re M
    Kar e M
    De Sh M
    Sw s F
    Kal ko F
    Raj la F

    Thanks,
    Srr

    Like

  41. Its nice article….. THis is basic level of things…
    But i need lettle Critical calculation and some Critical and depth manipluations…

    if its possible plz provide code for me….

    thank u so much

    Like

  42. –Hi friends see small example of Cursor versus Non Cursor
    create table #Temp
    (
    Id int identity(1,1),
    Fname nvarchar(25) not null,
    Lname nvarchar(25) not null
    )
    insert into #Temp values (‘Think’,’India’)
    insert into #Temp values (‘Kniht’,’India’)
    insert into #Temp values (‘How ‘,’India’)
    insert into #Temp values (‘Fine ‘,’India’)

    select * from #Temp
    —-Cursor and non cursor example
    Declare @id int
    Declare Curs_Temp cursor for
    select id from #Temp
    open Curs_Temp
    Fetch next from Curs_Temp into @id
    while @@fetch_status=0
    Begin
    Select * from #Temp where id=@id
    Fetch next from Curs_Temp into @id
    end
    close Curs_Temp
    deallocate Curs_Temp

    ——Same example without cursor
    Declare @id int
    Declare @i int
    select @id=Count(id) from #Temp
    set @i=0
    while (@i@id)
    Begin
    Set @i=@i+1
    Select * from #Temp where id=@i

    end

    ——-Drop Temp Table
    drop table #Temp

    Like

  43. Hi i want to genrate RegID Like That
    R201012310001
    R201012310002
    ……………………
    R2010123100010
    (R +Year + MM+DD + 00001)
    and again in next year last 4 digit will be start from 00001.
    How Can i do this in sql server 2008

    Like

    • You need to do this everytime a row is added to the table
      declare @n int
      set @n=(select right(col,4)*1 from table)
      set @n=coalesce(@n,0)
      select ‘R’+convert(char(8),getdate(),112)+right(‘0000’+cast(@n+1 as varchar(4)),4)

      Like

  44. hii… can anyone plz tell me how to retrive the value of cursor output type variable in java.. i have a following Stored Procedure…

    Create PROCEDURE [dbo].[USP_VIEW_PAYMENT]
    (
    @pPartyId int,
    @pAccNum varchar(20),
    @cGETDATA cursor varying output
    )
    AS
    Begin
    /* select es.*, tm.Description
    from T_TRANSACTION_REQUEST tr, T_EMI_SCHEDULE es, M_TXNSTATUS_MASTER tm
    where tr.Txn_Ref_Number=es.Txn_Ref_Number
    and es.Status=tm.Status_ID
    and tr.Party_ID=@pPartyId
    and tr.Account_No=@pAccNum
    Order by es.Txn_Ref_Number, es.Emi_Ref_Number

    End*/

    declare @vWhere as varchar(500)
    DECLARE @VAR1 VARCHAR(50)
    DECLARE cGETDATA CURSOR FOR

    select Txn_Ref_Number from T_TRANSACTION_REQUEST where Party_ID=@pPartyId and Account_No=@pAccNum

    OPEN cGETDATA

    FETCH NEXT FROM cGETDATA INTO @VAR1

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET NOCOUNT ON;
    declare @sQuery VARCHAR(MAX)

    — SELECT DATA HERE
    PRINT @VAR1
    set @vWhere = ‘where Txn_Ref_Number = ‘ + ”” + @VAR1 + ””

    set @sQuery = ‘SELECT Txn_Ref_Number as ”Transaction Reference Number”,Emi_Ref_Number as ”EMI No.”, Due_Date as ”Date”, Principle_Amt as ”Principle Amount”,
    Int_Amt as ”Interest Amount”,Status as ”Status”
    from dbo.T_EMI_SCHEDULE ‘ + @vWhere
    EXEC(@sQuery)
    PRINT @sQuery
    FETCH NEXT FROM cGETDATA INTO @VAR1

    END

    CLOSE cGETDATA
    DEALLOCATE cGETDATA
    End

    ————————————————-

    i want that “cGetData” which is of type cursor output in my java code… i have used registerOutParameter() method to reterive that but “unsupportedoperationexception” is thrown..

    plz help..

    and thanks in advance..

    Like

  45. Hi Pinal,

    Thanks alot, i had completed forgotten how to write cursor,
    was looking for a simple example, and found yours.

    Thanks
    Menon

    Like

  46. Hi Im Deepak

    I want to know can we pass the paremater in cursor loop

    Eg:
    DECLARE my_cursor CURSOR FOR
    select distinct(floors) from Dealer where bh = @bh
    OPEN my_cursor
    FETCH NEXT FROM my_cursor
    INTO @Event_ID

    can you pls let me know

    Like

  47. Hi Pinal,

    This is R.Rajesh from Tamilnadu.Ur Articles are Excellent to beginners like me.Keep this long and serve to us like this.Thanks Alot.

    Like

  48. Thank you, I have always found information you present very useful and this one most of all. I just completed the first CURSOR statement I understood! You have made it possible for me to fetch data that was stored in such a way that it was not easily done. Thanks!

    Like

  49. Hi,

    I have written a cursor in MS SQL server and was able to execute it also. But i have an issue where it’s not displaying the results in the order what i have written it’s displaying randomly. First I am selecting the records from Info table and then from sinfo table.

    Example:

    DECLARE emails CURSOR FOR select distinct LTRIM(RTRIM(useremail)) from Info
    UNION
    select DISTINCT LTRIM(RTRIM(useremail)) from SInfo
    SET NOCOUNT ON
    DECLARE @email varchar(100)
    OPEN emails
    FETCH NEXT from emails
    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH NEXT from emails into @email
    select username, password, email from info where useremail = @email
    union
    select username, password, email from Sinfo where useremail = @email
    END
    CLOSE emails
    DEALLOCATE emails

    Any suggestions would be much appreciated.

    Thanks

    Like

  50. Pingback: SQL SERVER – Using RANKING Functions Instead of SQL Looping Logic of Cursor – Quiz – Puzzle – 8 of 31 « SQL Server Journey with SQL Authority

  51. Hi All,

    I have an array of emails and i want to split every email and check every email from database and its correspondence Status.
    Please Help!

    Like

  52. Hi Pinal

    Can u give an example of selecting top few rows from each group where we are getting the result by joining two or more tables.can we give an alias name to the joining of two tables, if yes please let me know how, i shall be thankful.

    Thanks
    Taruna

    Like

    • Based on what I have understood from your query I have done something…

      Create table t_Employee
      ( EmployeeID Int,
      EmployeeName nVarChar(20)
      )

      Create table t_Deduction
      ( DeductionID Int,
      Description nVarChar(20)
      )

      Create table t_EmployeeDeductions
      ( EmployeeID Int,
      DeductionID Int
      )

      INSERT TABLE t_Employee
      Select 1,’ABC’
      UNION ALL
      Select 2,’DEF’
      UNION ALL
      Select 3,’GHI’
      UNION ALL
      Select 4,’CAT’
      UNION ALL
      Select 5,’ABC’

      INSERT TABLE t_Deduction
      Select 1,’PF’
      UNION ALL
      Select 2,’Phone Bill’
      UNION ALL
      Select 3,’Insurance’

      INSERT TABLE t_EmployeeDeduction
      Select 1,1
      UNION ALL
      Select 1,2
      UNION ALL
      Select 1,1
      UNION ALL
      Select 1,2

      Select top 2 ED.*
      from t_Employee AS Emp
      INNER JOIN
      t_EmployeeDeductions ED
      ON Emp.EmployeeID = ED.EmployeeID

      Now if you have any question please feel free to revert…

      Regards
      Malaya

      Like

  53. Emil

    when i am creating an SSIS, where I need to do a merge join between Excel source data and OLEDB source data(data from SQL table),ofcourse after sorting, it is giving me the following errors not on merge join but on retrieving data from the OLEDB source itself as the data is huge-in crores, so tell me the solution if u have one, wud b thankful, the reason why i am doing merge join is i have data in the excel file out of which some records are there in SQL database and i want to know what data is there and what is not , the data is in the related tables . Also i want the output containing the columns of excel as well as the columns (fields) of the tables in a single excel sheet so that the other persons or should i say the users of the data can get to know what data is useful and they want to compare.
    i want to match on 5 fields, say firstname,lastname,city,state and the first 5 characters of the street address.how can i use LTRIM,RTRIM,LEFT and UPPER string functions on the data i have imported from excel?

    Sorry, so many questions, but i really need help.

    Thanks
    Tanu

    Like

  54. Emil

    when i am creating an SSIS, where I need to do a merge join between Excel source data and OLEDB source data(data from SQL table),ofcourse after sorting, it is giving me the following errors not on merge join but on retrieving data from the OLEDB source itself as the data is huge-in crores, so tell me the solution if u have one, wud b thankful, the reason why i am doing merge join is i have data in the excel file out of which some records are there in SQL database and i want to know what data is there and what is not , the data is in the related tables . Also i want the output containing the columns of excel as well as the columns (fields) of the tables in a single excel sheet so that the other persons or should i say the users of the data can get to know what data is useful and they want to compare.
    i want to match on 5 fields, say firstname,lastname,city,state and the first 5 characters of the street address.how can i use LTRIM,RTRIM,LEFT and UPPER string functions on the data i have imported from excel?

    Sorry, so many questions, but i really need help.

    Thanks
    Tanu

    Error: 0x80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
    Error: 0x80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
    Error: 0xC004704A at Data Flow Task, DTS.Pipeline: The buffer manager cannot extend the file “C:\DOCUME~1\tarunm\LOCALS~1\Temp\a\DTS1A93.tmp” to length 3473188. There was insufficient disk space.
    Error: 0xC004704A at Data Flow Task, DTS.Pipeline: The buffer manager cannot extend the file “C:\DOCUME~1\tarunm\LOCALS~1\Temp\a\DTS1A94.tmp” to length 3473188. There was insufficient disk space.
    Error: 0x80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
    Error: 0x80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
    Error: 0xC004704A at Data Flow Task, DTS.Pipeline: The buffer manager cannot extend the file “C:\DOCUME~1\tarunm\LOCALS~1\Temp\a\DTS1A95.tmp” to length 3473188. There was insufficient disk space.
    Error: 0xC004704A at Data Flow Task, DTS.Pipeline: The buffer manager cannot extend the file “C:\DOCUME~1\tarunm\LOCALS~1\Temp\a\DTS1A96.tmp” to length 3473188. There was insufficient disk space.
    Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
    Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
    Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
    Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
    Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
    Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
    Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
    Information: 0x4004800D at Data Flow Task, DTS.Pipeline: The buffer manager failed a memory allocation call for 3473184 bytes, but was unable to swap out any buffers to relieve memory pressure. 1 buffers were considered and 0 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
    Information: 0x4004800D at Data Flow Task, DTS.Pipeline: The buffer manager failed a memory allocation call for 3473184 bytes, but was unable to swap out any buffers to relieve memory pressure. 7 buffers were considered and 3 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
    Error: 0xC0047012 at Data Flow Task, DTS.Pipeline: A buffer failed while allocating 3473184 bytes.
    Error: 0xC0047012 at Data Flow Task, DTS.Pipeline: A buffer failed while allocating 3473184 bytes.
    Error: 0xC0047011 at Data Flow Task, DTS.Pipeline: The system reports 44 percent memory load. There are 8589111296 bytes of physical memory with 4764954624 bytes free. There are 2147352576 bytes of virtual memory with 104652800 bytes free. The paging file has 12660408320 bytes with 8538820608 bytes free.
    Error: 0xC0047011 at Data Flow Task, DTS.Pipeline: The system reports 44 percent memory load. There are 8589111296 bytes of physical memory with 4764971008 bytes free. There are 2147352576 bytes of virtual memory with 104652800 bytes free. The paging file has 12660408320 bytes with 8538845184 bytes free.
    Warning: 0x8007000E at Data Flow Task, Sort 1 [796]: Not enough storage is available to complete this operation.
    Warning: 0x8007000E at Data Flow Task, Sort 1 [796]: Not enough storage is available to complete this operation.
    Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
    Error: 0xC020821A at Data Flow Task, Sort 1 [796]: The Sort transformation cannot queue a work item to its thread pool. There is not enough memory available.
    Error: 0xC020821A at Data Flow Task, Sort 1 [796]: The Sort transformation cannot queue a work item to its thread pool. There is not enough memory available.
    Warning: 0x8007000E at Data Flow Task, Sort 1 [796]: Not enough storage is available to complete this operation.
    Error: 0xC0208296 at Data Flow Task, Sort 1 [796]: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error.
    Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component “Sort 1″ (796) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
    Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread “WorkThread1″ has exited with error code 0x80004005.
    Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread “WorkThread3″ received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
    Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread “WorkThread2″ received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
    Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread “WorkThread2″ has exited with error code 0xC0047039.
    Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread “WorkThread3″ has exited with error code 0xC0047039.
    Error: 0xC02020C4 at Data Flow Task, OLE DB Source [187]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
    Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component “OLE DB Source” (187) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
    Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread “SourceThread1″ has exited with error code 0xC0047038.
    Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
    Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
    Information: 0x4004300B at Data Flow Task, DTS.Pipeline: “component “Excel Destination” (4131)” wrote 0 rows.
    Task failed: Data Flow Task
    Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (32) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    SSIS package “Package1.dtsx” finished: Failure.
    The program ‘[4820] Package1.dtsx: DTS’ has exited with code 0 (0x0).

    Like

  55. Hi Malaya

    u are right but i dont need that solution as my problem is i am getting the data from three related tables.say, i am grouping on a field from Table A and against every group i am calculating sum of values from a field which is in table B and also i am sorting on this sum field and now i get say 20 -30 records for each group, but i want to have top 3-3 from each group.

    I dont want to store the intermediate dat in any of temp tables.

    I just want a single query.

    If u can help, i really appreciate.

    Thanks

    Taruna

    Like

  56. I want to know what will happen for the below scenario.

    I have done some testing and found that it is a problem.

    Now, I am declaring cursor for table, in a row by row fetch I am inserting a data that qualifies the select statement while declaring cursor.

    For e.g. if my select statement gave me 4 records for salary > 1000 now for each of these 4 record I inserted one record with salary 1000+ in the same table then the records inserted newly should be 4. so the total records for salary > 1000 should be 8 after the loop ends.

    But this is not the case. The loop executes for random numbers in my case 886 to 122122 times.

    Please suggest how to make sure Rows selected for cursor for the first time do not change with example.

    Thanks in advance

    Like

  57. Msg 137, Level 15, State 2, Line 13
    Must declare the scalar variable “@cursor”.
    Msg 137, Level 15, State 2, Line 15
    Must declare the scalar variable “@cursor”.
    Msg 137, Level 15, State 2, Line 16
    Must declare the scalar variable “@cursor”.

    this was the msg which i was getting at the time of execution

    Like

  58. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  59. hi

    i want to select the tables which are seeded from database creation.can any one help to create it using cursors

    seed tables means the tables which have the default data when a database is created

    please tell me its urgent

    Like

  60. Dear friends,
    Please Help me to solve this.
    From cursor 4 Rows are returned.
    But I need that 4 rows concatenated as a single row.

    Thank you

    Like

  61. Dear sir ,
    Please help me ,
    How i get the complete level of Binary tree ,Ex. Suppose in binary tree it complete the pair then its level is 1 , it its child complete the pair both child then parent node complete the level 2 and child node complete the level 1 same as apply for every node .
    The main thing is the i create a prco in which pass the node id get the completed level of passing node

    EXEC spGetLevel(1)
    Ans is 2 means total pair is 3 1 for patent node and 2 pair for child node

    Please Help me …..

    Like

  62. To avoid cursors in sql server we can do it by below simple scenario..

    –create temporary table with same structure of its original table
    Create table #temp_table_name(id bigint primary key identity(1,1),[other parametrs that you want to include here….] )

    insert into #temp_table_name( parametrs that you want to include here….)
    select [parameters include here which you are going to processed]
    FROM Original_table_name

    –replace your cursor logic by below scenario….
    SELECT @MINROWID=MIN(ID),@MAXROWID=MAX(ID) FROM #temp_table_name WITH(NOLOCK) ;

    SET @CURRROWID=@MINROWID;

    WHILE (@CURRROWID<=@MAXROWID)
    BEGIN

    ——you will get one row at a time here..
    SELECT @parameters1,@para2……..n
    FROM #temp_table_name WHERE ID=@CURRROWID
    set @CURRROWID=@CURRROWID+1

    END

    Like

  63. Hi, Would you be able to help me with the below example:
    I have 3 tables with columns:
    Table 1: Orders
    columns: order_id; cancelled_flag;
    Table 2: Orders_details
    columns: order_id; product_id; quantity;
    Table 3: Products
    columns: product_id; amount_inventory;

    If the flag in the Table 1 is set to “Y” the trigger or cursor should check what were the amounts ordered in Table 2 and add these amounts back to the appropriate products in Table 3. There is an option that there are several products ordered under one order id therefore I suppose I need a cursor.
    I would be gratefull for your help.
    Thank you,

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s