Hi I'm opening a RecordSet using the following code :
declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode
FROM TCertResults TSample
WHERE (TSample.ISmpShortCode ='24/12359')
Open trcl
What I want to be able to get a count of this recordset. Been thru the TSQL help and it's pointing me toward the @.@.RowCount command but I can't seem to get this working.
Any help would be apprieciated@.@.ROWCOUNT returns the number of rows affected by the last statement. Use the @.@.CURSOR_ROWS function to receive the number of qualifying rows in the last opened cursor.
BTW - do you really need cursor? It heavily hits DB performance.|||I agree,
Wont SELECT COUNT(*) FROM TCertResults TSample
WHERE TSample.ISmpShortCode ='24/12359' do the trick?|||No cursrors...No cursors...
There is precious little you can do without them...
Describe what your trying to do...|||The cursor is a lot more complicated than posted but the basic gist is that the results of the cursor will be 'fetched' into variables which will then be used to update other tables, etc. Then the variables will be repopulated using the fetch next command. When updating the other tables with the results of the original cursor, one of the values that I need to write is total amount of records from the original cursor.
So I need the recordcount as soon as the cursor is open. But as I'm sure you've gathered I haven't got a clue how to get this!|||Makes no sense to me...(being sober sucks...)
So you want to write the count of the entire result set, for every row in the cursor?
That seems to be a derivation of a thing...
Anyway...do this before you open the cursor and just use the local variable..
DECLARE @.x int
SELECT @.x=COUNT(*)
FROM TCertResults TSample
WHERE ISmpShortCode ='24/12359'
Still, you could do all of this without a cursor.
Good luck|||Looks for me you can use temp tables instead of cursors...
But if you are decided to stay with them - didn't the @.@.CURSOR_ROWS suits you? Why - any reason? error? sth other?|||Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax
declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
FROM dbo.TSample INNER JOIN
dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
WHERE (dbo.TSample.ISmpShortCode = '24/12359')
Open tcrl
PRINT @.@.CURSOR_ROWS|||You don't my count solution?|||Your solution works great thank you.
I was just continuing the thread in the hope that someone could tell me what the correct syntax @.@.Cursor_Rows function was.
Cheers|||Look in BOL (Books Online):
@.@.CURSOR_ROWS
Returns the number of qualifying rows currently in the last cursor opened on the connection. To improve performance, Microsoft SQL Server can populate large keyset and static cursors asynchronously. @.@.CURSOR_ROWS can be called to determine that the number of the rows that qualify for a cursor are retrieved at the time @.@.CURSOR_ROWS is called.
Return value Description
-m The cursor is populated asynchronously. The value returned (-m) is the number of rows currently in the keyset.
-1 The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved.
0 No cursors have been opened, no rows qualified for the last opened cursor, or the last-opened cursor is closed or deallocated.
n The cursor is fully populated. The value returned (n) is the total number of rows in the cursor.
Don't use Cursors...|||Originally posted by SexualChocolate
Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax
declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
FROM dbo.TSample INNER JOIN
dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
WHERE (dbo.TSample.ISmpShortCode = '24/12359')
Open tcrl
PRINT @.@.CURSOR_ROWS
The Great Holy Online Book says:
"If you receive '-1' as return from @.@.CURSOR_ROWS that it means:
The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved. You can try with Brett Kaiser's count solution. So says The Great Holy Online Book"
Again:
<ghost_voice>Beware of cursors... uuuuaahahahahahaaa....</ghost_voice>|||like I say I'm fairly new to all this. I'm using a cursor because I don't know of another was to open a recordset and step thru it. If there is an alternative then please point me at it|||Count - to determine no of records,
temp tables or derived tables to help recordset operations.|||People that normally write code in client tools tend to use cursors when writing code in SQLServer..thats perfectly understood since thats the way its done in vb, c, c# etc.
BUT SqlServer is about batch updates (most of the time); lots and lots and lots of rows being updated/inserted/deleted at once, a cursor just isnt efficient enough..
My role is:If you are forced to use cursors or other types of loops in more than 1% of your sql code you have problaly done something wrong..|||Fair enough. So is there another way you can open a RecordSet populate variables based upon the first row returned from the recordset, perform an update on another table based upon the values held within these variables, step to the next row, repopulate the variables, perform the update again, etc. Without using a cursor?
Everybody tells me that there evil and everything I've read instructs me not to use them but is there another way?
TSQL novice|||You need to get your mindset away from "stepping to the next row", and think about updating ALL the rows SIMULTANEOUSLY. That is what set-based transactions are all about, and that is the power of a database management engine.
If you want to know how to do it, you will first need to read the Books Online entries for SELECT statements and UPDATE statements. Then, if you are still confused, post again and give more details about what you are trying to do. We can assist you in writing the code, but we need a base to build upon.|||Can you give us an example of an update and the cursor itself...also, tying the # of records to the "batch" that result set came from is sort of meangless...because data is fluid by nature , that number is wrong the instant you set it on the row...
Maybe a brief business description of what's going will help make things more clear to us.|||Okey dokey, heres the cursor:
declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode, TAnalyses.IAnalysesN, TSample.ISmpShortCode, TCertResults.ICertResultsN, TCertResults.LabNo,
TCertResults.LabNoDateTime, TJob.IJobCode, TAnalyses.IAnCode, TAnalyses.TemperatureID + 48 AS MyTempAscii,
TMedia.SpreadOrPour, TAnalyses.MediaID + 48 AS MyMediaAscii, TAnalyses.IAnShortCertDesc, TCertResultSpecDilution.Dilution,
TCertResultSpecDilution.SpecDilN, TAnalyses.KiestraTest, TAnalyses.LabelRule, TAnalyses.IAnWorkType, TAnalyses.Pathogen,
TAnalyses.TemperatureID,TAnalyses.CameraSortID,TAn alysesIncubation.IncubationLength,TAnalysesIncubat ionTolerance.IncubationTolerance
FROM TCertResults INNER JOIN
TSample ON TCertResults.ISmpN = TSample.ISmpN INNER JOIN
TJob ON TSample.IJobN = TJob.IJobN INNER JOIN
TAnalyses ON TCertResults.IAnalysesN = TAnalyses.IAnalysesN INNER JOIN
TCertResultSpecDilution ON TCertResults.ICertResultsN = TCertResultSpecDilution.ICertResultsN INNER JOIN
TAnalysesIncubationTolerance ON
TAnalyses.IncubationToleranceID = TAnalysesIncubationTolerance.IncubationToleranceID LEFT OUTER JOIN
TMedia ON TAnalyses.MediaID = TMedia.MediaID LEFT OUTER JOIN
TAnalysesIncubation ON TAnalyses.IncubationID = TAnalysesIncubation.IncubationID
WHERE (TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@.MYLSN,','))) AND (TCertResults.LabNo IS NOT NULL) AND (TCertResults.LabNoDateTime IS NOT NULL) AND
(TAnalyses.KiestraTest = 1) AND (TAnalyses.LabelRule = 'Shared') AND (TAnalyses.IAnWorkType = 'M') AND
(TAnalyses.Pathogen = 0)
Open tcrl
Fetch tcrl into @.MyLongLsn,@.MyAnalysisID,@.MyShortCode,@.MyCertResul tsN,@.MyLabNo,@.MyLabNoDateTime,@.MyJobCode,@.MyIanCod e,@.MyTempAscii,@.MySpreadOrPour,@.MyMediaAscii,@.MySh ortTestDesc,@.MyDilution,@.MySpecDilN,@.MyKiestraTest ,@.MyLabelRule,@.MyIanWorkType,@.MyPathogen,@.MyTemper atureID,@.MyCameraSortID,@.MyIncubationLength,@.MyInc ubationTolerance
While @.@.Fetch_status=0
Based upon the results of the this cursor variables will be populated and tested for certain criteria and other variables populated as a result of the results. Something like:
IF @.MySpreadOrPour = 'Pour'
BEGIN
Set @.MyMediaID = '0'
END
ELSE
BEGIN
Set @.myMediaID = @.MyMediaAscii - 48
END
Then finally another table will be populated with the values held within the variables:
INSERT INTO TBarCodAFile ( Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Dilution, LabelRule, DateDue)
VALUES (@.MyBarCode,@.MyLongLSN,@.MyJobCode,@.MyShortTestDesc ,@.MyDatePlateToBeRead,@.MyMediaID,@.MyStacker,@.MyWhi teLabels,@.MyAnalystPosition,@.MyEmpID,@.MyFormatting ,@.MyDilution,@.MyLabelRule,@.MyWholeDatePlateToBeRea d).
I'll then move to the next record in the recordset (Fetch next from tcrl into @.MyLongLsn,@.MyAnalysisID,@.MyShortCode,@.MyCertResul tsN,@.MyLabNo,@.MyLabNoDateTime,@.MyJobCode,@.MyIanCod e,@.MyTempAscii,@.MySpreadOrPour,@.MyMediaAscii,@.MySh ortTestDesc,@.MyDilution,@.MySpecDilN,@.MyKiestraTest ,@.MyLabelRule,@.MyIanWorkType,@.MyPathogen,@.MyTemper atureID,@.MyCameraSortID,@.MyIncubationLength,@.MyInc ubationTolerance).
Then do all my tests again and perform the INSERT statement again. And continue unless the end of the recordset.
No doubt this is a serious pain in the arse but I can find no other way of doing it.|||FIRST...lose the fully qualified table names...
Second, THAT'S ONE HELL OF A JOIN...ARE THE KEYS ALL IDENTITY?
Third, if it takes a long time to run, you could save the results to a temp table and do all of your work as a join to that
Fourth (and I'm liking the temp table thing more and more0 you could do the insert like:
INSERT INTO TBarCodAFile(
Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, Field9
, Field10
, Field11
, Dilution
, LabelRule
, DateDue)
SELECT b.ISmpCode
, d.IAnalysesN
, b.ISmpShortCode
, a.ICertResultsN
, a.LabNo
, a.LabNoDateTime
, c.IJobCode
, d.IAnCode
, d.TemperatureID + 48 AS MyTempAscii
, g.SpreadOrPour
, d.MediaID + 48 AS MyMediaAscii
, d.IAnShortCertDesc
, e.Dilution
, e.SpecDilN
, d.KiestraTest
, d.LabelRule
, d.IAnWorkType
, d.Pathogen
, d.TemperatureID
, d.CameraSortID
, h.IncubationLength
, f.IncubationTolerance
FROM TCertResults a
INNER JOIN TSample b
ON a.ISmpN = b.ISmpN
INNER JOIN TJob c
ON b.IJobN = c.IJobN
INNER JOIN TAnalyses d
ON a.IAnalysesN = d.IAnalysesN
INNER JOIN TCertResultSpecDilution e
ON a.ICertResultsN = e.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance f
ON d.IncubationToleranceID = f.IncubationToleranceID
LEFT JOIN TMedia g
ON d.MediaID = g.MediaID
LEFT JOIN TAnalysesIncubation h
ON d.IncubationID = h.IncubationID
WHERE (TSample.ISmpShortCode IN
(SELECT *
FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')
)
)
AND (TCertResults.LabNo IS NOT NULL)
AND (TCertResults.LabNoDateTime IS NOT NULL)
AND (TAnalyses.KiestraTest = 1)
AND (TAnalyses.LabelRule = 'Shared')
AND (TAnalyses.IAnWorkType = 'M')
AND (TAnalyses.Pathogen = 0)|||Obviously the columns do not match...there's more in the select than the INSERT, you need to match them up...
How long does that monster take to run as a stand alone select?|||I've got to be honest and at this point say you've lost me. Maybe I just can't get my head around this temp table idea but as I see it if I populated a temp table with the results of a straight select statement surely I'd then have to read all the rows from this table to perform the criteria testing and wouldn't I still a cursor to do this. Or more likely I'm being a cretin.
Most of the linking fields are identity,yes.
Oh yeah its a proper beast, it used to be coded in Access VBA and it could take up to ten minutes to complete depending on the amount of rows affected.
I've been asked to re-code it server side and its the first time I've ventured into server side programming, so please excuse my naivety.|||Where did I lose you?
The INSERT?
The Table Aliases?
The Temp Table?
I would do...
SELECT b.ISmpCode
, d.IAnalysesN
, b.ISmpShortCode
, a.ICertResultsN
, a.LabNo
, a.LabNoDateTime
, c.IJobCode
, d.IAnCode
, d.TemperatureID + 48 AS MyTempAscii
, g.SpreadOrPour
, d.MediaID + 48 AS MyMediaAscii
, d.IAnShortCertDesc
, e.Dilution
, e.SpecDilN
, d.KiestraTest
, d.LabelRule
, d.IAnWorkType
, d.Pathogen
, d.TemperatureID
, d.CameraSortID
, h.IncubationLength
, f.IncubationTolerance
INTO WorkTable
FROM TCertResults a
INNER JOIN TSample b
ON a.ISmpN = b.ISmpN
INNER JOIN TJob c
ON b.IJobN = c.IJobN
INNER JOIN TAnalyses d
ON a.IAnalysesN = d.IAnalysesN
INNER JOIN TCertResultSpecDilution e
ON a.ICertResultsN = e.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance f
ON d.IncubationToleranceID = f.IncubationToleranceID
LEFT JOIN TMedia g
ON d.MediaID = g.MediaID
LEFT JOIN TAnalysesIncubation h
ON d.IncubationID = h.IncubationID
WHERE (TSample.ISmpShortCode IN
(SELECT *
FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')
)
)
AND (TCertResults.LabNo IS NOT NULL)
AND (TCertResults.LabNoDateTime IS NOT NULL)
AND (TAnalyses.KiestraTest = 1)
AND (TAnalyses.LabelRule = 'Shared')
AND (TAnalyses.IAnWorkType = 'M')
AND (TAnalyses.Pathogen = 0)
INSERT INTO TBarCodAFile(
Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, Field9
, Field10
, Field11
, Dilution
, LabelRule
, DateDue)
SELECT ColList
FROM WorkTable
DROP WorkTable|||This is not all that bad. This should get you started:
INSERT INTO TBarCodAFile
(--Field1,
Field2,
Field3,
Field4,
--Field5,
Field6,
--Field7,
--Field8,
--Field9,
--Field10,
--Field11,
Dilution,
LabelRule
--DateDue
)
select --MyBarCode function,
TSample.ISmpCode,
TJob.IJobCode,
TAnalyses.IAnShortCertDesc,
--MyDatePlateToBeRead function
Case when TMedia.SpreadOrPour = 'Pour' then 0 else TAnalyses.MediaID end,
--MyStacker function,
--MyWhiteLabels function,
--MyAnalystPosition function,
--MyEmpID function,
--MyFormatting function,
TCertResultSpecDilution.Dilution,
TAnalyses.LabelRule
--MyWholeDatePlateToBeRead function
FROM TCertResults
INNER JOIN TSample ON TCertResults.ISmpN = TSample.ISmpN
INNER JOIN TJob ON TSample.IJobN = TJob.IJobN
INNER JOIN TAnalyses ON TCertResults.IAnalysesN = TAnalyses.IAnalysesN
INNER JOIN TCertResultSpecDilution ON TCertResults.ICertResultsN = TCertResultSpecDilution.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance ON TAnalyses.IncubationToleranceID = TAnalysesIncubationTolerance.IncubationToleranceID
LEFT OUTER JOIN TMedia ON TAnalyses.MediaID = TMedia.MediaID
LEFT OUTER JOIN TAnalysesIncubation ON TAnalyses.IncubationID = TAnalysesIncubation.IncubationID
WHERE TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')) --This should be changed to a join...
AND TCertResults.LabNo IS NOT NULL
AND TCertResults.LabNoDateTime IS NOT NULL
AND TAnalyses.KiestraTest = 1
AND TAnalyses.LabelRule = 'Shared'
AND TAnalyses.IAnWorkType = 'M'
AND TAnalyses.Pathogen = 0
I've commented out elements where the calculation function was not available. It should run MUCH faster if you convert it to this format.
...and I like using qualified column names, Brett! To each his own...|||Yeah to each his/her own...
But it's so verbose...
Sounds like they need to do many actions against many different tables based on the result.
What do you think of the temp table idea?
Just 1 pass versus over and over...|||Temp table (or table variable) is better than a cursor, no doubt.|||Ok I'll give those idea's a blast. Doubtless I'll be back in touch in about 15 minutes!!
Thanks again for your time and patience|||Originally posted by SexualChocolate
Ok I'll give those idea's a blast. Doubtless I'll be back in touch in about 15 minutes!!
Thanks again for your time and patience
OK...I gotta know...2:44?
Where in the world are you?|||Birmingham, England
How do I call a function from within a select statement? Told you I'd be back!|||You're better off starting a new thread for a new question but...
SELECT dbo.udf_myFunction99(Col1)....|||Thanks to everyone who posted.
Ta
![Smile [:)]](http://pics.10026.com/?src=/emoticons/emotion-1.gif)
)?And why do you use transactions in this example (I mean as long as you justhave select statements and none update, insert or delete statements?).
, see my code below: