?
Solved

Need help with writing the stored procedure

Posted on 2009-02-09
11
Medium Priority
?
225 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:TECH_NET
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 23597740

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
 

Author Comment

by:TECH_NET
ID: 23597922
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
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 23597934
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Expert Comment

by:r_vignesh
ID: 23597963
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
 
LVL 2

Expert Comment

by:r_vignesh
ID: 23597971
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
 

Author Comment

by:TECH_NET
ID: 23601063
Incorrect syntax near the keyword 'CASE'.
and i has to declare

DECLARE @TRAVEL_DATE datetime
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 23601875
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
 

Author Comment

by:TECH_NET
ID: 23602547
i get
" Incorrect syntax near the keyword 'ELSE'."
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23602681
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
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 23603604
Put the code you are using, if you put more than one sentence you must enclose it between begin and end
0
 
LVL 2

Accepted Solution

by:
r_vignesh earned 2000 total points
ID: 23608661
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question