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
196 Comments. Leave new
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
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
I NEED SIMPLE CURSOR FUNTION TO CONCAT THE TWO FEILDS
You dont need a cursor
It is as simple as
select col1+’,’+col2 from your_table
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
Give me the example for cursor
what is the use of cursor?
thank u
How can i insert a new column between twl column?
using SQL SERVER 2005
please tel me the query
thank u
How can i insert a new column between two columns?
using SQL SERVER 2005
@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.
very simple examples on
1.procedures
2.functions
3. cursors
4.packages
5.triggers
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)
HI
Thanks its GR8
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)
@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
very simple and good example for beginners…thanks a lot…..
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
Good example.. Thanks
very simple and good example for beginners…thanks a lot…..
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
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
Thanks a lot…This is the script I have always used for Cursors….