如何编写存储过程/函数以返回您的日期已存在/输入重复日期 [英] How to Write Stored Procedure/Function for return your dates already exists/Duplicated date entered

问题描述

Suppose I have Two date and I am inserting both i will select 0 if any one date is matches

MyLeave表

用户ID Fromdate ToDate Noofdays

1 6/6/2014 2014年8月6日3

MyLeave Table
Userid Fromdate ToDate Noofdays
1 6/6/2014 8/6/2014 3

when same userid inserting again fromdate as '5/06/2014' Todate as '7/6/2014'   already their in the myleave table,
It should be return already existed or return 0 or any Thing this date

推荐答案

试试这个.. :)

try this.. :)

CREATE PROCEDURE sp_ValidateDate --sp_Name

	@Count int=0,
	@userID int=0,
	@fromDate datetime='',
	@toDate datetime=''

AS
BEGIN
	select @Count=count(*) from MyLeave where fromDate=@fromDate and toDate=@toDate and userID=@userID
	if(@Count>0)
		BEGIN
			SELECT 'Data already exists..!!'
		END
	ELSE 
		BEGIN
			--INSERT OPERATION
			SELECT 'Data inserted successfully..!!'
			
		END
END
GO

最后我得到了解决方案......但是我写的函数没有存储过程

Finally I got Solution... But I have written in Functions not stored procedure

CREATE FUNCTION dbo.isDuplicateLeaveReqFound(@d1 date,@d2 date,@UserId int)
RETURNS bit
AS
BEGIN
    declare @index date,@isDuplicateLeaveReq bit;
    set @isDuplicateLeaveReq=0;
    set @index=@d1

    while(@index<=@d2)
    begin
            if exists(select 1 from Leaverequests where @index>=Fromdate and @index<=Todate and Userid=@UserId)
            begin
            set @isDuplicateLeaveReq=1;
            --print 'Duplicate Leave Request Found'
            --break
            end

    set @index=dateadd(dd,1,@index)
    end


    RETURN @isDuplicateLeaveReq;
END;

这篇关于如何编写存储过程/函数以返回您的日期已存在/输入重复日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！