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)
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
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
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!
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.
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.
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)
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
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
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 ”
Is there a way to populate a cursor with multiple sql statements?
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
Dont concentrate on cursor. Search for Set based approach in Google/Bing
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
This is not possible until you use dynamic sql. Can you give us more informations on what you are doing with cursor?
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
use right(col,4)
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.
1 Search for split function in this site
2 you need to use three part names dbname.ownername.objectname in the procedure
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
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
Hai Pinal,
How Could i use if else inside cursor.
You can use it just like you use for other statements. Refer this to know how to use it
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;
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.
hoe can i update multiple row in sqlserver but there is no primary or unique key
i like you code
no formating is better
make computer more fast