September 20, 2013 at 6:56 am
One of my Transfer Document SSRS report, consists of a table report item showing 3 columns, Item, ItemFrom , Itemcontent.
If it displays, ‘n ‘records, for the first record, ItemFrom column shows a correct value and rest of the records showing incorrect value. I went through the SP of this. It consists of cursors for updating and uses several variables. I set the variables to blank after declaring to show the correct value.
Any other idea is highly appreciated.
In the SP, the part is shown below:
DECLARE @containerCode NVARCHAR(MAX)
DECLARE @containerKey NVARCHAR(MAX)
DECLARE @storingEventModifiedOn DATETIME
DECLARE @containerFrom NVARCHAR(MAX)
SET @containerCode = ''
SET @containerKey = ''
SET @containerFrom = ''
OPEN @containersFromStoringEventsCursor
FETCH @containersFromStoringEventsCursor INTO @containerCode, @containerKey, @storingEventModifiedOn, @containerFrom
WHILE(@@FETCH_STATUS = 0)
BEGIN
DECLARE @filteredSpecimens NVARCHAR(MAX)
EXEC [dbo].[eusp_e5_eSM_SE_ChainOfCustody002_SubReport_GetFilteredSpecimensInContainer]
@storingEventModifiedOn,
@containerKey,
@studyIdentifier,
@destinationKey,
@filteredSpecimens OUTPUT
-- We must not report containers that contain no moved specimen.
IF @filteredSpecimens != ''
BEGIN
INSERT INTO @Results
SELECTitem = @containerCode,
itemFrom = @containerFrom,
itemContent = @filteredSpecimens
ORDER BY item ASC
END
FETCH @containersFromStoringEventsCursor INTO @containerCode, @containerKey, @storingEventModifiedOn, @containerFrom
END
CLOSE @containersFromStoringEventsCursor
SELECT * FROM @Results GROUP BY itemFrom,item,itemContent
September 20, 2013 at 7:55 am
Other than what must be horrific performance with a proc that has a cursor which calls another proc which has cursor what is the issue?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply