Cursor Based SQL?

How To Write Set-based SQL Instead Of Cursor
Guys
Here's the scenario

create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-12-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '30-11-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-10-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('98765' , '31-12-2005' , 2)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 1)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 0)
select * from data1


I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way
Any ideas

View Replies !

---

Cursor, Set Based Approach
I am replacing cursor logic in a SP to a setbased approach to scale better. My setbased approach seems to be better but it runs very fractionaly faster (execution time) than the cursor approach for a single run in test environment. I think resource cost wise, my set based approach should be better. Number of rows iterated thru this cursor is small (0-150). This particular SP is called over 2000 times in production everyday. Is it worth the trouble changing this if we get only marginally benefits, will my set-based approach work better on a server that has lot of activity (lot of connections etc). Our db server runs at about 75-85% cpu usage daily and this particular SP accounts to 13% CPU usage for 2000+ executions.

If the data set involved in cursors is small, is it worth the trouble changing them to set based approaches?
Am I doing right to change this SP to setbased approach.

View Replies !

---

Avoiding Cursor - Want Set Based Solution
Hi there!

Here is my situation:

table 'ReceiptHeader'

IDCustomerIDDateCreated
1225102/06/2002
1332102/09/2002
1444002/15/2002


table 'ReceiptDiscount'

IDDiscountIDReceiptHeaderIDAmount
111210.00
241250.00
311325.00

**a receipt can have multiple discounts**


Table 'ReceiptDetail'

ReceiptHeaderID LineItemIDTotal
121155.33
131145.33
141241.66

**for this example there is only one line item per receipt**



Without using a cursor, i would like to return a result set
like this one below using a set based solution...


ReceiptIDCustomerIDDiscountTotal
1225160.00155.33
1332125.00145.33
144400.00241.66

Thanks,
SF

View Replies !

---

How To Replace Cursor Based Statements With Set Ba
Hey All,
I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment.

Thanks in advance.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [SampleStoredProc]
@Var1varchar(20),
@Var2varchar(3),
@Var3 varchar(2) = 'Dummy'
As

declare @selectlist varchar(5000)
declare @tableBuild varchar(1000)
declare @FieldName varchar(50)
declare @FieldSelect varchar(500)
declare @FieldTitle varchar(50)
declare @TableName varchar(50)
declare @holdTable varchar(50)
declare @title varchar(50)
declare @holdTitle varchar(50)
declare @PageName varchar(50)
declare @sequence varchar(100)
declare @extraCriteria varchar(200)
declare @holdCriteria varchar(200)
declare @insertSQL varchar(5000)
declare @ConvertRoutine varchar(500)
declare @loopCtrl1 bit
declare @loopCtrl2 bit
declare @ConvertSQL varchar(5000)
declare @PrevValue varchar(50)
declare @NewValue varchar(50)
declare @ActionTxtvarchar(1)
declare @Descriptionvarchar(20)
declare @effDatevarchar(10)
declare @transEffDatevarchar(10)
declare @expDatevarchar(10)
declare @lastTransDatevarchar(10)
declare @policyStatusvarchar(2)
declare @reasAmendDescvarchar(50)
declare @policyNumbervarchar(20)
declare @riskStatevarchar(20)
declare @PriorPremmoney
declare @AmendPremmoney
declare @PremDiffmoney
declare mtcursor cursor for
select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1
where Column1 = @Var2
order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName

open mtcursor

fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine

set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
set @loopCtrl1 = 0
set @loopCtrl2 = 0

WHILE (@loopCtrl1 = 0)
begin
set @holdTable = @TableName
set @holdCriteria = @extraCriteria
set @holdTitle = @title

if @FieldSelect = ''
set @selectlist = @selectlist + ',' + @FieldName
else
set @selectlist = @selectlist + ',' + @FieldSelect

set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)'

fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
if @@fetch_status <> 0
set @loopCtrl2 = 1

if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1)
begin

set @tableBuild = @tableBuild + ')'
set @insertSQL = '
declare mtcursor2 cursor for
select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1
where TableName = ''' + @holdTable + '''
and ExtraCriteriaTxt = ''' + @holdCriteria + '''
and PageTitleTxt = ''' + @holdTitle + '''
and Column1 = ''' + @Var2 + '''
order by FieldDisplaySequenceNbr

declare @FieldName varchar(50)
declare @FieldTitle varchar(50)
declare @ExtraUpdateMatch varchar(500)
declare @PullUpdate bit
declare @PullAdd bit
declare @PullDelete bit
declare @PullAnyUpdate bit

open mtcursor2
fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate

WHILE (@@fetch_status = 0)
begin

if substring(@FieldTitle,1,1) = ''#''
set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1)
else
set @FieldTitle = '''''''' + @FieldTitle + ''''''''

if @PullAnyUpdate = 1
begin
exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''')
end
else
begin
if @PullUpdate = 1
exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null)
or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '')
end

if @PullAdd = 1
exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''')
if @PullDelete = 1
exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D''''
from #tempTable A where Val1 = ''''D'''''')
fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
end

close mtcursor2
deallocate mtcursor2'

exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL)

set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
end

if @loopCtrl2 = 1
set @loopCtrl1 = 1
end

close mtcursor
deallocate mtcursor

Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1
declare deletecursor cursor for
select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U'

open deletecursor

fetch next from deletecursor into @PageName

while @@fetch_status = 0
begin
if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0
DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U'
fetch next from deletecursor into @PageName
end

close deletecursor
deallocate deletecursor

declare convertcursor cursor for
select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a
inner join MyTable1 b
on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> ''
where a.Id = @Var1

open convertcursor

fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine

while @@fetch_status = 0
begin
set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)'
set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) '

set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) '
set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + ''''

if @ActionTxt = 'A'
set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + ''''
else
begin
set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + ''''
set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')'
end
if @ActionTxt = 'D'
set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + ''''
else
begin
set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + ''''
set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')'
end

set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted
where EntrySequenceNbr = ''' + @Sequence + ''''

exec (@ConvertSQL)

fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
end

close convertcursor
deallocate convertcursor

if @Var2 = 'PA '

--exec PAConfirmCovConversions @Var1 = @Var1
exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3


Create table #pageSeqTable (PageTitle varchar(50), PageSeq int)
insert into #pageSeqTable
select distinct PageTitleTxt, PageDisplaySequenceNbr
from MyTable1
where Column1 = @Var2

select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt
from MyTable2, #pageSeqTable b
where Id = @Var1 and PageNm = b.PageTitle
order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr

select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101),
@policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes,
@policyNumber = PolicyNumber,
@riskState = StateName,
@AmendPrem = convert(money,PremiumAmount)
from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C
where Id = @Var1
AND Val2 = (select max(Val2)
from SHPlanInfo
where Id = @Var1)
AND B.ReasonAmendedCd = A.ReasonAmendedCd
AND C.StateCode = A.RiskState
Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0'
Set @PremDiff = @AmendPrem - @PriorPrem


select EffectiveDate = @effDate

select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate

select AmendXPolStat = @policyStatus

select ReasonAmendedDes = @reasAmendDesc
select PolicyNumber = @policyNumber
select RiskState = @riskState
select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiffSelect ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1
delete from MyTable2 where Id = @Var1

return

View Replies !

---

Cursor Looping Versus Set-based Queries
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata is handled, I now have to make the same change in two (or more)places.* Different data from the same row needs to be inserted into multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.

View Replies !

---

Declare Cursor Based On Dynamic Query
Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

 

Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test

 

This code is not working.

 

please suggest

 

Nitin

View Replies !

---

Breakout Records Based On UNIT Total Without Cursor
I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge:

Ex:

Units REVCHG FIELD_A FIELD_B .....
3 3.00 ABCD EFGH

Needs to be converted to:

Units REVCHG FIELD_A FIELD_B .....
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH

The calc is obvious but how can I do this with a cursor but would like to do it without a cursor if possible? Anybody got an idea?

Thanks.

View Replies !

---

Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE               QTYIN  COST_IN_AMT        COST_OUT_AMT(MOVING AVERAGE)   
1          01/01/2007  PURCHASE            10                1000
2          01/01/2007  PURCHAES              5                1100
3          01/01/2007  SALES                    -5                                     *TobeCalculated
4          02/01/2007  Purchase                20                9000
5          02/01/2007  SALES                  -10                                     *TobeCalculated
5          02/01/2007  purchase                50                 8000 
6          03/01/2007  Sales                    -10                                      *TobeCalculate
7         01/01/2007   Purchase                20                12000
 
I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order  to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

View Replies !

---

How To Convert Cursor Based Stored Proc In Set Based Simple Stored Proc.
 

Hey All,
I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment.

Thanks in advance.

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [SampleStoredProc]
@Var1  varchar(20),
@Var2  varchar(3),
@Var3       varchar(2) = 'Dummy'           
As

declare @selectlist   varchar(5000)
declare @tableBuild   varchar(1000)
declare @FieldName   varchar(50)
declare @FieldSelect   varchar(500)
declare @FieldTitle   varchar(50)
declare @TableName   varchar(50)
declare @holdTable   varchar(50)
declare @title    varchar(50)
declare @holdTitle   varchar(50)
declare @PageName   varchar(50)
declare @sequence   varchar(100)
declare @extraCriteria   varchar(200)
declare @holdCriteria   varchar(200)
declare @insertSQL   varchar(5000)
declare @ConvertRoutine  varchar(500)
declare @loopCtrl1   bit
declare @loopCtrl2   bit
declare @ConvertSQL   varchar(5000)
declare @PrevValue   varchar(50)
declare @NewValue   varchar(50)
declare @ActionTxt  varchar(1)
declare @Description  varchar(20)
declare @effDate  varchar(10)
declare @transEffDate  varchar(10)
declare @expDate  varchar(10)
declare @lastTransDate  varchar(10)
declare @policyStatus  varchar(2)
declare @reasAmendDesc varchar(50)
declare @policyNumber  varchar(20)
declare @riskState  varchar(20)
declare @PriorPrem  money  
declare @AmendPrem  money  
declare @PremDiff  money  
declare mtcursor cursor for
 select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1
   where Column1 = @Var2
  order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName

 open mtcursor

 fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine

 set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
 set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
 set @loopCtrl1 = 0
 set @loopCtrl2 = 0

 WHILE (@loopCtrl1 = 0)
 begin
  set @holdTable = @TableName
  set @holdCriteria = @extraCriteria
  set @holdTitle = @title

  if @FieldSelect = ''
      set @selectlist = @selectlist + ',' + @FieldName
  else
      set @selectlist = @selectlist + ',' + @FieldSelect

  set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)'

  fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
  if @@fetch_status <> 0
      set @loopCtrl2 = 1

  if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1)
   begin

    set @tableBuild = @tableBuild + ')'
    set @insertSQL = '
 declare mtcursor2 cursor for
  select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1
  where TableName = ''' + @holdTable + '''
      and ExtraCriteriaTxt = ''' + @holdCriteria + '''
      and PageTitleTxt = ''' + @holdTitle + '''
      and Column1 = ''' + @Var2 + '''
      order by FieldDisplaySequenceNbr

 declare @FieldName varchar(50)
 declare @FieldTitle varchar(50)
 declare @ExtraUpdateMatch varchar(500)
 declare @PullUpdate bit
 declare @PullAdd bit
 declare @PullDelete bit
 declare @PullAnyUpdate bit

  open mtcursor2
  fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate

  WHILE (@@fetch_status = 0)
  begin

    if substring(@FieldTitle,1,1) = ''#''
        set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1)
                else
                    set @FieldTitle = '''''''' + @FieldTitle + ''''''''

   if @PullAnyUpdate = 1
                  begin
            exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
      from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
        where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''')
                   end
   else
      begin
  if @PullUpdate = 1
             exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
       from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
         where A.Val1 = ''''O''''  and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null)
          or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '')
                  end

   if @PullAdd = 1
  exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A''''       from #tempTable A where Val1 = ''''A'''''')
   if @PullDelete = 1
    exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D''''
      from #tempTable A where Val1 = ''''D'''''')
   fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
  end

  close mtcursor2
  deallocate mtcursor2'

    exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL)

    set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
    set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
   end

    if @loopCtrl2 = 1
       set @loopCtrl1 = 1
  end

  close mtcursor
  deallocate mtcursor

  Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1 
  declare deletecursor cursor for
 select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U'

  open deletecursor

  fetch next from deletecursor into @PageName

  while @@fetch_status = 0
     begin
  if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0
       DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U'
  fetch next from deletecursor into @PageName
     end

  close deletecursor
  deallocate deletecursor

  declare convertcursor cursor for
 select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a
 inner join MyTable1 b
  on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> ''
 where a.Id = @Var1

  open convertcursor

  fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine

  while @@fetch_status = 0
     begin
 set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)'
 set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) '
 -- start 33385
 set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) '
 set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + ''''
 --end 33385
 if @ActionTxt = 'A'
  set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + ''''
 else
   begin
  set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + ''''
  set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')' 
   end
 if @ActionTxt = 'D'
  set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + ''''
 else
   begin
  set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + ''''
  set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')'
   end

 set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted
  where EntrySequenceNbr = ''' + @Sequence + ''''

 exec (@ConvertSQL)

 fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
    end

  close convertcursor
  deallocate convertcursor

/* LOB-specific data conversions */
  if @Var2 = 'PA '
  --exec PAConfirmCovConversions @Var1 = @Var1
 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3
 -- End issue 33385
 
  Create table #pageSeqTable (PageTitle varchar(50), PageSeq int)
  insert into #pageSeqTable
 select distinct PageTitleTxt, PageDisplaySequenceNbr
 from MyTable1
 where Column1 = @Var2

  select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt
  from    MyTable2, #pageSeqTable b
  where Id = @Var1 and PageNm = b.PageTitle
  order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr

  select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101),
 @policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes,
 @policyNumber = PolicyNumber,
 @riskState = StateName,
 @AmendPrem = convert(money,PremiumAmount)   /* Case 33385 */
  from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C
  where Id = @Var1
       AND Val2 = (select max(Val2)
      from SHPlanInfo
      where Id = @Var1)
       AND B.ReasonAmendedCd = A.ReasonAmendedCd
       AND C.StateCode = A.RiskState
Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0' 
Set @PremDiff = @AmendPrem - @PriorPrem            


  select EffectiveDate = @effDate

  select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate

  select AmendXPolStat = @policyStatus

  select ReasonAmendedDes = @reasAmendDesc   

  select PolicyNumber = @policyNumber     

  select RiskState = @riskState      

  select PriorPremium =  @PriorPrem             
  select AmendPremium = @AmendPrem             
  select PremiumDifference = @PremDiff            
Select ClientNumber from SHClient with (nolock) where Id=@Var1 and  ApplicantRecordInd = 1     
delete from MyTable2 where Id = @Var1

return

 

View Replies !

---

Help Cursor Based Stored Procedure Is Getting Slower And Slower!
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO

View Replies !

---

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View Replies !

---

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !

---

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
 

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.
 
here is the stored procedure:
 

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval

 

GO

 
example useage:
 

EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies !

---

Join Cursor With Table Outside Of Cursor
part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View Replies !

---

ODBC-based To OLE DB-based Data Transfer
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!

View Replies !

---

Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View Replies !

---

Client Side Cursor Vs Sever Side Cursor?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View Replies !

---

Report Based On Filtered Query OR Based On Custom Query
adp on SLQ7

What would be wise to do.

I'm creating a report based on two inner joined tables and i've got a total sum field for each line in the report. (Price * Ordered)

This results in the following sql statement:

Code:


sqlStr = "SELECT *, [Table1].Ordered * [Table1].Price AS LineTotal FROM [Table1] INNER JOIN [Table2] ON [Table1].RecieptNumber = [Table2].Number WHERE ([Table1].RecieptNumber = " & varNumber & ")"



I think I have a few options now.

1. Leave the varNumber out of the query, and save it as a regular query. And now filter the report on varNumber.

2. Write the constructed query with createquerydef to eg. "TmpQueryForReport" And set the reports recordsource fixed to TmpQueryForReport.

3. Set the recordsource of the report to constructed sqlStr on Report_open()

---------------------

Option 1, I got this working but when the database grows (and it will) this might get awfully slow.

Option 2
I had this working before i switched to using MSSQL server 7.00
After the switch i thought, this might be a problem with giving db access rights cause the users might need write rights to the db.

Option 3. Fast and easy???

Option 4 Stored procedures???

View Replies !

---

Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)
Hi~, I have 3 questions about memory based bulk copy.

1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
    pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}

2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)

3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);

-------------------------------------------------------
My solution is like this. Is it correct?

// CoCreateInstance(...);
// Data source
// Create session

 m_TableID.uName.pwszName = m_wszTableName;
 m_TableID.eKind = DBKIND_NAME;

 DBPROP rgProps[1];
 DBPROPSET PropSet[1];

 rgProps[0].dwOptions  = DBPROPOPTIONS_REQUIRED;
 rgProps[0].colid   = DB_NULLID;
 rgProps[0].vValue.vt  = VT_BSTR;
 rgProps[0].dwPropertyID  = SSPROP_FASTLOADOPTIONS;
 rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";

 PropSet[0].rgProperties  = rgProps;
 PropSet[0].cProperties  = 1;
 PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;

 if(m_pIOpenRowset)
 {
  if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
  {
   return FALSE;
  }
 }
 else
 {
  return FALSE;
 }

View Replies !

---

Column Based On Other Column (short Name Based On Name), When To Do The Transformation?
Hi!
 
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:


Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName

Create a derived column in the SSIS flow which does the same thing

Create the ProductShortName column as a computed column which uses substring on ProductName

Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated

Create a named calculation in the table in the Analysis Services project's data source view

Create a named query in the Analysis Services project's data source view

I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?
 
Thanks!
 
 

View Replies !

---

How Cursor Used In Asp.net
hii have creted cursor but i want to use in my asp.net programming when some insert or delete command is work that time i want to excute my cursor how can i do that using asp.net with c#  waiting for replaythanks 

View Replies !

---

Is This Possible Without A Cursor?
 I have something like
 update table
set field = ...
where field = ...
 and for each entry that was effected by this query I want to insert an entry into another table.
I have always done this with cursors is there a more effecient way?  For some reason cursors run a lot slower on my sql2005 server than the sql2000 server...

View Replies !

---

Use Of Cursor
 
 I need some help with the concept of a Cursor, as I see it being used in a stored procedure I need to maintain.
Here is some code from the stored proc. Can someone tell me what is going on here. I haveleft out some of the sql, but have isolated the Cursor stuff.
Open MarketCursor  -- How is MarketCursor loaded with data ?
FETCH NEXT
FROM MarketCursorINTO ItemID, @Item, @Reguest
WHILE @@FETCH_STATUS = 0BEGIN
 
DEALLOCATE MarketCursor
 

View Replies !

---

Cursor For
Hi,
Declare wh_ctry_id CURSOR FOR 
 
Is "cursor for" is a function or datatype or what is this?
Regards
Abdul

View Replies !

---

Need Help With A Cursor
I hope this is the appropriate forum for this question, if not then I apologize.
I've got a SQL Server 2000 stored procedure that returns data to be used in a crystal report in Visual Studio 2005.  Most of the stored procedure works well, but there is a point where I need to calculate an average number of days been a group of date pairs. 
I'm not familiar with cursors, but I think that I will need to use one to achieve the result I am looking for so I came up with the code below which is a snippet from my stored procedure.  In this part of the code, the sp looks at the temporary table #lmreport (which holds all of the data that is returned at the end to crystal) and for every row in the table where the terrid is 'T' (the territory is domestic), it selects all of those territories from the territory table and loops through them to determine the date averages (by calling a nested stored procedure, also included below) for each territory and then updates #lmreport with that data.
When I try to run the stored procedure, I get "The column prefix '#lmreport' does not match with a table name or alias name used in the query." on the line indicated. 
Does anyone have any idea what might be wrong or if this will even work the way I need it to?
Thank you in advance.
 

View Replies !

---

Need Cursor Help
Hello, I'm trying to construct a cursor that will sequentually increment a number and then update a column with the incremented number. My propblem is that all the rows in the table are being updated with the base number +1. So all rows are updated with 278301. BUT, what I really want is for only the items with adrscode of 'bill to' to be given an incremented number.
For example, if there are only five rows of 100 with an adrscode = 'bill to' then only five rows will be updated and the value of the custnmbr should be, 278301, 278302, 278303 .....
I could really use some help with this cursor:
Declare @CustomerName as char (60),     @seqno as int,     @BaseSeqno as intset @Baseseqno = 278300
declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbropen cfetch next from c into @CustomerNamewhile @@fetch_status=0begin set @seqno = @BaseSeqno + 1
update NXOFcustomers set custnmbr = @seqnoWhere custnmbr = @CustomerName                    fetch next from c into @CustomerNameend close cdeallocate c
 

View Replies !

---

Cursor And UDf
Hello:
I am trying to define a cursor as follows:
 DECLARE   EmployeeList CURSOR FOR   dbo.GetRecord(@EmployeeID,@CurrentDate)Can't I use a UDF in the CURSOR FOR ?Help please.thank you.

View Replies !

---

Is Cursor Best Way To Go?
I need to get two values from a complex SQL statement which returns a singlerecord and use those two values to update a single record in a table. Inorder to assign those two values to variables and then use those variablesin the UPDATE statement, I created a cursor and used Fetch Next.... Into.This way, I only have to call the complex SQL once instead of twice.This seems like the best way to go. However, I've always used cursors forscrolling through resultsets. In this case, though, there is just a singlerecord being returned, and the cursor doesn't scroll.Is that the most efficient way to go, or is there a better way to be able touse both values from the SQL statement without having to call it twice?Thanks.

View Replies !

---

To Use A Cursor Or To Not Use A Cursor
I need to loop through a set of records to build a string. I can dothis without using a cursor by inserting the records into a temporarytable with an identity column. Count the number of records in thetemporary table and loop though the table selecting the values andbuilding the string where the identity column = the loop number.Is this more or less efficient than just using a cursor? If so why isit more or less efficient?Please explain in detailThank You,Jim Lewis

View Replies !

---

Cursor Help PLEASE!
Yes, I know that cursors are gauche, but I can't see a solution usingqueries and I'm pretty adept at them. This will be running once a dayin the wee small hours when minimal server activity will be takingplace, it will be handling 700-1000ish records. The box is a P4/SQL2000 with lots of ram and multiple CPUs.The code for creating and populating the table in question follows thecursor code.The application is for medical transport billing. We take people tothe doctor and home again and the health plan pays us. A one-waytrip, home -> doctor, is a single ‘line item'. A round trip, home ->doctor -> home is rolled up, the charge summed, and billed as a singleline item. A three-leg, home -> doctor -> pharmacy -> home, is billedas three line items.This code is just for identifying the data, once this works correctlyI'll add the code in for doing the rollup and I'm confident I canhandle that.If I ruled the world, every leg of every trip would be a single lineitem and I wouldn't have to deal with this rollup BS, but I don't makethe policy, I just have to code it. <g>A trip is a round trip if the date, account number, and passenger nameis the same and the origin street of record X equals the destinationstreet of record X+1. The problem is that X+1 could be the start of around trip and X+2 could be the completing leg of a round trip. Soyou could have a scenario of hospital -> home -> doctor -> home inwhich X is one-way and X+1 & X+2 form a round trip.BLERG! Any help is greatly appreciated, it has me stumped./*select trip_id, acct_number, passenger, street, dest_street,flat_rate, screated, smeteroff, remark1, remark2from zvoucherstodbf order by cast(screated as smalldatetime),acct_number, passenger, smeteroff*/declare @roundtrip integerdeclare @cmsg char(200)declare @Trip_ID char(11)declare @acct_number char(11)declare @passenger char(12)declare @created char(22)declare @meter_off char(22)declare @street char(32)declare @dest_street char(32)declare @remark1 char(32)declare @remark2 char(32)declare @flat_rate smallintdeclare @prev_cmsg char(200)declare @prev_Trip_ID char(11)declare @prev_acct_number char(11)declare @prev_passenger char(12)declare @prev_created char(22)declare @prev_meter_off char(22)declare @prev_street char(32)declare @prev_dest_street char(32)declare @prev_remark1 char(32)declare @prev_remark2 char(32)declare @prev_flat_rate smallintdeclare cVoucher cursor scroll forselect trip_id, acct_number, passenger, street, dest_street, screated,smeteroff, remark1, remark2, flat_ratefrom zVouchersToDBF--where screated = '12/03/03'order by screated, acct_number, passenger, smeteroffopen cVoucher--Load the @Prev_ (previous record) variables with the first recordfetch from cVoucher into @prev_trip_id, @prev_acct_number,@prev_passenger,@prev_street, @prev_dest_street, @prev_created, @prev_meter_off,@prev_remark1, @prev_remark2, @prev_flat_rate--Initialize current variablesselect @trip_id = @prev_trip_idselect @acct_number = @prev_acct_numberselect @passenger = @prev_passengerselect @street = @prev_streetselect @dest_street = @prev_dest_streetselect @created = @prev_createdselect @meter_off = @prev_meter_offselect @remark1 = @prev_remark1select @remark2 = @prev_remark2while @@fetch_status = 0--not EOFbeginselect @roundtrip = 0if (@prev_created = @created) and (@prev_acct_number = @acct_number)and (@prev_passenger = @passenger)and (@prev_street =@dest_street)-- MATCH! Apparent round tripbeginselect @roundtrip = 1select @prev_trip_id = @trip_idselect @prev_acct_number = @acct_numberselect @prev_passenger = @passengerselect @prev_street = @streetselect @prev_dest_street = @dest_streetselect @prev_created = @createdselect @prev_meter_off = @meter_offselect @prev_remark1 = @remark1select @prev_remark2 = @remark2endif (@roundtrip = 0)beginfetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateif (@prev_created = @created) and (@prev_acct_number = @acct_number)and (@prev_passenger = @passenger)and (@prev_street =@dest_street)-- MATCH! Apparent round tripselect @roundtrip = 1else--definitely one-way tripselect @roundtrip = 0fetch prior from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendif (@roundtrip = 1)beginselect @cMsg = 'Start: ' + rtrim(@prev_created) + ' ' +rtrim(@prev_trip_id) + ': ' + @prev_acct_number + ' ' +rtrim(@prev_meter_off) + ' ' + @prev_passenger + ' ' +rtrim(@prev_street) + ' to ' + @prev_dest_street + ' ' + @prev_remark1+ ' ' + @prev_remark2print @cMsgselect @cMsg = 'End: ' + rtrim(@created) + ' ' + rtrim(@trip_id) +': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + ' '+ rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +@remark2print @cMsgprint ''endelsebeginselect @cMsg = 'One-Way: ' + rtrim(@created) + ' ' + rtrim(@trip_id)+ ': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + '' + rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +@remark2print @cMsgprint ''endselect @prev_trip_id = @trip_idselect @prev_acct_number = @acct_numberselect @prev_passenger = @passengerselect @prev_street = @streetselect @prev_dest_street = @dest_streetselect @prev_created = @createdselect @prev_meter_off = @meter_offselect @prev_remark1 = @remark1select @prev_remark2 = @remark2fetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendclose cVoucher--/*--This code works for displaying the data set as a whole-- so you can visually identify what constitutes a round trip.open cVoucherfetch from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_ratewhile @@fetch_status = 0beginselect @cMsg = rtrim(@created) + ' ' + rtrim(@trip_id) + ': ' +@acct_number + ' ' + @passenger + ' ' + @street + ' ' + @dest_street +' ' + @meter_off + ' ' + @remark1 + ' ' + @remark2print @cMsgfetch next from cVoucher into @trip_id, @acct_number, @passenger,@street, @dest_street, @created, @meter_off, @remark1, @remark2,@flat_rateendclose cVoucher--*/deallocate cVoucherCREATE TABLE [zVouchersToDBF] ([house] [char] (5) NULL ,[street] [char] (32) NULL ,[district] [char] (8) NULL ,[passenger] [char] (12) NULL ,[remark1] [char] (32) NULL ,[remark2] [char] (32) NULL ,[dest_house] [char] (5) NULL ,[dest_street] [char] (32) NULL ,[dest_district] [char] (13) NULL ,[acct_number] [char] (11) NULL ,[sub_acct_number] [char] (15) NULL ,[flat_rate] [money] NULL ,[car] [char] (3) NULL ,[driver_id] [char] (9) NULL ,[meter_on] [char] (5) NULL ,[meter_off] [char] (5) NULL ,[fare] [char] (9) NULL ,[cancelled] [char] (21) NULL ,[no_trip] [char] (7) NULL ,[no_trip_reason] [int] NULL ,[auth_number] [char] (32) NULL ,[auth_name] [char] (32) NULL ,[trip_id] [char] (7) NULL ,[created] [char] (8) NULL ,[patient_birthday] [char] (16) NULL ,[waittime] [char] (3) NULL ,[sNoTrip] [char] (22) NULL ,[sMeterOn] [char] (22) NULL ,[sMeterOff] [char] (22) NULL ,[sCancelled] [char] (22) NULL ,[sCreated] [char] (22) NULL ,[sPatientBirthday] [char] (22) NULL ,[SystemRate] [money] NULL) ON [PRIMARY]GOInsert zvoucherstodbfValues (null,'100 E 1st',null,'A','X','Y',null,'200 W2nd',null,'1000',null,5.00,null,null,null,'13:20', '5',null,null,null,null,null,'1',null,null,null,nu ll,null,'13:20',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'200 W 2nd',null,'A','X','Y',null,'100 E1st',null,'1000',null,5.00,null,null,null,'14:20', '5',null,null,null,null,null,'2',null,null,null,nu ll,null,'14:20',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'101 E 101',null,'B','X','Y',null,'202 W202',null,'2000',null,10.00,null,null,null,'13:50' ,'10',null,null,null,null,null,'3',null,null,null, null,null,'13:50',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'123 N 456',null,'C','X','Y',null,'234 S321',null,'2000',null,5.00,null,null,null,'13:51', '5',null,null,null,null,null,'4',null,null,null,nu ll,null,'13:51',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'999 N 666',null,'D','X','Y',null,'666 S999',null,'1000',null,7.50,null,null,null,'14:00', '7.5',null,null,null,null,null,'5',null,null,null, null,null,'14:00',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'666 S 999',null,'D','X','Y',null,'999 N666',null,'1000',null,8.00,null,null,null,'14:30', '8',null,null,null,null,null,'6',null,null,null,nu ll,null,'14:30',null,'12/8/2003',null,null)Insert zvoucherstodbfValues (null,'123 n 456',null,'E','X','Y',null,'456 s789',null,'3000',null,5.00,null,null,null,'14:30', '5',null,null,null,null,null,'7',null,null,null,nu ll,null,'14:30',null,'12/8/2003',null,null)

View Replies !

---

What Is A Cursor?
I had a friend write a stored procedure to perform a function for oneof my clients. What he wrote doesn't fully do what I need, and I hopeto finish it myself. I have programming sense, but not so much withSQL.I'm trying to figure out the code, and he has used something called a"cursor." I'm not sure whether this is an SQL construct or a structurethat he has just labeled "cursor." My guess is that it is an SQLconstruct. Can anyone give me a quick run down of how this works?sincerely,Tyler H.-----------------------------------------------<a href="http://www.seearoomhawaii.com/bed-breakfasts/">bed &breakfasts in Hawaii</a>

View Replies !

---

It Is Possible With Cursor?
I need to fill a cursor with 3 columns.
A want to use a Select sprocs (for re-use de code), but this sproc return 15 columns and the 3 a need was not the 3 frist. :confused:

Do I need to map the 15 columns with 15 variables locally? Or they have a way easier?


Thanks

View Replies !

---

Can I Use SQL Instead Of Cursor?
Hi,

I'm currently converting a VB function to SQL-Server. The function uses a cursor to find the "terms of delivery" (TOD) with the highest priority.

I have a table with articlenumber, tod (and lots of other columns that doesn't matter now)

ABC123 , AFG
ABC123 , AFG
ABC123 , BGH
ABC123 , BGH
ABC123 , CDD

"CDD" has the highest priority and therefore ALL with the same articlenumber should use that tod.

The existing function uses a cursor and loops through a recordset and updates every row with the same articlenumber as the current row with the tod with the highest priority (of the ones read) with the same articlenumber.

One update per row takes "forever" to run...

I figured it would be possible to select the tod with the highest priority for one articlenumber into a temp table and then do ONE update to set the tod on all rows...

View Replies !

---

Can't Use Cursor With SP
Hi

I have a SP and in it I call another SP which returns one row in one column, I need to concatenate the value (varchar) to the query in the first SP.
I tried to use cursor with FAST_FORWARD to fetch the result and concatenate it, but I get an error, here is what I tried:

DECLARE Cur CURSOR FAST_FORWARD
FOR SP_Something @SomeValue

So is it possible to use cursor on SP ? And if it's possible so how ???

Thanks,

Inon.

View Replies !

---

Get Value From CURSOR
I'm writing a stored procedure that involves looping through a recordset and using the values from the recordset as parameters for a second stored procedure. Here's what I've got so far...

My question is, how do I get the value out of the Cursor? There's only one field.

Declare @Day as int
Declare @Plant as varchar(30)

SET NOCOUNT ON

CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime)

Declare Facility_Cursor CURSOR
For Select Distinct(Facility) From ProductionHistory
OPEN Facility_CURSOR
Declare @Facility_Cursor as sysname

FETCH NEXT From Facility_CURSOR into @Facility_Cursor

WHILE @@FETCHSTATUS = 1

--YESTERDAY
Set @Day = -2

Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @Day, @Facility, 'NAP'

--TODAY
SET @Day = -1
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @Day, @Facility, 'NAP'

FETCH NEXT FROM Facility_CURSOR into @Facility_Cursor

CLOSE Facility_Cursor
DEALLOCATE Facility_CURSOR

SET NOCOUNT OFF

Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESC

View Replies !

---

How To Do This Without Using A Cursor?
I am in the last stages of designing a forecasting "engine" for my company,
and I'm stuck on something that seems simple in comparison to everything
else I've done so far.

I have product ABC, and it's total sales forecast is 15 units.
I split the forecast into 2 different locations, based on an established percentage. In this case, I'll say 67% in location 'OH', and 33% in location 'AL' That's 10 units 'OH' and 5 units 'AL'. Then I get my actual orders by location, and compare them to the forecast.

If the orders exceed the forecast, I'll use orders, otherwise, I use forecast. Whenever I do that, I need to reduce the forecast for the other location, in order to keep the total forecast of 15 whole. (It is not possible for total orders to exceed total forecast, I've already dealt with that.)


CREATE PROCEDURE tempSelect
AS

CREATE TABLE #tmpTest (
parent char(2),
proj_ship real,
open_ord real,
)

insert into #tmpTest (parent, proj_ship, open_ord)
select 'OH', 10, 4

insert into #tmpTest (parent, proj_ship, open_ord)
select 'AL', 5, 7

SELECT PARENT, 'UNITS' = ???
FROM #TMPTEST

DROP TABLE #TMPTEST
GO


I need help with '???' in the query.

The result set I am looking for is:
OH 8
AL 7

View Replies !

---

Trying To NOT Use A Cursor...
I need to write a sproc to supply records for a report. The boss has asked
"Of all the tons on order right now, how much is already in inventory, and how much needs to be produced." And "Apply the same logic to just the orders that came in yesterday." It would have been easy, if he hadn't asked for the second part, because now I have to look at each product on each order, rather than comparing total orders for a product to total available inventory.

Here's some sample data for what I need to do:


CREATE TABLE ORDER_ITEM (
ORDER_NUM VARCHAR(10),
SHIP_DATE SMALLDATETIME,
PRODUCT VARCHAR(10),
ORD_TONS REAL)

INSERT INTO ORDER_ITEM (ORDER_NUM, SHIP_DATE, PRODUCT, ORD_TONS)
SELECT '001', '3/1/2006', 'ABC', 4 UNION ALL
SELECT '002', '3/4/2006', 'ABC', 2 UNION ALL
SELECT '002', '3/4/2006', 'DEF', 6 UNION ALL
SELECT '003', '3/7/2006', 'DEF', 8

CREATE TABLE PROD_INVENTORY (
PRODUCT VARCHAR(10),
INV_TONS REAL)

INSERT INTO PROD_INVENTORY (PRODUCT, INV_TONS)
SELECT 'ABC', 5 UNION ALL
SELECT 'DEF', 13



The final recordset needs to be something like:

PRODUCT ORDER_NUM ORD_TONS SFI SFP END_INV
ABC 001 4 4 0 1
ABC 002 2 1 1 0
DEF 002 6 6 0 7
DEF 003 8 7 1 0


SFI = Sales from Inventory
SFP = Sales from production

I need a little help in how to do a running inventory balance (END_INV)
for each item. Once I have that, then I can calculate SFI and SFP.
I could figure out how to do it with a cursor, but it
would probably be pretty slow. I'll have about 10,000 records to sort thru,
and of course there will be more columns than what I show here.

Any ideas would be appreciated.

View Replies !

---

Cursor
hey all,
i am relatively new to cursors and have created a SP that uses a cursor to populate a table. here is the code

CREATE PROCEDURE sppa_invvoid
(
@invnoint
)

AS

DECLARE @glTranKey int
DECLARE @AcctRefKey int
DECLARE @BatchKey int
DECLARE @CreateDate datetime
DECLARE @CreateType smallint
DECLARE @CurrExchRate float
DECLARE @CurrID varchar (3)
DECLARE @ExtCmnt varchar (255)
DECLARE @FiscPer smallint
DECLARE @FiscYear varchar (5)
DECLARE @GLAcctKey int
DECLARE @JrnlKey int
DECLARE @JrnlNo int
DECLARE @PostAmt decimal(15, 3)
DECLARE @PostAmtHC decimal(15, 3)
DECLARE @PostCmnt varchar (50)
DECLARE @PostDate datetime
DECLARE @PostQty decimal(16, 8)
DECLARE @SourceModuleNo smallint
DECLARE @TranDate datetime
DECLARE @TranKey int
DECLARE @TranNo varchar (10)
DECLARE @TranType int
DECLARE @Companyid varchar(3)
DECLARE @Batchtype int
DECLARE @Userid varchar (30)
DECLARE @Moduleno int
DECLARE @NextBatchNo int
DECLARE @_oRetVal int
DECLARE @iTableNamevarchar(50)
DECLARE @iCommitFlag int
DECLARE @NextJrnlNo int


Set @CompanyID= 'EMA'
Set @JrnlNo = 139
Set @iCommitFlag = 1
Set @JrnlKey = 193
Set @iTableName='tgltransaction'
Set @batchtype = 501
Set @moduleNo = 5
Set @Userid = 'Admin'



EXECUTE spGetNextBatchNo @CompanyID, @BatchType, @UserId, @ModuleNo, @BatchKey OUTPUT, @NextBatchNo OUTPUT, @_oRetVal OUTPUT

Execute spglGiveNextJrnlNo @CompanyID, @JrnlKey, @iCommitFlag, @JrnlNo, @NextJrnlNo OUTPUT




DECLARE cursor_tran CURSOR FOR

select glTranKey, AcctRefKey,CreateDate,CreateType,CurrExchRate,Curr ID,ExtCmnt,FiscPer,FiscYear,GLAcctKey,JrnlKey,Jrnl No,PostAmt,PostAmtHC,PostCmnt,PostDate,PostQty,Sou rceModuleNo,TranDate,TranKey,TranNo,TranType
from tgltransaction where tranno = @invno

OPEN cursor_tran

FETCH NEXT FROM cursor_tran INTO

@glTranKey,
@AcctRefKey,
@CreateDate,
@CreateType,
@CurrExchRate,
@CurrID,
@ExtCmnt,
@FiscPer,
@FiscYear,
@GLAcctKey,
@JrnlKey,
@JrnlNo,
@PostAmt,
@PostAmtHC,
@PostCmnt,
@PostDate,
@PostQty,
@SourceModuleNo,
@TranDate,
@TranKey,
@TranNo,
@TranType





WHILE (@@FETCH_STATUS <> -1)

BEGIN

Execute spgetnextSurrogateKey @iTablename , @glTranKey OUTPUT
/*Execute aaaa*/





INSERT INTO tgltransaction
(glTranKey,
AcctRefKey,
BatchKey,
CreateDate,
CreateType,
CurrExchRate,
CurrID,
ExtCmnt,
FiscPer,
FiscYear,
GLAcctKey,
JrnlKey,
JrnlNo,
PostAmt,
PostAmtHC,
PostCmnt,
PostDate,
PostQty,
SourceModuleNo,
TranDate,
TranKey,
TranNo,
TranType)

Values

(@glTrankey,
@AcctRefKey,
@BatchKey,
@CreateDate,
@CreateType,
@CurrExchRate,
@CurrID,
@ExtCmnt,
@FiscPer,
@FiscYear,
@GLAcctKey,
@JrnlKey,
@JrnlNo,
@PostAmt,
@PostAmtHC,
@PostCmnt,
@PostDate,
@PostQty,
@SourceModuleNo,
@TranDate,
@TranKey,
@TranNo,
@TranType)




FETCH NEXT FROM cursor_tran INTO

@glTranKey,
@AcctRefKey,
@CreateDate,
@CreateType,
@CurrExchRate,
@CurrID,
@ExtCmnt,
@FiscPer,
@FiscYear,
@GLAcctKey,
@JrnlKey,
@JrnlNo,
@PostAmt,
@PostAmtHC,
@PostCmnt,
@PostDate,
@PostQty,
@SourceModuleNo,
@TranDate,
@TranKey,
@TranNo,
@TranType



END
CLOSE cursor_tran
DEALLOCATE cursor_tran
GO


the issue that i am having is after i do the insert, the cursor picks up on the inserted row and it ultimately becomes an infinate loop. what can i do to prevent it from picking up the newly inserted rows. thanks alot

tibor

View Replies !

---

SQL Cursor?
Hi all,

I have two tables with

IMPORT_TABLE (Imported from MS Excel) <-- Source table
Name
Address
City
State
Zip

CONTACT_TABLE: <-- Target Table
Full_Name
Address_Line1
City
State
Zip


I have to match the name from the Import table with the full_name in Contact table, if it matches, i have to update address / city / state / zip information in the Contact table if and only if corresponding columns in the target table (Contact table) is empty. If the name does not match, i have to insert a new record in contact table.

Should i use SQL cursor? If so could any one provide me with a sample solution?

Thanks in Advance,
Newbie

---------------------------------------------
SQL 2000 on Windows 2000 Server

View Replies !

---

Using Cursor
Using SQL2005. I need to create a cursor to update a field in a table with current date by Invoice Number. I get an endless loop and record is not updated. Where did I go wrong. David

DECLARE

@GRGR_ID CHAR(10),
@SBSB_ID CHAR(10),
@INVOICE_DUE_DATE DATETIME,
@INVOICE_TOTAL_AMOUNT_DUE MONEY,
@INVOICE_NUMBER CHAR(12),
@PASS1_GENERATION_DATE DATETIME,
@PASS1_TOTAL_AMOUNT_DUE MONEY,
@PASS1_END_OF_GRACE DATETIME



DECLARE DEQ CURSOR FOR
SELECT
GRGR_ID,
SBSB_ID,
INVOICE_DUE_DATE,
INVOICE_TOTAL_AMOUNT_DUE,
INVOICE_NUMBER,
PASS1_GENERATION_DATE,
PASS1_TOTAL_AMOUNT_DUE,
PASS1_END_OF_GRACE

FROM dbo.RPT_DELINQUENCY_TEST WHERE INVOICE_NUMBER = '080710019183'

OPEN DEQ

FETCH DEQ INTO
@GRGR_ID,
@SBSB_ID,
@INVOICE_DUE_DATE,
@INVOICE_TOTAL_AMOUNT_DUE,
@INVOICE_NUMBER,
@PASS1_GENERATION_DATE,
@PASS1_TOTAL_AMOUNT_DUE,
@PASS1_END_OF_GRACE

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE dbo.RPT_DELINQUENCY_TEST SET @PASS1_GENERATION_DATE = GETDATE()
WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID AND INVOICE_NUMBER=@INVOICE_NUMBER

END

CLOSE DEQ
DEALLOCATE DEQ

Thank for your help.

View Replies !

---

Msg 512 On Cursor
Everytime I execute this procedure it gives a msg 512 subquery returns more then one value. Can someone just explain why or what I'm doing wrong? Thanks in advance

Here is my code:

create procedure insert_sku_info
AS
Declare
@method varchar(40),
@sku int,
@location varchar(40)

Declare insert_cur Cursor For
select a.method, s.sku, s.location
from archive_sku a INNER JOIN sku s on a.sku = s.sku
and a.location = s.location

Open insert_cur

Fetch Next from insert_cur
Into @method, @sku, @location

While (@@Fetch_Status = 0)
Begin

print @method + @sku + @location

Update sku
set method = @method
where sku = @sku
and location = @location

Fetch Next from insert_cur
Into @method, @sku, @location

End
close insert_cur
deallocate insert_cur

View Replies !

---

Cursor Help Please
Hi all!

I just need some help with cursors, a topic I don't profess to be an expert in.

I've got two tables with a 1-many relationship between them. Let's say they're "tblCustomers" and "tblOrders".

tblCustomers data:

CustomerID Name
1 Fred
2 Charlie
3 Lucy


tblOrders data:

OrderID CustomerId Qty
1 1 10
2 1 5
3 1 20
4 2 8
5 3 20
6 3 6


I need to return a result set that puts all the "many" records into a single row, like:

Name Qty

Fred 10, 15, 20
Charlie 8
Lucy 20, 6

THANKS IN ADVANCE!!!!!!!!!!

View Replies !

---

Do I Really Need A Cursor?
I've built an application to import transactions into the database. Bad transactions go in a separate table and dupe transactions get updated. Currently, it takes about 2 hours to import ~40K records using the code below. Obviously I'd like this to run as fast as possible and since cursors are a real drag I was wondering if there was a more efficient way to accomplish this.

DECLARE
@contact_id int,
@product_code char(9),
@status_date datetime,
@business_code char(4),
@expire_date datetime,
@prod_status char(4),
@transaction_id int,
@emailAddress varchar(50),
@journal_id int



BEGIN TRAN
DECLARE transaction_import_cursor CURSOR
FOR SELECT transaction_id, product_code, emailAddress, status_date, business_code, expire_date, prod_status from transactions_batch_tmp
OPEN transaction_import_cursor
FETCH NEXT FROM transaction_import_cursor INTO @transaction_id, @product_code, @emailAddress, @status_date, @business_code, @expire_date, @prod_status
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT top 1 contacts.contact_id AS contact_id, transactions_batch_tmp.status_date AS status_date, transactions_batch_tmp.product_code AS product_code,
transactions_batch_tmp.business_code AS business_code, transactions_batch_tmp.expire_date AS expire_date,
transactions_batch_tmp.prod_status AS product_status
FROM transactions_batch_tmp INNER JOIN
journal INNER JOIN
contacts ON journal.contact_id = contacts.contact_id ON transactions_batch_tmp.emailAddress = contacts.emailAddress AND
transactions_batch_tmp.product_code = journal.product_code INNER JOIN
products ON transactions_batch_tmp.product_code = products.product_code
WHERE rtrim(ltrim(contacts.emailAddress)) = @emailAddress AND journal.product_code = @product_code
ORDER BY transactions_batch_tmp.status_date desc
IF @@ROWCOUNT = 0
BEGIN
print 'NEW transaction! ' + @product_code + @emailAddress
insert into journal (contact_id, product_code, status_date, business_code, expire_date, entryTypeID, product_status, date_entered)
SELECT distinct rtrim(ltrim(contacts.contact_id)) as cid, rtrim(ltrim(products.product_code)), transactions_batch_tmp.status_date,
rtrim(ltrim(transactions_batch_tmp.business_code)) , transactions_batch_tmp.expire_date, 21, rtrim(ltrim(transactions_batch_tmp.prod_status)), getDate()
FROM contacts INNER JOIN (transactions_batch_tmp INNER JOIN products ON transactions_batch_tmp.product_code=products.produ ct_code) ON contacts.emailAddress=transactions_batch_tmp.email Address
WHERE transactions_batch_tmp.transaction_id=@transaction _id
END
ELSE
BEGIN
--print 'UPDATE transaction! ' + @product_code + @emailAddress
UPDATE journal
SET status_date =
(SELECT max(tmp.status_date)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @product_code
AND j.product_code = tmp.product_code)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @product_code
AND j.product_code = tmp.product_code
END
FETCH NEXT FROM transaction_import_cursor INTO @transaction_id, @product_code, @emailAddress, @status_date, @business_code, @expire_date, @prod_status
END
CLOSE transaction_import_cursor
DEALLOCATE transaction_import_cursor
COMMIT TRAN

/** purge data from temp error table before writing bad records for this batch **/
truncate table tran_import_error;

/** write bad records (missing product code or email address) to temp_error table **/
insert into tran_import_error (transaction_id, product_code, emailAddress, date_entered)
SELECT DISTINCT transactions_batch_tmp.transaction_id, transactions_batch_tmp.product_code, transactions_batch_tmp.emailAddress, getDate()
FROM transactions_batch_tmp
where transactions_batch_tmp.emailaddress not in (select emailaddress from contacts)
OR
transactions_batch_tmp.product_code not in (select product_code from products)


TIA

View Replies !

---

A BUG In Cursor Or What Is Going On???
Hi, I have created a cursor to select 5 rows from a table , then I put the cursor in a store procedure, when I test the cursor the firt time I get correct results, but when I test it again in the same query window NOTHING happen?.... is this a BUG or am I doing something wrong, by the way, when I execute the procedure in another query window, I get it right. In conclusion, whenever I run the procedure more than once in the same query window the second try fails...but when I go to another query window it works.. I appreciate if anyone can explain why?

reagards
Ali

create procedure p_test_cursor as
DECLARE RST CURSOR
FOR SELECT counter,INVESTORID,BALANCE
FROM BALANCE
WHERE INVESTORID =300
OPEN RST
DECLARE @COUNTER INT,@INVESTORID INT, @BALANCE MONEY -- putting rst col in @
WHILE @@FETCH_STATUS <>-1 -- mean when there is no error

BEGIN
SELECT @COUNTER ,@INVESTORID,@BALANCE
UPDATE BALANCE
SET OWNERSHIP =@COUNTER
WHERE COUNTER =@COUNTER
FETCH RST INTO @COUNTER ,@INVESTORID,@BALANCE
END
CLOSE RST
DEALLOCATE RST

View Replies !

---

Cursor, Is It The Only Way?
Is using cursor the only way to do update in this case.
I'm updating TableA.ID with TableB.New_id where TableA.ID =
TableB.ID. TableA has 2.5 million records and TableB has 500,000
records. Doing it this way bring the system down to it's knees, and
is taking forever. Any suggestion are welcome.

declare mrn_cur cursor for
select dealer_ident, kealer_id
from dealer
for read only

declare @result int
declare @temp_ident int
declare @temp_id int
declare @temp_var int

open mrn_cur
fetch mrn_cur into @temp_ident, @temp_id

while (@@fetch_status = 0)
begin
begin transaction
update label
set dealer_id = @temp_ident
where dealer_id = @temp_id
commit tran
fetch mrn_cur into @temp_ident, @temp_id
end
close mrn_cur
deallocate mrn_cur
go

View Replies !

---

Cursor Help!
This is what I am trying to do:

Table 1 has numerous resume's for each person. Each resume has a unique id.
ie: Table 1
res_id fname lname userid pwd address city state etc...
100 John Doe jd ok xxxx xxxx xx xxxx
104 Sally May sm sm ccccc cc c cc ccc
643 John Doe jd ok ssss null null
1003 John Doe jd ok 123 elm Nome AK ...
5000 Tom Cat tc tc null null null

I need to insert into Table 2 only the demographic information for each person appearing in Table 1. The catch is that Table 2 doesn't have the same unique id that appears in Table 1. userid and pwd are unique to Table 2 but are numerous in Table 1.

Table 2
new_ident userid pwd address city state etc..
10 jd ok 123 elm Nome AK ....
11 Sally May sm sm ccccc cc c cc ccc
12 Tom Cat tc tc null null null


Basically I need to choose the most current "max(res_id)" occurance for John Doe above to get only one row out of his three rows. Then I need to get all the other unique rows from table 1.

I hope that is clear. I was considering a cursor. Any ideas??

Troy

View Replies !

---

Cursor
I am receiving this error when running my cursor:

Error Messages...
Server: Msg 16933, Level 16, State 1, Line 0
The cursor does not include the table being modified.
The statement has been terminated.
Server: Msg 16933, Level 16, State 1, Line 5 (this message repeats)...
The cursor does not include the table being modified.
The statement has been terminated.

query:

declare cursor_test CURSOR for
select emp_ssn, effective_date1 from temp_employee_benefit_load
open cursor_test
declare @ssn char(9), @process_date char(8)
fetch next from cursor_test into @ssn, @process_date
while (@@fetch_status=0)
update test_cursor
set ssn = @ssn, process_date = @process_date
where current of cursor_test
fetch next from cursor_test into @ssn, @process_date

close cursor_test
deallocate cursor_test


Any help is appreciated:

Thanks,

View Replies !

---

Cursor
I am trying to build a cursor based on a query that uses a variable and cant seem to make it work..

Here is the query:

declare ob_cursor cursor for
select name
from @dbname.dbo.sysobjects
where xtype = 'U'

How could I pass this cursor declaration the database name?

Any help would be appreciated.

View Replies !

---

Can I Do This Without A Cursor?
Here's what I' trying to do...

I am writing an application for a training facility. I have three tables in particular that I'm concerned with.

Here they are with the relevent keys
1. Registrations - contains a customerid and a classid
2. Sessions - contains a classid (multiple sessions can exist for a class)
3. Attendence - contains a session and a customerid.

Whenever I insert a session record for a class, I want to automatically create a corresponding record in the attendence table for every student in the class . My only thought is to create an insert trigger on the session table than creates creates a cursor containing the customerid for every student registered in the class. Then I can walk through the cursor and insert an Attendence record for each student.

I really don't want to use a cursor if I can help it, but I can't think of a way to write an single INSERT statement to put into my trigger. Is there a way to do this without using a cursor?

Any thoughts would be appreciated.
David

View Replies !

---

Can I Not To Use Cursor?
I am sure I am not the first one ask this.
I have got two tables, what I would like to do now is to update the second table using the values in the first table where
T1.id = T2.id, normally I have to use cursor to loop through table two to achieve this. But is it possible to do this without using cursor?

Thanks,

Alan

View Replies !

---

Cursor
How can I do take the select of a stored procedure from another stored procedure.
Something like that:

PROC A
Declare c Cursor For Exec B




PROC B
Select * From Clients

View Replies !

---

Need Help With Cursor??
I am trying to determine how many future weeks of demand to cover.
I think I need to use a cursor but I haven not used one before.

Select Style, color, size, corp_week, inv_qty
From DSOINV
order by Style, color, size, corp_year,corp_week

I want to look at the matching style, color, size from the DSODMD
but the DSODMD.Corp_Week will always be the week following DSOINV.Corp_Week

Week# 1 2 3 4 5
Demand 10 20 25 40
Inv 50 40 65 60
Ex:For CorpWeek1 The inventory (50)covers demand for CorpWeek2(10)+CorpWeek3(30)plus
.8 of CorpWeek4(20 of 25)= 2.8 weeks(1 + 1 + .8)
CorpWeek2 The inventory(40) covers demand for CorpWeek3(20)plus
.8 of CorpWeek4(20 of 25)= 1.8 weeks(1 + .8)
CorpWeek3 The inventory(65) covers demand for CorpWeek4(25)plus
CorpWeek5(40)= 2.0 weeks(1 + 1)
CorpWeek4 The inventory(60) covers demand for CorpWeek5(40) - the number would be
infinite b/c inventory does not have demand to cover it

CREATE TABLE [DSOINV] (
[Corp_Year] [int] NOT NULL ,
[Corp_Week] [int] NOT NULL ,
[Style] [char] (7) NOT NULL ,
[Color] [char] (3) NOT NULL ,
[Size] [char] (3) NOT NULL ,
[InvQty] [int] NULL ,
CONSTRAINT [PK_DSOINV] PRIMARY KEY NONCLUSTERED
(
[Corp_Year],
[Corp_Week],
[Style],
[Color],
[Size]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [DSODMD] (
[Corp_Year] [int] NOT NULL ,
[Corp_Week] [int] NOT NULL ,
[Style] [char] (7) NOT NULL ,
[Color] [char] (3) NOT NULL ,
[Size] [char] (3) NOT NULL ,
[DMDQty] [int] NULL ,
CONSTRAINT [PK_DSOINV] PRIMARY KEY NONCLUSTERED
(
[Corp_Year],
[Corp_Week],
[Style],
[Color],
[Size]
) ON [PRIMARY]
) ON [PRIMARY]
-- populate DSOINV table
insert into DSOINV values(2000,1,29M,TNA,XL,50)
insert into DSOINV values(2000,2,29M,TNA,XL,40)
insert into DSOINV values(2000,3,29M,TNA,XL,65)
insert into DSOINV values(2000,4,29M,TNA,XL,60)
insert into DSOINV values(2000,1,29M,WHI,2X,50)
insert into DSOINV values(2000,2,29M,WHI,2X,40)
insert into DSOINV values(2000,3,29M,WHI,2X,65)
insert into DSOINV values(2000,4,29M,WHI,2X,60)
insert into DSOINV values(2000,1,562M,WHI,XXL,50)
insert into DSOINV values(2000,2,562M,WHI,XXL,40)
insert into DSOINV values(2000,3,562M,WHI,XXL,65)
insert into DSOINV values(2000,4,562M,WHI,XXL,60)
-- populate DSODMD table
insert into DSODMD values(2000,2,29M,TNA,XL,10)
insert into DSODMD values(2000,3,29M,TNA,XL,20)
insert into DSODMD values(2000,4,29M,TNA,XL,25)
insert into DSODMD values(2000,5,29M,TNA,XL,40)
insert into DSODMD values(2000,2,29M,WHI,2X,50)
insert into DSODMD values(2000,3,29M,WHI,2X,10)
insert into DSODMD values(2000,4,29M,WHI,2X,25)
insert into DSODMD values(2000,5,29M,WHI,2X,20)
insert into DSODMD values(2000,2,562M,WHI,XXL,10)
insert into DSODMD values(2000,3,562M,WHI,XXL,20)
insert into DSODMD values(2000,4,562M,WHI,XXL,25)
insert into DSODMD values(2000,5,562M,WHI,XXL,40)

My output should look like 2000, 1, 29M, TNA, XL, 2.8
2000, 2, 29M, TNA, XL, 1.8
2000, 3, 29M, TNA, XL, 2.0
2000, 4, 29M, TNA, XL, infinite number
2000, 1, 29M, WHI, 2X, 2.8
2000, 2, 29M, WHI, 2X, 1.8
2000, 3, 29M, WHI, 2X, 2.0
2000, 4, 29M, WHI, 2X, infinite number
2000, 1, 29M, WHI, XXL, 2.8
2000, 2, 29M, WHI, XXL, 1.8
2000, 3, 29M, WHI, XXL, 2.0
2000, 4, 29M, WHI, XXL, infinite number

View Replies !

---