recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1

recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1

Record selection formula with Is Null

Hi all ,
Can any one correct my Record selection formula.
if IsNull ({?Start}) and IsNull ({?End})
then
{Query1.Completion Date} in CurrentDate-3 to CurrentDate-1
else
{Query1.Completion Date} in {?Start} to {?End}What if only one of the dates is set?

( IsNull({?Start}) and IsNull({?End})
and {Query1.Completion Date} in CurrentDate-3 to CurrentDate-1
)
or
( Not(IsNull({?Start}) and IsNull({?End}))
and {Query1.Completion Date} in {?Start} to {?End}
)

It's sometimes easier (if only to read) to create a formula for each parameter and use the formula in the record selection.
e.g. formula @.Start
if IsNull({?Start}) then CurrentDate-3 else {?Start}

e.g. record selection
{Query1.Completion Date} in {@.Start} to {@.End}

You might want to check how/whether this affects what's sent to the database (e.g. is the where clause set?)|||Hi Jagan,
Thanks for reply,
I have a report which will be schedule on every Monday at 1 am.
In this report they want Prev Friday to sunday data.So i am using date range
"{Query1.Completion Date} in CurrentDate-3 to CurrentDate-1"
this is the formula I have in my select expert.But some times user want to give his own parameters.So i want create a parameter that by default it has to bring data for prev Fri to Sun and if user select any date range then it has to bring data for that particular range dates..
I know this one we can create using stored procedures is there any other way with out stored procedure.