MS SQL預存程序的一些小問題

預存程序 mssql

syunmin 2020-11-19 17:42:57 ‧ 2169 瀏覽

分享至

請問各位先進、大大
目前有一個需求需要寫預存程序目前遇到的問題如下

1.我有一個需求需要在預存程序內要使用變數，我有單獨測試過第一句內的Convert內的語法是沒有問題的，但實際測試時，第一句確跑不出結果；相同條件我不使用變數，如第二句卻跑的出來所以我想請教第一句是哪邊有問題

第一句-----------------------------------------------
DECLARE
@S1 INT,@S2 INT,@SS INT,@TOTALKWH INT,@TOTALKWH_Y INT,@WeekStartDay DATE,@ID VARCHAR,@StartTime1 DATETIME,@EndTime1 DATETIME

SET @WeekStartDay = '2020/11/18' SET @ID = 'PW02_KWH' SET @StartTime1 = '00:00:00' SET @EndTime1 = '07:30:00'

SELECT TOP(1)[KWH],[ID], [TRX_DATETIME] FROM [EMS10].[dbo].[EMS_LOG] WHERE [ID] = @ID AND
[TRX_DATETIME] BETWEEN
CONVERT(datetime, CONVERT (date, @WeekStartDay)) + ' ' + CONVERT(datetime, @StartTime1)
AND
CONVERT(datetime, CONVERT (date, @WeekStartDay)) + ' ' + CONVERT(datetime, @EndTime1)
ORDER BY [TRX_DATETIME] DESC

第二句-----------------------------------------------
SELECT TOP(1)[KWH],[ID], [TRX_DATETIME] FROM [EMS10].[dbo].[EMS_LOG] WHERE [ID] = 'PW02_KWH' AND
[TRX_DATETIME] BETWEEN
CONVERT(datetime, CONVERT (datetime, '2020/11/18')) + ' ' + CONVERT(datetime, '00:00:00') AND
CONVERT(datetime, CONVERT (datetime, '2020/11/18')) + ' ' + CONVERT(datetime, '07:30:00') ORDER BY [TRX_DATETIME]

我是分隔線-------------------------------------------------------------

目前是使用以下這種方法，變數都是宣告varchar(10)

declare @ID varchar(10), @WeekStartDay varchar(10), @StartTime1 varchar(10), @EndTime1 varchar(10), @StartTime2 varchar(10), @EndTime2 varchar(10), @StartTime3 varchar(10), @EndTime3 varchar(10)
declare @S1 int, @S2 int, @S3 int, @TotalTP2_LoopKWH int, @TotalTP2_LoopKWH_H int
declare @LoopStartDay varchar(10)

set @ID = 'PW02_KWH'
set @TotalTP2_LoopKWH = 0
set @TotalTP2_LoopKWH_H = 0

while datediff( day, @LoopStartDay, convert (date, getdate())) >= 0
begin
select @S1 =
(select top(1) [KWH] from [EMS10].[dbo].[EMS_LOG]
where [TRX_DATETIME] between convert( datetime, @LoopStartDay + ' '+ @StartTime1) and convert( datetime, @LoopStartDay + ' '+ @EndTime1) and [ID] = @ID order by [TRX_DATETIME] desc)-
(select top(1) [KWH] from [EMS10].[dbo].[EMS_LOG]
where [TRX_DATETIME] between convert( datetime, @LoopStartDay + ' '+ @StartTime1) and convert( datetime, @LoopStartDay + ' '+ @EndTime1) and [ID] = @ID order by [TRX_DATETIME])

if @S1 is null set @S1 = 0
set @TotalTP2_LoopKWH = @TotalTP2_LoopKWH + @S1

select @S2 = 
(select top(1) [KWH] from [EMS10].[dbo].[EMS_LOG] 
  where [TRX_DATETIME] between convert( datetime, @LoopStartDay + ' '+ @StartTime2) and convert( datetime, @LoopStartDay + ' '+ @EndTime2) and [ID] = @ID order by [TRX_DATETIME] desc)-
(select top(1) [KWH] from [EMS10].[dbo].[EMS_LOG] 
  where [TRX_DATETIME] between convert( datetime, @LoopStartDay + ' '+ @StartTime2) and convert( datetime, @LoopStartDay + ' '+ @EndTime2) and [ID] = @ID order by [TRX_DATETIME])

if @S2 is null set @S2 = 0
set @TotalTP2_LoopKWH = @TotalTP2_LoopKWH + @S2

select @S3 = 
(select top(1) [KWH] from [EMS10].[dbo].[EMS_LOG] 
  where [TRX_DATETIME] between convert( datetime, @LoopStartDay + ' '+ @StartTime3) and convert( datetime, @LoopStartDay + ' '+ @EndTime3) and [ID] = @ID order by [TRX_DATETIME] desc)-
