Removing cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Removing cursors

Author

Topic

munishw
Starting Member

4 Posts

Posted - 2007-06-04 : 03:05:29

Hi friends,

I have tried to remove the cursors from the below code using the
mix and max solution but it just dint work,

could you please post a sample code to guide me to how to remove the cursors from the below code :-


DECLARE Instrument_Cur CURSOR FOR SELECT DISTINCT(ID) FROM ##Scales_From_P41 
OPEN Instrument_Cur
FETCH NEXT FROM Instrument_Cur Into @InstrumentID
WHILE @@FETCH_STATUS = 0
BEGIN
	USE P41_REPORTRULEBASE
	DECLARE Scale_Cur CURSOR FOR SELECT ID,Scale,ReportName,RulebaseName from ReportScales 
	WHERE ReportID  in (SELECT item as ID FROM udf_Split(@InputReportId) )
	OPEN Scale_Cur  
	--For each ReportScale in Scale_Cur				 
	FETCH NEXT FROM Scale_Cur INTO @ReporttID,@ReportScale,@Reportname,@RuleBaseName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		USE P41production

		SET @instrumentScale = NULL
		SET @ScaleName = NULL
		SET @UniqueTag = NULL
		SET @GenericTag = NULL

		SELECT @InstrumentTemplateId=Id,					
				@ScaleName=ScaleName,
				@InstrumentScale= InstrumentScale,
				@UniqueTag=UniqueTag, 
				@GenericTag=GenericTag,
				@Iname= InstrumentName,
				@InstrumentTemplateId=ID					
		  FROM ##Scales_From_P41 WHERE ID = @InstrumentID
		  AND  @ReportScale=Instrumentscale

		USE [P41_ReportServer]
		--IF (select @instrumentscale COLLATE SQL_Latin1_General_AI_CS) IS NULL
		IF (@instrumentscale is NULL )
		BEGIN
			INSERT INTO ##Result(ReportID,ReportName,InstrumentId,InstrumentName,ScaleName,
			UniqueTag,GenericTag,InstrumentScale,ReportScale,RuleBaseFileName,Match) 
			VALUES (@ReporttID,@ReportName,@InstrumentID,@Iname,@ScaleName,

			@UniqueTag,@GenericTag,@instrumentScale,@ReportScale,@RuleBaseName,'NO')
		END
		ELSE
		BEGIN
			INSERT INTO ##Result(ReportID,ReportName,InstrumentId,InstrumentName,ScaleName,
			UniqueTag,GenericTag,InstrumentScale,ReportScale,RuleBaseFileName,Match) 
			VALUES (@ReporttID,@ReportName,@InstrumentID,@IName,@ScaleName,

			@UniqueTag,@GenericTag,@instrumentScale,@ReportScale,@RuleBaseName,'YES')
		END

	FETCH NEXT FROM Scale_Cur INTO @ReporttID,@ReportScale,@ReportName,@RuleBaseName   
	END 
	CLOSE Scale_Cur
	DEALLOCATE Scale_Cur
FETCH NEXT FROM Instrument_Cur Into @InstrumentID
END 
CLOSE Instrument_Cur
DEALLOCATE Instrument_Cur

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 03:52:52

You can most probably do it in one set-based operation.

INSERT		P41_ReportServer.##Result
		(
			ReportID,
			ReportName,
			InstrumentId,
			InstrumentName,
			ScaleName,
			UniqueTag,
			GenericTag,
			InstrumentScale,
			ReportScale,
			RuleBaseFileName,
			Match
		)
SELECT		d.ID,
		d.ReportName,
		@InstrumentID,
		p.InstrumentName,
		p.UniqueTag, 
		d.Scale,
		p.UniqueTag,
		p.GenericTag,
		p.InstrumentScale,
		p.ReportScale,
		d.RuleBaseName,
		CASE WHEN p.InstrumentScale IS NULL THEN 'NO' ELSE 'YES' END
FROM		(
			SELECT		s.ID,
					s.Scale,
					s.ReportName,
					s.RulebaseName
			FROM		P41_REPORTRULEBASE.ReportScales AS s
			INNER JOIN	udf_Split(@InputReportID) AS w ON w.Item = s.ReportID
		) AS d
LEFT JOIN	P41PRODUCTION.##Scales_From_P41 AS p ON p.InstrumentID = d.ID AND p.ReportScale = d.Scale

Peter Larsson
Helsingborg, Sweden

munishw
Starting Member

4 Posts

Posted - 2007-06-04 : 05:29:17

Hi Peter,

Thanks for the reply.

But the solutions you have provided is not working, its giving some errors :-

Also ##Scales_From_P41 is global temp table, one can not address it
using the way you did :-

P41PRODUCTION.##Scales_From_P41 AS p ON p.InstrumentID = d.ID

Addition, in the above statement, instrumentid never joins with
report id, they dont have any relationship sets between them.

quote:

Msg 207, Level 16, State 1, Line 85
Invalid column name 'InstrumentID'.
Msg 207, Level 16, State 1, Line 85
Invalid column name 'ReportScale'.
Msg 207, Level 16, State 1, Line 85
Invalid column name 'ReportScale'.

Please guide me where I am making the mistake.

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources