By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

ASP Vs Query Analyzer Different Results

P: 1
I am using sql 2005 express and I have a strange proble, when I execute a stored procedure from the query window I get the results I expect, but when I execute it from an asp page then I get a slightly different result.

My stored procedure accepts a start date and a Day of week, it then gets the date of the next date that falls on that day, it is for clinic generation, it does an insert into a clinic table, if I pass in the following params

ClinicPlanID - 4
NoWeeks - 1
StartDate - 24/11//2007
DayName - Monday

This should do an insert into the clinic table with 26/11/2007 which is the next monday - if run from query analyzer this it what happens, but if run from an asp page with those params the date becomes 27/11/2007

Does anyone know why this could be happening, SP code below:

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[clinicGenerate]
  2. @ClinicPlanID INT,
  3. @NoWeeks INT,
  4. @StartDate varchar(10),
  5. @DayName varchar(10)
  6. AS
  7.  
  8. SET NOCOUNT ON
  9. DECLARE @TeamID int
  10. DECLARE @dow INT
  11. DECLARE @Apppurp INT
  12. DECLARE @ClinicType INT
  13. DECLARE @ClinicianID INT
  14. DECLARE @LocationID INT
  15. DECLARE @SexRestriction INT
  16. DECLARE @StartTime datetime
  17. DECLARE @EndTime datetime
  18.  
  19. DECLARE @currdate datetime
  20. DECLARE @currnum INT
  21. DECLARE @ClinicID INT /** newly inserted clinic ID**/
  22.  
  23. BEGIN
  24. /** get the data for insert from the clinic plan **/
  25. SELECT
  26. @TeamID = team_id,
  27. @Dow = dow_id,
  28. @Apppurp = apppurp,
  29. @ClinicType = clinic_type_id,
  30. @ClinicianID = clinician_id,
  31. @LocationID = location_id,
  32. @SexRestriction = sex_restriction_id,
  33. @StartTime = start,
  34. @EndTime = finish
  35. FROM clinic_plan WHERE id = @ClinicPlanID
  36.  
  37. DECLARE @aStartDate datetime
  38.  
  39. /** Convert the passed in start date to an actual datetime **/
  40. SET @aStartDate = CONVERT(datetime, @StartDate, 103)
  41. DECLARE @index int
  42. DECLARE @downame VARCHAR(10)
  43. SET @index = 1
  44.  
  45. /** get the day of week, loop through dates until dates day of week is the same**/
  46. WHILE (@index < 7)
  47. BEGIN
  48.     /** get the text of the day of week for the aStartDate **/
  49.     SET @downame = CASE DATEPART(dw, @aStartDate)
  50.         WHEN 1 THEN 'Sunday'
  51.         WHEN 2 THEN 'Monday'
  52.         WHEN 3 THEN 'Tuesday'
  53.         WHEN 4 THEN 'Wednesday'
  54.         WHEN 5 THEN 'Thursday'
  55.         WHEN 6 THEN 'Friday'
  56.         WHEN 7 THEN 'Saturday'
  57.     END
  58.  
  59.     /** if the current day of week in the loop matches the param day of week the break
  60.     **     then the next date to generate from is in aStartDate
  61.     **/
  62.     IF (@downame = @DayName)
  63.         BREAK
  64.     ELSE
  65.         SET @aStartDate = DATEADD(d, 1, @aStartDate)
  66.         SET @index = @index + 1
  67.         CONTINUE
  68. /** set the generation start date to the next date on which the day passed in falls **/
  69. END
  70. /** Set the currnum to 1 - loop counter**/
  71. SELECT @currnum = 1
  72.  
  73. /** Convert date **/
  74. /** SELECT @currdate = CONVERT(datetime, @StartDate, 103) **/
  75. SET @currdate = @aStartDate
  76. END
  77.  
  78. BEGIN TRAN
  79.  
  80. /** While currtime less than endtime loop **/
  81. WHILE (@currnum <= @NoWeeks)
  82. BEGIN
  83.     /** Do Insert**/
  84.     INSERT INTO clinic(clinic_type_id, team_id, location_id, lead_clinician_id, sex_restriction_id, clinic_date, start_time, end_time, clinic_of_day, slot_purpose, clinic_plan_id)
  85.     VALUES (@ClinicType, @TeamID, @LocationID, @ClinicianID, @SexRestriction, @currdate, @StartTime, @EndTime, @Dow, @Apppurp, @ClinicPlanID)
  86.  
  87.     /** get the new id of the inserted clinic for use inserting the slot plans **/
  88.     SET @ClinicID = SCOPE_IDENTITY()
  89.  
  90.     /** insert the slots from the slot plan into the slots table **/
  91.     INSERT INTO slot(clinic_id, slot_time, team_id, dow, apppurp, clinician_id)
  92.     SELECT @ClinicID, slot_time, team_id, dow, apppurp, clinician_id FROM slot_plan WHERE clinic_plan_id = @ClinicPlanID
  93.  
  94.     IF(@currnum >= @NoWeeks)
  95.         BREAK
  96.     ELSE
  97.         /** Increment the currtime by the minsperslot **/
  98.         SELECT @currdate = DATEADD(week, 1, @currdate)
  99.         SELECT @currnum  = @currnum + 1
  100.         CONTINUE
  101. END
  102.  
  103. IF @@ERROR<>0
  104. BEGIN
  105.     ROLLBACK TRAN
  106.     RAISERROR('generateSlots - Error creating Clinic', 16, 1)
  107.     RETURN -3
  108. END
  109.  
  110. COMMIT
  111.  
Nov 24 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.