February 4, 2005 at 3:54 pm
Hi,
I have the following script:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.upCheckSessionExists Script Date: 04/02/2005 21:32:25 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[upCheckSessionExists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[upCheckSessionExists]
GO
/****** Object: Stored Procedure dbo.upCheckSessionExists Script Date: 04/02/2005 21:31:42 ******/
CREATE PROCEDURE [dbo].[upCheckSessionExists]
@intVenue int = Null
,@strDate varchar(12) = Null
,@strMessage varchar(255) = '' OUTPUT
AS
DECLARE @intError int, @intRows int, @strBreak char(2)
SET NOCOUNT ON
-- set variables used in the proc
SET @strBreak = CHAR(10) + CHAR(13) -- line break
-- input checking
IF (@intVenue IS Null) OR (@strDate Is Null)
SET @strMessage = 'Need values for Venue, Date' + @strBreak
SET @intError = ISDATE(@strDate)
IF @intError = 0
SET @strMessage = @strMessage + 'Date is incorrect format and should be dd mmm yyyy eg 30 Jan 2005.' + @strBreak
IF @strMessage <> ''
BEGIN
SET @strMessage = 'ERROR ! ' + @strBreak + @strMessage + 'Command should be:' + @strBreak
SET @strMessage = @strMessage + 'EXEC dbo.upCheckSessionExists VenueID, Date, ''@variable'' OUTPUT'
SET @intError = -1
GOTO CleanExit
END
-- end of input checking
-- check to see if session already exists
SELECT
@strMessage = SessionID
FROM
dbo.tblSessions
WHERE
SessionVenue = @intVenue
AND
SessionDate = CAST(@strDate AS smalldatetime)
SELECT
@intRows = @@RowCount
,@intError = @@Error
IF @intError <> 0
SET @strMessage = 'Error connecting to Sessions table.'
ELSE
BEGIN
IF @intRows = 1
BEGIN
SET @strMessage = 'Session Exists'
RETURN 0 -- carry on with routine based on session exists
END
ELSE
BEGIN
SET @strMessage = 'Session Does Not Exist'
RETURN 10 -- carry on with routine based on session does not exist
END
END
-- routine to pass error message and exit
CleanExit:
RETURN -1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Now, when I run (QA)
DECLARE @strMessage varchar(255), @strResult varchar(2)
EXEC @strResult = dbo.upCheckSessionExists 2, '30 Oct 2004', @strMessage OUTPUT
PRINT @strMessage
PRINT @strResult
Result is:
Session Exists
0
Brilliant.
When I run (QA)
DECLARE @strMessage varchar(255), @strResult varchar(2)
EXEC @strResult = dbo.upCheckSessionExists 2, '30 Oc 2004', @strMessage OUTPUT -- Date deliberatly wrong
PRINT @strMessage
PRINT @strResult
Result is:
Server: Msg 241, Level 16, State 1, Procedure upCheckSessionExists, Line 32
Syntax error converting datetime from character string.
If I debug this stored proc in QA and pass parameters exactly as the error run above ( ie @intValue = 2 and @strDate = 30 Oc 2005
Stepping through the proc, the ISDATE test in the proc picks up the error in date format, and @strMessage indicating this is set.
Why doesn't the ISDATE test pick this up when I call the stored proc outside the debugger. It should return an error stating the date is in the incorrect format and should not return
Server: Msg 241, Level 16, State 1, Procedure upCheckSessionExists, Line 32
Syntax error converting datetime from character string.
February 4, 2005 at 4:23 pm
Are there different connection parameters in effect for the debugger connection ?
Maybe some of the SET options ? (Longshot, but maybe some SET option would cause 'Oc' to be correctly interpretted as October).
February 5, 2005 at 1:41 am
I pass exactly the same parameter values in the debugger.
Also, if I try in QA
DECLARE @strMessage varchar(255), @strResult varchar(2)
EXEC @strResult = dbo.upCheckSessionExists 2, '30 xxx 2004', @strMessage OUTPUT -- Date deliberatly wrong
PRINT @strMessage
PRINT @strResult
Result is:
Server: Msg 241, Level 16, State 1, Procedure upCheckSessionExists, Line 32
Syntax error converting datetime from character string.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply