MSSQL Date Function! Creating Function 1st Time...

In this  function im checking date, if  web form submitted date less then current date and start date with End date.
please find function code below!

please help me how to write in storedprocedure.


CREATE FUNCTION myDateCompare(@startDate DateTime, @EndDate DateTime)
RETURNS @resultDate 
AS
BEGIN

WITH datesFunc(date1, date2) AS
(
SELECT  CAST(date1 AS DATETIME),
       CAST(date2 AS DATETIME)
)


SELECT CASE WHEN CAST(@startDate AS DATE) <= CAST(@EndDate AS DATE) THEN 'Start Date Cannot Start Before End Date' ELSE 
SELECT CASE WHEN CAST(@startDate AS DATE) <= CAST(getDate AS DATE) THEN 'Start date cannot be less than current date' ELSE 'Y' END
FROM datesFunc

   RETURN;
END;

Open in new window

xeondxbAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Instead of using a function in T-SQL you should use

1. CHECK CONSTRAINT


Use a strict model on database level.
CREATE TABLE [dbo].[ADDEvent]
(
	EventType VARCHAR(50),
	EventNumber VARCHAR(50),
	EventTitle1 VARCHAR(50),
	EventTitle2 VARCHAR(50),
	EventBase VARCHAR(50),
	EventStartDt DATE NOT NULL,
	EventEndDt DATE NOT NULL,
	CONSTRAINT CK_dbo_ADDEvent_StartEndDtRange CHECK ( EventStartDt <= EventEndDt )
);

Open in new window

btw, post your table DDL. The use of VARCHAR's in the SP makes no sense.

2. Input Validation


To avoid unnecessary database round-trips, validate the entered information also in your upper, front-end layers.
0
 
Dale BurrellDirectorCommented:
What do you want it as a SP? Surely a function is more practical?

As an SP do you want to return the result as a parameter as a result set or as the return value?
0
 
xeondxbAuthor Commented:
before inserting in database i just want to use this function so less code on application side
if my function send any error date, for Example:  if date is already passed or start date greater then End date then it will not perform inserting command. send message to application.

USE [Registration]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ADDEvent]
    @EventType varchar(50),
	@EventNumber varchar(50),
	@EventTitle1 varchar(50),
	@EventTitle2 varchar(50),
	@EventBase varchar(50),
	@EventStartDt varchar(50),
	@EventEndDt varchar(50),
	@Status	 varchar(50) OUTPUT


AS 

BEGIN

insert into tbl_EventCategory
	(eventType,eventNumber,eventTitle1,eventTitle2,eventBase,eventStartDt, eventEndDt)
values
	(@EventType,@EventNumber,@EventTitle1,@EventTitle2,@EventBase,@EventStartDt,@EventEndDt)
END

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Dale BurrellDirectorCommented:
In that case just call your function from within your SP - that avoids duplicating code.

So something like

if (select dbo.myDateCompare(@EventStartDt, @EventEndDt)) = 'Y' begin
  insert into ...
end

Open in new window

0
 
xeondxbAuthor Commented:
that is right but my main issue is my function script giving error can u have a look my function script ... if you can help me ...

Thanks ...
0
 
QlemoConnect With a Mentor DeveloperCommented:
If all you want to get is the result of the check:
CREATE FUNCTION myDateCompare(@startDate DateTime, @EndDate DateTime)
RETURNS @resultDate 
AS
BEGIN
  return case
     when @startDate <= @EndDate then 'Start Date Cannot Start Before End Date' 
     when @startDate <= getdate() then 'Start date cannot be less than current date'
     else 'Y'
     end
END

Open in new window

0
 
Dale BurrellConnect With a Mentor DirectorCommented:
RETURNS @resultDate

should be

RETURNS varchar(1024) -- Or whatever type you are trying to return.
0
All Courses

From novice to tech pro — start learning today.