Need help with writing the stored procedure

I have a a stored procedure that passes 4 input parameters of which two happens to be the date columns ie START_DATE. and END_DATE

I want to use the insert a records for all the dates that fall in the range for START_DATE and END_DATE and in addition i need to compare the TRAVEL_FREQUENCY_ID input paramter to filter out the dates in the range to fall in weekdays and weekends. I wrote the Pseudo code from what i know, can anyone help me complete the stored procedure.

Thanks
CREATE PROCEDURE ProcessTravelDateRequests
(
@START_DATE datetime,
@END_DATE  datetime,
@TRAVEL_FREQUENCY_ID int,
@REQUEST_ID int
 
)
 
AS
 
 
-- Insert data into the table based on the travel_frequency
IF @TRAVEL_FREQUENCY_ID=1 'Daily
	@TRAVEL_DATE = @START_DATE
   'Insert records for all the dates in this range ie if 4 days is the range between the START_DATE and END_DATE, then execute the INSERT STATEMENT 4 times with the TRAVEL_DATES
	INSERT INTO TABLE TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE) VALUES (@REQUEST_ID,@TRAVEL_DATE)
 
ELSE IF @TRAVEL_FREQUENCY_ID=2 'Weekday
 'Insert records for all the dates in this range ie if 4 days is the range between the START_DATE and END_DATE, then execute the INSERT STATEMENT Based on whether the 4 days identified are Weekdays. (the travel dates inserted should be MONDAY THRU FRIDAY)
 
ELSE IF @TRAVEL_FREQUENCY_ID=3 'Weekend
 'Insert records for all the dates in this range ie if 4 days is the range between the START_DATE and END_DATE, then execute the INSERT STATEMENT Based on whether the 4 days identified are Weekend. The travel dates inserted should be a SATURDAY OR A SUNDAY
 
ELSE
	'Insert the record
	INSERT INTO TABLE TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE) VALUES (@REQUEST_ID,@START_DATE)

Open in new window

TECH_NETAsked:
Who is Participating?
 
r_vigneshCommented:
Hope This Snippet will be useful.
DECLARE @TRAVEL_DATE DATETIME
DECLARE @START_DATE DATETIME
DECLARE @END_DATE DATETIME
DECLARE @TRAVEL_FREQUENCY_ID INT
DECLARE @TRAVEL_REQUESTS TABLE
(REQUESTID INT IDENTITY(1,1),
 TRAVEL_DATE DATETIME)
SET @START_DATE = GETDATE()
SET @END_DATE = DATEADD(DAY,7,@START_DATE)
SET @TRAVEL_FREQUENCY_ID = 2
 
SET @TRAVEL_DATE = @START_DATE
WHILE @TRAVEL_DATE <= @END_DATE
	BEGIN
	   		IF (@TRAVEL_FREQUENCY_ID = 1) --Daily
				INSERT INTO @TRAVEL_REQUESTS(TRAVEL_DATE) VALUES (@TRAVEL_DATE)
			ELSE IF ((@TRAVEL_FREQUENCY_ID = 2) AND (NOT DATENAME(WEEKDAY, @TRAVEL_DATE) IN ('Saturday', 'Sunday'))) 
				INSERT INTO @TRAVEL_REQUESTS(TRAVEL_DATE) VALUES (@TRAVEL_DATE)
			ELSE IF ((@TRAVEL_FREQUENCY_ID = 3) AND (DATENAME(WEEKDAY, @TRAVEL_DATE) IN ('Saturday', 'Sunday')))  
				INSERT INTO @TRAVEL_REQUESTS(TRAVEL_DATE) VALUES (@TRAVEL_DATE)
			SET @TRAVEL_DATE = DATEADD(DAY,1,@TRAVEL_DATE)
	END
 SELECT * FROM @TRAVEL_REQUESTS

Open in new window

0
 
jorge_torizResearch & Development ManagerCommented:

WHILE @START_DATE < @END_DATE
BEGIN
   CASE
		WHEN @TRAVEL_FREQUENCY_ID = 1 --Daily
			--YOUR INSERT STATEMENT FOR DAILY
		WHEN @TRAVEL_FREQUENCY_ID = 2 AND NOT DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday')
			--YOUR INSERT STATEMENT FOR WEEK DAYS
		WHEN @TRAVEL_FREQUENCY_ID = 3 AND DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday')
			--YOUR INSERT STATEMENT FOR WEEKEND DAYS
		ELSE
			--YOUR INSERT STATEMENT FOR ELSE
	END
END

Open in new window

0
 
TECH_NETAuthor Commented:
I get an error "Incorrect syntax near keyword 'CASE'

Here is my sp.

CREATE PROCEDURE dbo.ProcessTravelDateRequests

(
@START_DATE datetime,
@END_DATE  datetime,
@TRAVEL_FREQUENCY_ID int,
@REQUEST_ID int
)
 
AS

WHILE @START_DATE < @END_DATE
BEGIN
   CASE
            WHEN  @TRAVEL_FREQUENCY_ID =1  THEN
                  --YOUR INSERT STATEMENT FOR DAILY
            WHEN   @TRAVEL_FREQUENCY_ID =2 AND NOT DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday') THEN
                  --YOUR INSERT STATEMENT FOR WEEK DAYS
            
            WHEN   @TRAVEL_FREQUENCY_ID =3 AND DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday') THEN
                  --YOUR INSERT STATEMENT FOR WEEKEND DAYS
             
            ELSE
                  --YOUR INSERT STATEMENT FOR ELSE
      END
END
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jorge_torizResearch & Development ManagerCommented:
Sorry:
CREATE PROCEDURE dbo.ProcessTravelDateRequests
 
(
@START_DATE datetime,
@END_DATE  datetime,
@TRAVEL_FREQUENCY_ID int,
@REQUEST_ID int
)
 
AS
 
WHILE @START_DATE < @END_DATE
BEGIN
   IF @TRAVEL_FREQUENCY_ID =1
      --YOUR INSERT STATEMENT FOR DAILY
   ELSE IF @TRAVEL_FREQUENCY_ID =2 AND NOT DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday')
      --YOUR INSERT STATEMENT FOR WEEK DAYS
   ELSE IF @TRAVEL_FREQUENCY_ID =3 AND DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday')
      --YOUR INSERT STATEMENT FOR WEEKEND DAYS
   ELSE
      --YOUR INSERT STATEMENT FOR ELSE
 
   SET @StartDate = DATEADD(DAY, 1, @StartDate)
END

Open in new window

0
 
r_vigneshCommented:
Hope this helps
SET @TRAVEL_DATE = @START_DATE
WHILE @TRAVEL_DATE < @END_DATE
	BEGIN
	   CASE
			WHEN @TRAVEL_FREQUENCY_ID = 1 THEN --Daily
				INSERT INTO TABLE TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE) VALUES (@REQUEST_ID,@TRAVEL_DATE)
			WHEN @TRAVEL_FREQUENCY_ID = 2 AND NOT DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday') THEN 
				INSERT INTO TABLE TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE) VALUES (@REQUEST_ID,@TRAVEL_DATE)
			WHEN @TRAVEL_FREQUENCY_ID = 3 AND DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday') THEN 
				INSERT INTO TABLE TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE) VALUES (@REQUEST_ID,@TRAVEL_DATE)
			ELSE
				--YOUR INSERT STATEMENT FOR ELSE
		END
		SET @TRAVEL_DATE = DATEADD(DAY,1,@TRAVEL_DATE)
	END
 

Open in new window

0
 
r_vigneshCommented:
OOPS, No need for table Keyword
SET @TRAVEL_DATE = @START_DATE
WHILE @TRAVEL_DATE < @END_DATE
	BEGIN
	   CASE
			WHEN @TRAVEL_FREQUENCY_ID = 1 THEN --Daily
				INSERT INTO TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE) VALUES (@REQUEST_ID,@TRAVEL_DATE)
			WHEN @TRAVEL_FREQUENCY_ID = 2 AND NOT DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday') THEN 
				INSERT INTO TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE) VALUES (@REQUEST_ID,@TRAVEL_DATE)
			WHEN @TRAVEL_FREQUENCY_ID = 3 AND DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday') THEN 
				INSERT INTO TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE) VALUES (@REQUEST_ID,@TRAVEL_DATE)
			ELSE
				--YOUR INSERT STATEMENT FOR ELSE
		END
		SET @TRAVEL_DATE = DATEADD(DAY,1,@TRAVEL_DATE)
	END
 

Open in new window

0
 
TECH_NETAuthor Commented:
Incorrect syntax near the keyword 'CASE'.
and i has to declare

DECLARE @TRAVEL_DATE datetime
0
 
jorge_torizResearch & Development ManagerCommented:
Have you tried my last code?

CREATE PROCEDURE dbo.ProcessTravelDateRequests
 
(
@START_DATE datetime,
@END_DATE  datetime,
@TRAVEL_FREQUENCY_ID int,
@REQUEST_ID int
)
 
AS
 
WHILE @START_DATE < @END_DATE
BEGIN
   IF @TRAVEL_FREQUENCY_ID =1
      --YOUR INSERT STATEMENT FOR DAILY
   ELSE IF @TRAVEL_FREQUENCY_ID =2 AND NOT DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday')
      --YOUR INSERT STATEMENT FOR WEEK DAYS
   ELSE IF @TRAVEL_FREQUENCY_ID =3 AND DATENAME(WEEKDAY, GETDATE()) IN ('Saturday', 'Sunday')
      --YOUR INSERT STATEMENT FOR WEEKEND DAYS
   ELSE
      --YOUR INSERT STATEMENT FOR ELSE
 
   SET @StartDate = DATEADD(DAY, 1, @StartDate)
END
0
 
TECH_NETAuthor Commented:
i get
" Incorrect syntax near the keyword 'ELSE'."
0
 
Scott PletcherSenior DBACommented:
First, create a sequential numbers table (of course that only has to be done once; code to create the table is given below).  That way, a single SELECT can do all the INSERTs for you.  

Then, you can do this:

INSERT INTO TRAVEL_REQUESTS(REQUEST_ID,TRAVEL_DATE)
SELECT @REQUEST_ID,DATEADD(DAY, seqNum, @START_DATE)
FROM seqNums
WHERE seqNum BETWEEN 0 AND DATEDIFF(DAY, @START_DATE, @END_DATE)
AND (@TRAVEL_FREQUENCY_ID NOT IN (2, 3)) --if @TRAVEL_FREQUENCY_ID is not 2 or 3, insert for every day
 OR (@TRAVEL_FREQUENCY_ID = 2 AND DATENAME(WEEKDAY, DATEADD(DAY, seqNum, @START_DATE)) NOT IN ('Saturday', 'Sunday')) --NOTE the "NOT": this inserts Monday thru Friday only
 OR (@TRAVEL_FREQUENCY_ID = 3 AND DATENAME(WEEKDAY, DATEADD(DAY, seqNum, @START_DATE)) IN ('Saturday', 'Sunday'))

DECLARE @maxValueToGenerate INT
 
-- chg next value to the highest value you need; 
-- the current code supports up to 100M - 1, but you could increase that if you wanted to
 
SET @maxValueToGenerate = 16000
 
IF OBJECT_ID('seqNums') IS NOT NULL
    DROP TABLE seqNums
 
CREATE TABLE seqNums (
    seqNum INT,
    CONSTRAINT seqNums_CI --don't remove, improves performance!
        UNIQUE CLUSTERED (seqNum) WITH FILLFACTOR = 100
    )
 
INSERT INTO seqNums
SELECT [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + 
	[1Ms] + [10Ms] /*+ [100Ms]*/
FROM (
    SELECT 0 AS [1s] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS digits
CROSS JOIN (
    SELECT 00 AS [10s] UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL 
    SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL 
    SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) AS [10s]
CROSS JOIN (
	SELECT [100s]
	FROM (
    SELECT 000 AS [100s] UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL 
    SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL 
    SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
	) inlineData
	WHERE [100s] <= @maxValueToGenerate
) AS [100s]
CROSS JOIN (
	SELECT [1000s]
	FROM (
    SELECT 0000 AS [1000s] UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL 
    SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL 
    SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
	) inlineData
	WHERE [1000s] <= @maxValueToGenerate
) AS [1000s]
CROSS JOIN (
	SELECT [10Ks]
	FROM (
    SELECT 00000 AS [10Ks] UNION ALL SELECT 10000 UNION ALL SELECT 20000 UNION ALL 
    SELECT 30000 UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000 UNION ALL 
    SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
	) inlineData
	WHERE [10Ks] <= @maxValueToGenerate
) AS [10Ks]
CROSS JOIN (
	SELECT [100Ks]
	FROM (
    SELECT 000000 AS [100Ks] UNION ALL SELECT 100000 UNION ALL SELECT 200000 UNION ALL 
    SELECT 300000 UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000 UNION ALL 
    SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
	) inlineData
	WHERE [100Ks] <= @maxValueToGenerate
) AS [100Ks]
CROSS JOIN (
	SELECT [1Ms]
	FROM (
    SELECT 0000000 AS [1Ms] UNION ALL SELECT 1000000 UNION ALL SELECT 2000000 UNION ALL 
    SELECT 3000000 UNION ALL SELECT 4000000 UNION ALL SELECT 5000000 UNION ALL SELECT 6000000 UNION ALL 
    SELECT 7000000 UNION ALL SELECT 8000000 UNION ALL SELECT 9000000
	) inlineData
	WHERE [1Ms] <= @maxValueToGenerate
) AS [1Ms]
CROSS JOIN (
	SELECT [10Ms]
	FROM (
    SELECT 00000000 AS [10Ms] UNION ALL SELECT 10000000 UNION ALL SELECT 20000000 UNION ALL 
    SELECT 30000000 UNION ALL SELECT 40000000 UNION ALL SELECT 50000000 UNION ALL SELECT 60000000 UNION ALL 
    SELECT 70000000 UNION ALL SELECT 80000000 UNION ALL SELECT 90000000
	) inlineData
	WHERE [10Ms] <= @maxValueToGenerate
) AS [10Ms]
/*
CROSS JOIN (
	SELECT [100Ms]
	FROM (
    SELECT 000000000 AS [100Ms] UNION ALL SELECT 100000000 UNION ALL SELECT 200000000 UNION ALL 
    SELECT 300000000 UNION ALL SELECT 400000000 UNION ALL SELECT 500000000 UNION ALL SELECT 600000000 UNION ALL 
    SELECT 700000000 UNION ALL SELECT 800000000 UNION ALL SELECT 900000000
	) inlineData
	WHERE [100Ms] <= @maxValueToGenerate
) AS [100Ms]
*/
WHERE [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms] /*+ [100Ms]*/ <= @maxValueToGenerate
ORDER BY [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms] /*+ [100Ms]*/
 
DBCC SHOWCONTIG(seqNums) --make sure table is highly contig for max performance
 
SELECT COUNT(*) FROM seqNums WITH (NOLOCK) --verify that the expected number of rows were generated

Open in new window

0
 
jorge_torizResearch & Development ManagerCommented:
Put the code you are using, if you put more than one sentence you must enclose it between begin and end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.