Cursor Looping Versus Set-based Queries

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 !

---

Cursor Versus While Loop
I have always been told that Cursors create a lot of overhead and consume a lot of system resources. Is it faster to store the data in a temp table and loop through it by using Select Top 1 and Delete statements or by using a static, Forward-Only Cursor? Both ways store the data in TempDB, but doesn't the While Loop statement generate more IO's than the Cursor? In theory, I am thinking that the Cursor is better. Any info will be appreciated.

Thanks!!

View Replies !

---

Looping Cursor
Hi,

I need to create a cursor that will loop through my customer database to return matching rows of data based on my select statement criteria. I have written most of it based on what I remember from my limited SQL exposure at a previous job afew years ago, but I can't remember how to make the @cust_id varaible increment by 1 and loop to the end of the customer table.

Can anyone steer me in the right direction here please?

DECLARE @cust_id INT
SET @cust_id = 371
DECLARE my_cursor CURSOR FOR
SELECT CUSTOMER_ID, FULL_NAME, ADDRESS_LINE1, SUBURB, STATE, POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE POSTCODE IN (SELECT POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE CUSTOMER_ID = @cust_id
AND INACTIVE = 0 )
AND CUSTOMER_ID <> @cust_id

SELECT CUSTOMER_ID, FULL_NAME, ADDRESS_LINE1, SUBURB, STATE, POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE CUSTOMER_ID = @cust_id
AND INACTIVE = 0
OPEN my_cursor
--SET @cust_id = @cust_id + 1
FETCH NEXT FROM my_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor
END
CLOSE my_cursor
DEALLOCATE my_cursor

View Replies !

---

Looping Database Queries
Hi, newbie here:I have created a small (5 fields) Access table which I will beconverting to MS-SQL Server format when the ASP code has beencompleted. It is basically a collection of links to news releases frommining companies. The group of people who will be browsing thisdatabase want to know if the news release pertains to their area.Sometimes the news release pertains to multiple areas if the miningproperties are scattered. Given the possibility of a one-to-manyrelationship, ie one news release, many areas, I created an additionaltable for the areas. I created the ASP code to pull down the newsrelease information, then loop through the area records such as:set RSNewsRelease = Server.CreateObject("ADODB.Recordset")NewsRelSQL = "Select date, company, title, newsreleaseID fromnewsreleases;"do while not RSNewsRelease.EOF'display news release date, company and titleresponse.write RSNewsRelease(0).Value & RSNewsRelease(1).Value &RSNewsRelease(2).Value'loop through areasset RSAreas = Server.CreateObject("ADODB.Recordset")'run querydo while not RSAreas.EOF'display areasLoopset RSAreas = nothingLoopIn other words, the only way I could get the results I wanted was toset the Recordset to nothing, then reset it with each iteration of theouter loop.Is there a better way to do this?Jules

View Replies !

---

Application/Security Design: Stored Procedures Versus SQL Queries
Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries? 

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

 

Any thoughts or ideas?

Thanks for your time, Adrian

 

View Replies !

---

Looping A Package Based On A Return Value From A Stored Proc?
Hi,
 
I have a package that I need to loop (possibly multiple times) based on a return value from a stored procedure.
 
I know that in DTS you could use the return value of "execution status" to do this.
 
Is there a way in SSIS to loop based on a return value?
 
Thanks much

View Replies !

---

Cursor Based SQL?
Does anyone have any good references they could recommend on Cursorbased SQL writing? I have to create SQL that can loop though recordssimular to VB loops and I have been told that this is the way to go.Any recommendations would be helpful.

View Replies !

---

Repeating Cursor Queries - Fails
When I try to repeat a query using cursors the first run is succesful but the second returns nothing; I then have to disconnect and reconnect to get the query to work again. Here's sample code (which does nothing except print returned items)

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

declare @login sysname , @password sysname

declare sourcelogins cursor for

select name , password

from master.dbo.syslogins

open sourcelogins

while ( @@fetch_status = 0)

begin

fetch sourcelogins into @login , @password

print @login

end

close sourcelogins

deallocate sourcelogins


SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO

First time through this prints all logins. Second time it returns "completed successfully" and prints nothing. Similarily, if I run two queries consecutively, the second using a differenct cursor name, the second still fails. Any ideas?

View Replies !

---

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 !

---

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 !

---

Performing Date Range Queries Based On A Non-Calendar Fiscal Year
Hi,

Using SQL Server 2000, I need to perform date range type queries that involve my company's Fiscal Year, which is not the same as the calendar year.  My company's Fiscal Year if from Sept 1 to Aug 31, where Aug 31st year determines the Fiscal Year.  For example, since today's date is 09/20/2006, the current Fiscal Year is 2007.

 An example of a typical query requirement:

