quote:

---

Originally posted by gpiva

I have a store proc where I use exec but cannot run correctly
the code it seem correct ...
cannont understand any help will be appreciate...

CREATE PROCEDURE [spDoBooking]
@Tour_ID int,
@StartDate datetime
AS
BEGIN
DECLARE @Departure_Day datetime
DECLARE @Arrival_Day datetime
DECLARE @NewDepDateTime varchar(30)
DECLARE @NewArrDateTime varchar(30)
SET @Departure_Day=@StartDate
/*I select the row specified using coursor....*/
DECLARE @i int
SELECT @i = 1
DECLARE @DT_ID int
DECLARE @MT_ID int
DECLARE @Arrival_Location_ID int
DECLARE @Departure_Location_ID int
DECLARE @Departure_Time datetime
DECLARE @Arrival_Time datetime
DECLARE @NoOfDays int
DECLARE @NoOfDaysTrip int
-- 'INSENSITIVE' allows us to create a copy to work with and not the actual tables...
DECLARE cSnapBooking INSENSITIVE CURSOR
FOR
SELECT dbo.TOURS_DETAILS.DT_ID,
dbo.TOURS_MASTER.MT_ID,
dbo.TOURS_DETAILS.Arrival_Location_ID,
dbo.TOURS_DETAILS.Departure_Location_ID,
dbo.TOURS_DETAILS.Departure_Time,
dbo.TOURS_DETAILS.Arrival_Time,
dbo.TOURS_DETAILS.NoOfDays,
dbo.TOURS_DETAILS.NoOfDaysTrip
FROM dbo.TOURS_MASTER INNER JOIN
dbo.TOURS_DETAILS ON dbo.TOURS_MASTER.MT_ID = dbo.TOURS_DETAILS.MT_ID INNER JOIN
dbo.Locations ON dbo.TOURS_DETAILS.Departure_Location_ID = dbo.Locations.Location_ID INNER JOIN
dbo.Locations Locations_1 ON dbo.TOURS_DETAILS.Arrival_Location_ID = Locations_1.Location_ID
WHERE dbo.TOURS_MASTER.MT_ID = @Tour_ID
FOR READ ONLY
OPEN cSnapBooking
WHILE @i <= @@CURSOR_ROWS
BEGIN
FETCH cSnapBooking INTO @DT_ID,
@MT_ID,
@Arrival_Location_ID ,
@Departure_Location_ID ,
@Departure_Time ,
@Arrival_Time ,
@NoOfDays,
@NoOfDaysTrip
--Generate the real data booking based on the template....
SET @Arrival_Day=DATEADD(day, @NoOfDaysTrip, @Departure_Day)
--print @Departure_Day
--print @Arrival_Day

set @NewDepDateTime = ''''
set @NewDepDateTime = @NewDepDateTime + CONVERT(varchar(10), @Departure_Day , 101) + ' ' + CONVERT(varchar(8), @Departure_Time , 114)
set @NewDepDateTime = @NewDepDateTime + ''''

set @NewDepDateTime = ''''
set @NewArrDateTime = @NewArrDateTime + CONVERT(varchar(10), @Arrival_Day , 101) + ' ' + CONVERT(varchar(8), @Arrival_Time , 114)
set @NewArrDateTime = @NewArrDateTime + ''''
--print @NewDepDateTime
--print @NewArrDateTime

declare @varcharMT_ID varchar(12)
declare @varcharDeparture_Location_ID varchar(12)
declare @varcharArrival_Location_ID varchar(12)
set @varcharMT_ID = convert(varchar(10),@MT_ID)
print @varcharMT_ID
set @varcharDeparture_Location_ID = convert(varchar(10),@Departure_Location_ID)
set @varcharArrival_Location_ID = convert(varchar(10),@Arrival_Location_ID)
exec('INSERT INTO BOOKING_DETAILS (MT_ID,
Departure_Location_ID,
Arrival_Location_ID,
Departure_Time,
Arrival_Time,
NoOfDays)
VALUES(' +CONVERT(varchar(5), @MT_ID) + ','
+CONVERT(varchar(5), @Departure_Location_ID)+ ','
+CONVERT(varchar(5), @Arrival_Location_ID) + ','
+@NewDepDateTime+','
+@NewArrDateTime+',0)')
--EXEC(@StrSql)
--SET @Departure_Day = DATEADD(day, @NoOfDays, @Arrival_Day)
SELECT @i = @i + 1 --increment counter
END
CLOSE cSnapBooking
DEALLOCATE cSnapBooking
END
GO

---

SELECT @strSQL = 'INSERT INTO BOOKING_DETAILS (MT_ID, 
Departure_Location_ID, 
Arrival_Location_ID, 
Departure_Time, 
Arrival_Time, 
NoOfDays) 
VALUES(' +CONVERT(varchar(5), @MT_ID) + ','
+CONVERT(varchar(5), @Departure_Location_ID)+ ','
+CONVERT(varchar(5), @Arrival_Location_ID) + ','
+''''+@NewDepDateTime+''''+','
+''''+@NewArrDateTime+''''+',0)'

EXEC (@strSQL)
to take care of NULLs you would need to substitute something like:
+ COALESCE(''''+@NewArrDateTime+'''', 'NULL') +

CREATE PROCEDURE dbo.spDoBooking
	@Tour_ID	int,
	@StartDate	datetime 
AS
BEGIN
INSERT INTO BOOKING_DETAILS
(
	MT_ID, 
	Departure_Location_ID, 
	Arrival_Location_ID, 
	Departure_Time, 
	Arrival_Time, 
	NoOfDays
) 
SELECT	dbo.TOURS_MASTER.MT_ID, 
	dbo.TOURS_DETAILS.Departure_Location_ID, 
	dbo.TOURS_DETAILS.Arrival_Location_ID, 
	CONVERT(varchar(10), @StartDate , 101) 
		+ ' ' + CONVERT(varchar(8), Departure_Time , 114),
	CONVERT(varchar(10), DATEADD(day, NoOfDaysTrip, @StartDate) , 101) 
		+ ' ' + CONVERT(varchar(8), Arrival_Time , 114),
	0
FROM   dbo.TOURS_MASTER 
       JOIN dbo.TOURS_DETAILS 
           ON dbo.TOURS_MASTER.MT_ID = dbo.TOURS_DETAILS.MT_ID
       JOIN dbo.Locations 
           ON dbo.TOURS_DETAILS.Departure_Location_ID = dbo.Locations.Location_ID
       JOIN dbo.Locations Locations_1 
           ON dbo.TOURS_DETAILS.Arrival_Location_ID = Locations_1.Location_ID
WHERE   dbo.TOURS_MASTER.MT_ID = @Tour_ID
END