如果存在，则从表中选择并插入到#temp，否则插入到#temp，选择#temp [英] if exists select from table and insert to #temp, else insert to #temp, select #temp

问题描述

dbo.usp_employee_history_fillDates
@employee_id UNIQUEIDENTIFIER
, @date AS DATETIME
AS
BEGIN
	DECLARE @empID UNIQUEIDENTIFIER
	DECLARE @day DATETIME
	
	SET @empID = @employee_id
	SET @day = @date

	IF EXISTS(SELECT history_id FROM employee_history WHERE employee_id = @employee_id AND CONVERT(VARCHAR(24),employee_timeIn,107) = @date)
	BEGIN
		SELECT
		el.employee_id
		, e.history_id
		, CONVERT(VARCHAR(24),e.employee_timeIn,107) AS rawDate
		, e.employee_workHours
		, e.dayType
		INTO #temp
		FROM employee_list el
		INNER JOIN employee_history e ON e.employee_id = el.employee_id

		WHERE e.employee_id = @employee_id
		AND CONVERT(VARCHAR(24),e.employee_timeIn,107) = @date
		AND el.delete_status = 0 
		AND isActive = '1'   
		AND e.delete_status = 0           
		ORDER BY e.employee_timeIn ASC
	END
	ELSE
	BEGIN
		INSERT INTO #temp
		(
		el.employee_id
		, e.history_id
		, rawDate
		, e.employee_workHours
		, e.dayType
		)
		VALUES
		(
		@empID
		, '00000000-0000-0000-0000-000000000000'
		, @day
		, ''
		, ''
		)
	END
	SELECT * FROM #temp
END

没有错误，但如果不存在，则不会插入数据.
在此先谢谢您.

No error but if does not exists, data does not insert.
Thanks in advance.

推荐答案

可能是您的数据类型和转换所致:

Probably, the problem is your datatypes and conversions:

@date AS DATETIME
...
CONVERT(VARCHAR(24),employee_timeIn,107) = @date

我不确定您为什么要转换显示的内容在将其分隔为DateTime值之前，先将其作为Varchar的Date或DateTime列，但我知道我不会-我将直接比较DataTime cvlaues.

列格式是什么?以及为什么您认为在比较之前将其转换为Varchar是一个好主意-因为有很多方法可能会出错.

I''m not sure why you are converting what appears to be a Date or DateTime column to a Varchar before you comparte it to a DateTime value, but I know I wouldn''t - I would compare the DataTime cvlaues directly.

What is the column format? And why do you think it is a good idea to convert it to a Varchar before the comparison - because there are so many ways that that could go wrong.

尝试一下此

Try this

DECLARE @empID UNIQUEIDENTIFIER
DECLARE @day DATETIME
	
SET @empID = @employee_id
SET @day = @date

SELECT
	el.employee_id
	, e.history_id
	, CONVERT(VARCHAR(24),e.employee_timeIn,107) AS rawDate
	, e.employee_workHours
	, e.dayType
INTO #temp
FROM employee_list el
INNER JOIN employee_history e ON e.employee_id = el.employee_id
WHERE e.employee_id = @employee_id
	AND CONVERT(VARCHAR(24),e.employee_timeIn,107) = @date
	AND el.delete_status = 0 
	AND isActive = '1'   
	AND e.delete_status = 0           
ORDER BY e.employee_timeIn ASC

IF @@ROWCOUNT = 0
	BEGIN
	INSERT INTO #temp
	(
	employee_id
	, history_id
	, rawDate
	, employee_workHours
	, dayType
	)
	VALUES
	(
	@empID
	, '00000000-0000-0000-0000-000000000000'
	, @day
	, ''
	, ''
	)
	END

SELECT * FROM #temp

这篇关于如果存在，则从表中选择并插入到#temp，否则插入到#temp，选择#temp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！