(select top(1) [KWH] from [EMS10].[dbo].[EMS_LOG] 
  where [TRX_DATETIME] between convert( datetime, @LoopStartDay + ' '+ @StartTime3) and convert( datetime, @LoopStartDay + ' '+ @EndTime3) and [ID] = @ID order by [TRX_DATETIME])

if @S3 is null set @S3 = 0
set @TotalTP2_LoopKWH_H = @TotalTP2_LoopKWH_H + @S3

set @LoopStartDay = dateadd (day , 1 , @LoopStartDay)

end
select @TotalTP2_LoopKWH as KWH
select @TotalTP2_LoopKWH_H as KWH

目前使用這個方法暫時可以解決問題，
但每一次查詢都要花個幾秒，
我擔心以後如果要查的時間比較長時，
可能會有問題，
所以想請教各位先進，
有什麼地方可以改進的

syunmin iT邦新手 5 級 ‧ 2020-11-20 17:24:34 檢舉

感謝各位先進大大，問題解決了
不好意思，問了一個沒有深度的問題哈哈

syunmin iT邦新手 5 級 ‧ 2020-11-20 17:41:11 檢舉

純真的人 iT邦大師 1 級 ‧ 2020-11-20 23:30:48 檢舉

嗯~解決就好~

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

4 個回答

allenlwh

iT邦高手 1 級 ‧ 2020-11-20 09:09:54

最佳解答

declare @WeekStartDay varchar(10),@StartTime1 varchar(10),@EndTime1 varchar(10)
declare @starttime datetime,@endtime datetime

set @WeekStartDay = '2020/11/18' 
set @StartTime1 = '00:00:00' 
set @EndTime1 = '07:30:00'

set @starttime=convert(datetime,@WeekStartDay+' '+@StartTime1)
set @endtime=convert(datetime,@WeekStartDay+' '+@EndTime1)

select * from [EMS10].[dbo].[EMS_LOG] 
  where [TRX_DATETIME] 
	between @starttime and @endtime

回應
分享
檢舉

登入發表回應

leeihsing0127

iT邦新手 5 級 ‧ 2020-11-19 18:20:06

第二句的 CONVERT 是 date 還是 datetime ?

回應
分享
檢舉

登入發表回應

純真的人

iT邦大師 1 級 ‧ 2020-11-19 21:12:32

試試~有沒有資料~

SELECT TOP(1)[KWH],[ID], [TRX_DATETIME] 
FROM [EMS10].[dbo].[EMS_LOG] 
WHERE [ID] = @ID 
AND CONVERT (date,[TRX_DATETIME]) = @WeekStartDay
AND CONVERT (time,[TRX_DATETIME]) BETWEEN @StartTime1 AND @EndTime1
ORDER BY [TRX_DATETIME] DESC

我測試你的日期時間相加是可以的~

所以你的[TRX_DATETIME] 欄位類型是?

可以先用這樣顯示資料給我們看嗎?

SELECT TOP(10)[KWH],[ID], [TRX_DATETIME] 
FROM [EMS10].[dbo].[EMS_LOG] 
WHERE [ID] = @ID

回應
分享
檢舉

登入發表回應

japhenchen

iT邦超人 1 級 ‧ 2020-11-20 07:48:05

問題應該是出現在
CONVERT(datetime, CONVERT (date, @WeekStartDay)) + ' ' + CONVERT(datetime, @StartTime1)
字串轉日期時間再轉字串相加..
怎麼會有這個腦洞？當日期轉字串，就要看執行SQL句的主機上的國別設定裡的日期時間格式是否有用到"AM PM 上午下午"，如果有，就是錯誤的原因，因為你根本無法判定那台主機是如何把日期時間表示成字串的，除非你有權限登入主機進控制台設定國別

改一下@WeekStartDay,@StartTime1,@EndTime1都當字串處理，相加再轉日期時間就好

DECLARE @S1 INT,@S2 INT,@SS INT,@TOTALKWH INT,@TOTALKWH_Y INT
DECLARE @WeekStartDay varchar(10),@ID VARCHAR,@StartTime1 varchar(8),@EndTime1 varchar(8)

SET @WeekStartDay = '2020/11/18' 
SET @ID = 'PW02_KWH' 
SET @StartTime1 = '00:00:00' 
SET @EndTime1 = '07:30:00'

select 
CONVERT(datetime,@WeekStartDay+' ' +@StartTime1) ,
CONVERT(datetime, @WeekStartDay+' ' +@EndTime1)