【解決方法】以下のクエリをストアドプロシージャ (MS SQL サーバー) に変換するにはどうすればよいですか?

[ad_1]

DECLARE @locs nvarchar(max)
 
				DECLARE @RT TABLE(make nvarchar(max),
				CGS NVARCHAR(MAX),
				inserttimestamp DATETIME,
				NAME NVARCHAR(MAX),
				CGSName nvarchar(max),
				locs nvarchar(max),
				Running_Hour DECIMAL(18,2),
				IC_consumption_Kg Decimal(18,2),
				Running_Min DECIMAL(18,2)
				)

DECLARE VarC cursor 
				FOR SELECT Distinct locs from  [Archive-HourlyFirstValues]
				where CGS = @CGSName
				OPEN VarC
				FETCH FROM VarC into @locs
				WHILE(@@FETCH_STATUS=0)
					BEGIN
					insert into @RT
							SELECT 
	A.make
	,A.CGS
	,A.inserttimestamp
	,A.name
        ,A.CGS AS CGSName
	,A.locs
	,(A.value - COALESCE(LAG(A.VALUE) OVER(ORDER BY A.INSERTTIMESTAMP),0)) "Running_Hour"
	,(B.value - COALESCE(LAG(B.VALUE) OVER(ORDER BY B.INSERTTIMESTAMP),0)) "IC_consumption_Kg"
	,(C.value - COALESCE(LAG(C.VALUE) OVER(ORDER BY C.INSERTTIMESTAMP),0)) "Running_Min"
FROM [Archive-HourlyFirstValues] A
JOIN  [Archive-HourlyFirstValues] B ON A.inserttimestamp = B.inserttimestamp AND A.locs  = B.locs
JOIN  [Archive-HourlyFirstValues] C ON C.inserttimestamp = B.inserttimestamp AND C.locs  = B.locs
WHERE A.parameter = 'RUN-HR' 
	AND ((MONTH(convert(date,A.inserttimestamp,103)) = @Month
	AND DATENAME(YEAR,convert(date,A.inserttimestamp,103)) = @Year)
	OR A.inserttimestamp = DATEADD(HOUR,6,DATEADD(MONTH,DATEDIFF(MONTH,-1,DATEADD(MONTH,@Month-1,DATEADD(YEAR,@Year-1900,0)))-1,-1)))
	AND a.locs = @locs
	AND CAST(A.inserttimestamp AS TIME) = '06:00:00'
	AND B.parameter = 'ENG-MASSTOT' 
	AND ((MONTH(convert(date,B.inserttimestamp,103)) = @Month
	AND DATENAME(YEAR,convert(date,B.inserttimestamp,103)) = @Year)
	OR B.inserttimestamp = DATEADD(HOUR,6,DATEADD(MONTH,DATEDIFF(MONTH,-1,DATEADD(MONTH,@Month-1,DATEADD(YEAR,@Year-1900,0)))-1,-1)))
	AND B.CGS = @CGSName
	AND CAST(B.inserttimestamp AS TIME) = '06:00:00'

	AND C.parameter = 'RUN-Min'
	AND ((MONTH(convert(date,C.inserttimestamp,103)) = @Month
	AND DATENAME(YEAR,convert(date,C.inserttimestamp,103)) = @Year)
	OR C.inserttimestamp = DATEADD(HOUR,6,DATEADD(MONTH,DATEDIFF(MONTH,-1,DATEADD(MONTH,@Month-1,DATEADD(YEAR,@Year-1900,0)))-1,-1)))
	AND C.locs = @locs
	AND CAST(C.inserttimestamp AS TIME) = '06:00:00'

order by a.inserttimestamp,b.inserttimestamp,C.inserttimestamp,a.locs,b.locs,C.locs			
						FETCH NEXT FROM VarC into @locs
					END
			CLOSE VarC
			DEALLOCATE VarC
			Select * from @RT
	where  ((MONTH(convert(date,inserttimestamp,103)) = @Month
	AND DATENAME(YEAR,convert(date,inserttimestamp,103)) = @Year)
		)