Find all the sales figures to-date for the current Fiscal Year.  So, a WHERE clause will consist of a date range query from 09/01/2006 to 8/31/2007.

Initially, I created a Function to find the current Fiscal Year based on the current date, by calling the GETDATE() function and passing the results to the following function:

CREATE  FUNCTION dbo.fnGetFY (@CurrentDatetime datetime) 
RETURNS int
 AS 
BEGIN
  DECLARE @FY int
  IF (SELECT MONTH(@CurrentDatetime)) > 8
    SET @FY = YEAR(@CurrentDatetime) + 1
  ELSE
    SET @FY = YEAR(@CurrentDatetime)
   RETURN(@FY)
END

So, the view queries that  involve the Fiscal Year call the above function.

However, these function calls drag down the VIEW query response time to the point where the time is either unacceptable or an ODBC Timeout occurs, even with Query Analyzer.

Is there a way to create a Global server parameter to hold the current Fiscal Year value, so function calls are not necessary?  Or set Fiscal Year date ranges for a database or server system parameter? 

Does anyone know of a efficent, response timewise,  way to handle Fiscal Year date range queries?

Will appreciate the help!!!

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 !

---

Nesting A Looping Query Withing A Looping Query
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
     Category                    SubCategory           (PK)CategoryName      (PK) SubCategoryNameCategoryID                    SubCategoryIDDate                                Date                      (Just shows the date inserted)                                  (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?

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 !

---

@@ Versus @ In T-SQL
I'm sorry to be ignorant on this point. It seems trivial, but what's the difference between @@ and @ when using variables in T-SQL? I have a developer that always uses @@ for local variables and @ for reference variables (meaning variables declared as parameters for a stored procedure or function).

Is that purely stylistic? Is it a holdover from some previous version? Or is it a legitimate best practice that I've not seen before?

My google-shui is weak today; I found nothing when searching.

Regards,

hmscott

View Replies !

---

SP2 Versus SP1
Do you need SP1 installed before installing SP2?


http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

View Replies !

---

SQL - SP1 Versus SP2
Hi everybody. I am just confused. What is the difference between SQL SP1 and SP2 ? Can anyone explain further to me.

Thanks.
-Ron-

View Replies !

---

NOT EXISTS Versus NOT IN
I have two tables :

tableA
a_id (int)
value (varchar 255)

tableB
id (int)
a_id (int)
b_id (int)
c_id (int)
d_id (int)

Both these tables contain considerable amounts of rows, but over time tableA will end up containing orphaned values (i.e. the a_id is not used in tableB) and this problem cannot be rectified by setting, for example, cascade deletes.

To fix this problem I decided to write a simple stored procedure to purge all values in tableA where its a_id is not used in tableB :

DELETE FROM tableA WHERE a_id NOT IN (SELECT a_id FROM tableB)

Now although the following document relates to postgres :

http://archives.postgresql.org/pgsql-sql/2003-12/msg00174.php

I was interested to find out if I should be wary of using NOT IN in my query.

View Replies !

---

SSIS Versus DTS
Hello,

 

I wonder if anyone else out there has the same impression that I have: I find that DTS works much better than SSIS.

 

I find that DTS is so easy to use and reliable: it gets the job done and fast! On the other hand, SSIS seems to be so needlessly complex that it takes hours of troubleshooting just to get it to work, and sometimes it doesn't work at all. For example I have just spent hours trying to get SSIS to import a flat file with 300,000 rows. It just crashes and doesn't even give an error message so that one can fix it. On the other hand I have just now successfully accomplished the same task with DTS and it took me 5 minutes!

 

I honestly don't see a valid reason for using SQL Server 2005 instead of 2000. So far it's much more productive to use 2000.

 

I hope Microsoft can clarify this issue.

 

Regards,

 

Jerome Smith

View Replies !

---

SET Versus SELECT
Hello,

I have an Output parameter as follows:
@Feedback INT OUTPUT

I want to give it a value and return it.
What is the difference between using:

SELECT @Feedback = -1
RETURN @Feedback

And

SET @Feedback = -2
RETURN @Feedback

Thanks,
Miguel

View Replies !

---

ADO.NET DATAVIEWS VERSUS SQL ENGINE
Hi All,
Any suggestions / views / help on below question would be welcomed.
I am building an asp.net 2.0 application with sql 2005 express as back end. My back end has 3 major tables which are:
tblArticles - saves basic info on articles posted by user (like articleid, title, short desc, rating, views, etc)
tblCategories - saves various categories and their hierarchies (id, parented, name, etc)
tblArticleCategories - saves info on which articles fall in which categories (like articleid, categoryid)
as of now, i am caching all rows from the first 2 tables, but i am in a bit of doubt for caching the third table (tblArticleCategories), although data in this table wont change very often and also this table will just have 2 columns and not many rows as well and this is a good target for caching,
but the reason I am in a bit of doubt to cache this table is, when my website visitor clicks on any category link in the category tree view, I need to use an inner join across all these 3 tables to locate and return all articles found in that particular category.
But I can do the same thing without hitting the database as I already have 2 of the required 3 tables in my cache, I can simply add the third table to my cache and then using the dataview objects rowfilter property on these 3 cached tables, I can very well get the appropriate results.
But I wonder which of the 2 methods would you prefer and suggest, I mean do you feel that just to save hits against the database, I am going to far and doing a lot of crap using the dataview (which might not be as efficient as sql engine) or you feel that the inefficiency of the dataview will still win compared to the cost of hitting the database for this
Thanks in advance, bye take care
Raj Chaudhari, Mumbai, India (MCAD.NET)
www.xtremebiz.biz

View Replies !

---

Generic Arc Versus Explicit Arc
in my database model I have a entityType ATTACHMENT where I relate my entityTypes to documents such as pictures,word documents,excel etc.
Person,Company and Requirement tables have one to many relationships to ATTACHMENT table.I have to implements joins to that table.
I have two ways to implement the database design.
1.Put RelatedPersonID , RelatedCompanyID ,RelatedRequirementID into ATTACHMENT table.(explicit ARC)
2.Put RelatedEntityID and RelatedentityType to ATTACHMENT table.(generic ARC)
What are the advantages and disadvantages of these two approaches?
Which one is faster with lots of data and lots of concurrent users?

View Replies !

---

Trigger Versus SQL Insert
Hello all,
I have table 'statistics' which holds information about another table, i.e. number of rows belonging to each user.
Would I be better off using a trigger after each insert to increment a certain row.
Or would I be better off selecting the data by means of an sql statement and updating the column whenever the statisitcs page is requested.
Does sql provide any methods which allow a column to count other rows or columns?
 

View Replies !

---

Update Versus Append
I have a database that is being used as sort of a reports datawarehouse. I use DTS packages to upload data from all the differentsources. Right now I have it truncating the tables and appending withfresh data. I was considering using updates instead and my question waswhich is more efficent?

View Replies !

---

Update Versus Rebuild
Hi,Is there ever a case where a total rebuilt is preferable to anupdate? I am talking in the context of a data warehouse.Many thanksSam

View Replies !

---

Return Versus Raiserror
I am trying to get a better understanding of when to use return (witha print statement) and when to use raiserror.* Both statements can be used with stored procedures while only returncan be used with functions.* With raiserror it is easy to have multiple errors thrown. (If boththe calling procedure and called procedure both try to handle error)Wow. Thought I could think of more. So that really leaves me with verylittle info on the proper use of these two statements.

View Replies !

---

Sp_addextendedproperty Versus Sysproperties
I have been searching for a way to associate a description with acolumn name. I have come across multiple posts regarding thisquestion. Problem is that I have seen two different answers.One post mentioned using the undocumented system table namedsysproperties while other posts mentioned using thesp_addextendedproperty (and fn_listextendedproperty).Which one and why one over the other?Thanks.

View Replies !

---

Joins Versus Relationships
If a database has relationships establshed between all of the tablesvia primary and foreign key constraints, why isn't is possible to makea SELECT statement across multiple tables without using a JOIN?If the system knows the relationsip schema already why are JOINSrequired?Thanks,HC

View Replies !

---

NEtwork Techs Versus DBA's
I gotta network tech that I work with from time to time. Hes gonna migrate a access database over to sql. He says it should be easy its a flat file can just do it through enterprise manager. I warned him that datatypes can become an issue (kinda have to know your db) he looked at me like I'm an idiot and proceeded to migrate the tables over to sql...Needless to say he got alot of error messages and is now totally confused. Now let me ask some experts who really Know Databases, do you ever have problems with Network Techs who think they know all

View Replies !

---

Size Versus Space Available
We are planning hardware purchases (more is better). One of our databases is 131 gigs in size and has 45 gigs of 'space available'. I'm not a very experienced SQL Server person, but this seems like quite a bit of 'space available'

1) Is there a way to regulate the amount of 'space available'?
2) are there any rules of thumb for how 'space available' there should be?

Appreciate any feedback or help.

Ray

View Replies !

---

SQL Server 2005 And 32-bit Versus 64-bit
Hi all,

we are about to purchase new database servers and have been offered a good deal on 64-bit Xeon machines. At present we run SQL 2000 on Windows Server 2003 both of which are 32-bit versions.

Is there any problem using our current 32-bit Server software on the 64-bit machines (apart from not being able to utilise its full power)? I'm assuming the SQL 2005 licenses are the same price regardless of 32-bit or 64-bit version. If we buy a 64-bit SQL Server version license are we going to get the best out of it on a 32-bit Windows Server edition?

tia - Matt

View Replies !

---

ArcServe Versus BackupExec
We currently use CA ArcServe (ArcServe 6.5 Enterprise and Single Server Editions) to backup our Windows NT files and MS-SQL Server databases. We have experienced significant reliability issues with ArcServe. Many times we have found ourselves rebuilding a corrupt ArcServe Job (ArcServe’s backup schedule) database. One of our NT server occasionally NT bug checks when ArcServe is performing backups. Occasionally ArcServe Jobs incorrectly reschedule themselves. Sometimes the Jobs do not complete but stay executing, not performing any work, and to cancel them may require a lot of effort. The ArcServe job DB repair utility generally does not work. The user interface is lacking. For example, the job scheduling options are very limited. CA tech support for this product has been poor. Because we have issues with ArcServe stability we are now evaluating Veritas (formally Seagate) Backup Exec for NT. What are other people’s experiences with these 2 products?

View Replies !

---

SQL Server Versus Informix
Hi all,

Anyone here ever used the Informix database and can give me some differences between Informix and SQL.

One of our users is thinking about purchasing a COTS product that only supports an Informix database. I need to convince the user to evaluate other rival applications that can support SQL and need some arguments in favor of not going with Informix.

Any ideas appreciated,
Faustina

View Replies !

---

DT_I8 Versus DT_UI8
Can some one tell me in basic terms the difference between a signed and unsigned integer?  When would you decided to use one over the other?  I'm looking for it more in layman terms than a technical bit level discussion.

View Replies !

---

SSIS Versus Coding.
This question probably overlaps a few different topic areas.

 

As I will be required to work with both Oracle and SQL Server I will be in a difficult position with SSIS(due to it's change in distribution).

 

Therefore I am having to look at alternatives.

 

With coding a can open a text file and parse it reasonably to my satisfaction. However getting the data into the database is incredibly slow.

 

I am using an Insert into for each line, which I am sure everone will shake their head over. This seems to be pretty slow even using transactions.

 

Is there any scope in using data tables or have the read on one thread and write on another.

 

Other than that is there an Oracle equivalent of SSIS which comes (probably get shot for asking that on a microsoft web site, but would probably get shot if I asked on Oracle forums as well).

 

In the past we had reasonable results in outputting to csv and then doing some sort of bulk insert, messy and irritating though this may be.

 

Any ideas on this area will be gratefully accepted.

View Replies !

---

Certificates Versus Keys
Hi,

View Replies !

---

HTML Versus SSRS
 

Hi
 
we are currently working in a project where we need to create reports in Visual Studio 2005. the parent screen has a drop down which specifies the name of the report along with some other parameters. then the report is displayed in the same screen. now the issue is we are pretty much confused as to what to use to generate these reports? HTML or SSRS? the input fileds are only from date and to date and the displayed fileds are also not that many.
 
can some one please throw light on this?
 
Thanks,
Kumaraswamy

View Replies !

---

Ntext Versus Nvarchar
Hi,

my question concerns both desktop and device apps.

I'm using sql compact to store some data. I often have to store strings (descriptions, url, etc.) but I don't know when to use nvarchar or ntext.

Nvarchar needs to have a size limit, but I often set it to 8092 when I don't know the actual limit (urls can be very long !).
I fear Ntext because I suppose there is performances impact.



Is there any "rules" to help to choose which data type I'd use ?

Thanks,
Steve

View Replies !

---

Authentication - Windows Versus Sql
Can anyone give me some advice on using authentication.  What is the best way to go with a database on a server and why?  And in order for you to use sql authentication, do you have to change the registry?  I have seen some posts that seem to say you can only use it by changing the registry. 
 
So if anyone can gie me the pro's and con's i would appreciate it. 
 
Thanks
Jeff

View Replies !

---

Nvarchar Versus Varchar
I have table with a field defined as nvarchar. I want to change it to varchar. I have a stored procedure which defines the parameter @strCall_desc as nvarchar(4000). Are there going to be ay problems with running this sp if I just change the field type as described.

TIA

View Replies !

---