Isdate function not working

[tman135]

I'm using a stored proc inside Sql 7.0 to add a record to a table taking parameters form an ASP form. I want to check one parameter to ensure it is a valid date. Here's the code..

@inspection_date char(8),
@output_message nvarchar(255) output

as

declare @datecheck int
begin tran addnewsurveillance
--do some error checking

set @datecheck = (select isdate(@inspection_date))
	if @datecheck = 0
	begin
		select @output_message = 'Inspection date is not a valid date format.  Item not added.'
		rollback tran addnewsurveillance
		return
	end

Can't get this to work so the stored proc continues on to the insert statment where it attempts to insert a non-date in the date field and accordingly errors.

Any help would be appreciated

 

[tlbroadbent]

Use the following syntax. Leave out the Select. It is unnecessary.

set @datecheck = isdate(@inspection_date) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[tman135]

Thanks for the suggestion. I implemented it, but the stored proc still doesn't catch the error. Here is the entire stored proc. This must be something obvious that I'm missing.

CREATE PROCEDURE sp_addnewasbestossurveillance
@material_id int,
@inspector nvarchar(50),
@inspection_date varchar(8),
@type nvarchar(100),
@action nvarchar(100),
@contractor nvarchar(50),
@projectnumber nvarchar(50),
@condition nvarchar(20),
@condition_comments nvarchar(255),
@surveillance_comments nvarchar(255),
@output_message nvarchar(255) output
as

declare @error int
declare @datecheck int
begin tran addnewsurveillance

--do some error checking
if @material_id is null
	begin
		select @output_message = 'An error occured regarding key field insertion.  Item not added.'
		rollback tran addnewsurveillance
		return
	end

if exists(select * from tbl_asbestos_surveillance where inspection_date = @inspection_date and material_id = @material_id)
	begin
		select @output_message = 'A surveillance item already exists for this material on this date.  Item not added.'
		rollback tran addnewsurveillance
		return
	end

set @datecheck = isdate(@inspection_date)
	if @datecheck = 0
	begin
		select @output_message = 'Inspection date is not a valid date format.  Item not added.'
		rollback tran addnewsurveillance
		return
	end

insert into tbl_asbestos_surveillance(material_id,inspector,inspection_date,type,action,contractor,projectnumber,condition,condition_comments,surveillance_comments)
		values
			(@material_id,@inspector,@inspection_date,@type,@action,@contractor,@projectnumber,@condition,@condition_comments,@surveillance_comments)
select @error = @@error
	if @error !=0
		begin
			rollback tran addnewsurveillance
			select @output_message = 'An error occured trying to add a surveillance item'
			return
		end
	else
		begin
			commit tran addnewsurveillance
			select @output_message = 'New surveillance item added'
		end

------
And here's the asp error generated on execute of the proc.

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.

Inspection_date is the only smalldatetime field in the table.

 

[tlbroadbent]

The SP is using @inspection_date in a query before it is validated. The red code should follow the green code.

if exists(select * from tbl_asbestos_surveillance where inspection_date = @inspection_date and material_id = @material_id)
begin
select @output_message = 'A surveillance item already exists for this material on this date. Item not added.'
rollback tran addnewsurveillance
return
end

set @datecheck = isdate(@inspection_date)
if @datecheck = 0
begin
select @output_message = 'Inspection date is not a valid date format. Item not added.'
rollback tran addnewsurveillance
return
end Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Glitchbane]

Question for you (all):
Won't the "ROLLBACK TRAN" rollback the "SELECT @output_message ="

I would expect that, if you set a variable inside a transaction, then rollback the transaction, the variable would be rolled back as well. Am I missing something?

 

[tlbroadbent]

The variable content will remain intact upon rollback. Assignment of a value to a variable is not part of the transaction. Transactions record DB updates and assigning a value to a variable doesn't affect the DB data or structure. Variables only exist for the duration of the batch or procedure so recording changes to variables is unnecessary.

I do recommend that the Begin Transaction be moved to the line just before the first update. There is no need for a transaction or rollback prior to that. There will be no change to rollback. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[tman135]

Works like a charm. Thanks for the advice.