SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2

I have recently received email that I should update SQL SERVER – Simple Example of Cursor with example of AdventureWorks database.

Simple Example of Cursor using AdventureWorks Database is listed here.

USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE
@getProductID CURSOR
SET
@getProductID = CURSOR FOR
SELECT
ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
@ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE
@getProductID
DEALLOCATE @getProductID
GO


Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – 2005 – A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold
Next Post
SQLAuthority News – SQL Server 2005 is The Data Platform Leader

Related Posts

69 Comments. Leave new

  • how should i use cursor for make a dynamic query. like
    declare @test_temp_count_one cursor
    declare @test_temp_query_one nvarchar(1000)
    declare @VAR_DIVIDE1 varchar(20)
    declare @var_table varchar(100)
    declare @hold_temp_value varchar(20)
    set @VAR_DIVIDE1 = 10
    set @var_table = ‘table13’
    set @hold_temp_value = 20

    set @test_temp_query_one = ‘ SET
    @test_temp_count_one = (SELECT TOP ‘ + @VAR_DIVIDE1 + ‘ id FROM ‘+ @var_table +’ WHERE ID > ‘ + @hold_temp_value + ‘ ORDER BY ID ) ; OPEN @test_temp_count_one ‘
    exec (@test_temp_query_one)

    when i execute this code , i found an error……
    error:

    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable ‘@test_temp_count_one’.

    can anybody help me…….
    thanks in advance

    Reply
  • hi ,
    iam new to sqlserver, but i need to write a stored procedure using cursor. i have field in the application called refno. and referencing to that field i need to capture the other field values for a particular date .The refno. field have a specific value for some application type and other field value changes.
    could u please help me in sorting out this.

    many thanks

    Reply
  • Hi,
    I’m working with SQL and I admit that I am struggling!!! Here’s my problem, I’m trying to use a cursor to look at:
    1. The first line…
    2. The second line…
    if those lines are equal only output the first line. and fetch the next…compare that to the first line….if those are equal do not output anything…simply fetch next.

    BUT
    if the rows are not equal…output both rows and make the second row the “comparison” row.

    In c# it would be something like

    int Variable = variable1

    for (int row = 0; row < rowCount; row++)
    {
    IF (variable 1 == variable[i])
    {
    output variable1
    }
    else
    {
    variable1 = variable[i]
    }
    }

    any help would be much appreciated!

    Reply
  • Md. Shahnawaz Akhtar
    October 20, 2009 8:49 am

    Sir,

    You are really doing a great job. I am a MCA student. I have become a regular user of your blog for the past one month and it seems that I have addicted to your blog. I have learned many things from your blog. So, Iwant to thank you from the bottom of my heart.

    Keep it up and God bless you.

    Reply
  • i have a scenario, where i have to insert agent ids, Say i have 5 agentids(1,2,3,4,5) in Table A and when i insert any records in Table B it has to assign the agent id , If there is a transaction which inserts 21 records in TABLE B for each record agentid should be assigned ,till 5 records it should assign the agent ids accordingly n for the sixth record it should start from 1 so on.For the 21st record the the agentid 1 should be assigned

    ex: TableB
    c1 c2 c3 agentid
    r1 r12 r13 1
    r2 r22 r23 2
    r3 r32 r33 3
    r4 r42 r43 4
    r5 r52 r53 5
    r6 r62 r63 1
    r7 r72 r73 2
    ………
    ……
    ……
    r21 r1 r14 1 “last record ”

    what will be the best approach fr this

    Thanks,
    Preetham.

    Reply
  • brijnandan — Error statement is itself telling you the solution, you need to declare the variable.

    set @test_temp_query_one = ‘ declare @test_temp_count_one cursor SET
    @test_temp_count_one = (SELECT TOP ‘ + @VAR_DIVIDE1 + ‘ id FROM ‘+ @var_table +’ WHERE ID > ‘ + @hold_temp_value + ‘ ORDER BY ID ) ; OPEN @test_temp_count_one ‘
    exec (@test_temp_query_one)

    Reply
  • i have the table with the following fields

    FromGrams
    ToGrams
    Price
    CountryID

    suppose consider the table have the value as follows

    FromGrams=10
    ToGrams=100
    Price=0.25
    CountryID=221

    ________________________________________________

    if suppose user adding new range that have the value like follows

    FromGrams=50
    ToGrams=120
    Price=0.95
    CountryID=221

    then the result table will like this

    FromGrams ToGrams Price CountryID

    10 50 0.25 221

    51 120 0.95 221

    like that all conditions have to satisfy

    Reply
  • Dear Pinal,

    I’ve got the same problem below as desribed by Smita

    Smita
    Hello Pinal,

    I have begin to learn writing Stored procdures using cursors. Please give a few examples of how I can concatenate a set of strings from a table field and use the result string to update another table field.

    Thanks

    Reply
    • Can you give us some sample data and exprected result?
      It seems you need to use concatenation method specified here
      See the code under “Generate data in csv format ”

      Reply
  • Is there a way to populate a cursor with multiple sql statements?

    Reply
  • Mr Thipphaphone
    October 5, 2010 9:33 am

    Hi,
    please you send me the e-book vie email address of cursor of sql server 2005
    I want to learn more
    thank you sir
    Phone

    Reply
  • Hi all,
    Im using cursor in my procedure,Im getting the value from 1 table,that value is stored in local variable @var,that value is the column name of another table.I want to pass the @var in Cursor Select Statement.How i do that..the Below statements im using…

    Set @var=(select field from table1)

    Declare cursorname cursor for
    Select @var from table2 where No=2 —>Getting Error
    open cursorname

    Reply
    • This is not possible until you use dynamic sql. Can you give us more informations on what you are doing with cursor?

      Reply
  • Hi EveryOne,

    I need a solution to this Situation .
    In a table there is a column with values 00090,000080,000189 and so on
    I need to update the column of the table to 0090,0080,0189 and so on.the data type of the column is varchar(20)
    Can we do that .

    If so please let me know.

    Thanks,
    Sravanthi

    Reply
    • use right(col,4)

      Reply
      • create table fu1(uf varchar(20),id int)
        insert into fu1(uf,id)
        select ‘00000090’,1
        union all
        select’000080′,2
        union all
        select ‘000189’,3
        select * from fu1
        update fu1 set uf=’0090′ where id=1
        update fu1 set uf=’0080′ where id=2
        update fu1 set uf=’0189′ where id=3

      • This is very simple

        update fu1 set uf=right(uf,4)

  • 1) how to access indivudal values in a multi value filed.

    for eg:

    table1
    col1
    (1,2,3)
    i want extract each value from col1 and wants to compare with single value column.

    2) how to enter into particular user database in stored procedure.

    Reply
    • 1 Search for split function in this site
      2 you need to use three part names dbname.ownername.objectname in the procedure

      Reply
  • Sunil Kumar Kaushal
    May 10, 2011 1:45 pm

    Hi All,

    This blog is very useful to everyone we can discuss our probs here & got a good response with less time.

    By the way my self Sunil Kumar kaushal i am wokring as a MIS Analyst but i want to become a SQL Develper, i have good knowledge of SQL SERVER 2005 but i want to work on live project.

    Please guide me how can i do that..

    Regards
    Sunil Kumar Kaushal

    Reply
  • Sunil Kumar Kaushal
    May 11, 2011 5:52 pm

    Hi,

    I have created a cursor on Trigger but getting an error. Row is getting effected after updation.

    Server: Msg 18025, Level 16, State 1, Line 0
    xp_sendmail: failed with mail error 0x80040111
    (1 row(s) affected)

    Can any one help me to resolve this error..

    declare @deptno int
    declare @deptname varchar(10)

    declare curdept cursor for
    SELECT vdeptno, vdeptname from departments

    OPEN curdept

    FETCH curdept INTO @deptno, @deptname
    WHILE(@@fetch_status=0)
    BEGIN
    Print ‘Department No =’ + cast(@deptno as varchar(10)
    Print @deptname

    FETCH curdept INTO @deptno, @deptname
    END

    CLOSE curdept
    DEALLOCATE curdept

    Reply
  • Hai Pinal,

    How Could i use if else inside cursor.

    Reply
  • Hi Pinal,

    Can you help me with CASE Update with Cursor. The cursor is workin fine but not updating the value properly.

    ——
    Declare @state varchar (20) , @city varchar (20) ,@occgrp varchar (10)

    Declare dimclinfo Cursor
    For
    Select state,city , occgrp
    from dimclinfo
    order by state,city,occgrp

    open dimclinfo;
    while @@fetch_status = 0
    begin

    Fetch next from dimclinfo
    into @state , @city , @occgrp

    update dimclinfo
    set combinationid = CASE
    WHEN ( @state is null and @city is null and @occgrp = ‘Overall’ )
    THEN 411
    WHEN (@state is not null and @city is null and @occgrp = ‘Overall’ )
    THEN 412
    WHEN ( @state is null and @city is not null and @occgrp = ‘Overall’)
    THEN 413
    WHEN (@state is null and @city is null and @occgrp != ‘Overall’ )
    THEN 414
    WHEN (@state is not null and @city is null and @occgrp != ‘Overall’)
    THEN 415
    WHEN ( @state is not null and @city is not null and @occgrp!= ‘Overall’)
    THEN 416 — It is populating only this value throughout
    else 1
    END

    Fetch next from dimclinfo
    into @state , @city ,@occgrp

    END
    CLOSE dimclinfo ;
    Deallocate dimclinfo;

    Reply
  • hello i have a question …
    suppose a query
    select * from emp where empid in(1,5,8,2,4,10,9);

    what i want is
    the result should come as in the same sequence as the sequence maintained in above query as(1,5,8,2,4,10,9).

    generally what happen is the result come in sorted order but i do not want that sorted order.

    Reply
  • hoe can i update multiple row in sqlserver but there is no primary or unique key

    Reply
  • i like you code
    no formating is better
    make computer more fast

    Reply

Leave a Reply