December 2, 2008 at 3:25 pm
Hello,
I create a stored procedure and I am attempting to validate my @DateMax and @DateMin parameters. Both parameters have a varchar data type. If the user provides an inappropriate date like: '13/21/08' then I want to raise an error "Invalid Date". I am getting these errors when creating the procedure.
Msg 111, Level 15, State 1, Procedure spDateRange, Line 9
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure spDateRange, Line 14
Incorrect syntax near the keyword 'If'.
Msg 156, Level 15, State 1, Procedure spDateRange, Line 19
Incorrect syntax near the keyword 'If'.
Below is my code. The first 2 rules look at the date format.........Thanks for any help.
USE AP
GO
If Object_ID ('spDateRange') IS NOT NULL
DROP PROC spDateRange
GO
select ISDATE ('5/1/2008')
CREATE PROCspDateRange
@DateMin varchar (50) = NULL,
@DateMax varchar (50) = NULL
AS
If ISDATE(@DateMin) = 0
Raiserror('Please enter valid date format ',11,1)
Return
End
If ISDATE(@DateMax) = 0
Raiserror('Please enter valid date format ',11,1)
Return
End
If (@DateMin is null) Begin
Raiserror('Please enter the MinimumDate ',11,1)
Return
End
If (@DateMax is null) Begin
Raiserror('Please enter the MaximumDate ',11,1)
Return
End
If (@DateMin = @DateMax) Begin
Raiserror('Minimum Date can''t be equal to the maximum date ',11,1)
Return
End
If (@DateMin > @DateMax) Begin
Raiserror('Minimum Date can''t be later then maximum date ',11,1)
Return
End
Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS Balance
From Invoices
Where InvoiceDate Between @DateMin and @DateMax
Order By InvoiceDate
December 2, 2008 at 3:40 pm
Remove the select ISDATE ('5/1/2008') from before the Create Proc
You're missing begins for two of those ifs
If ISDATE(@DateMin) = 0
BEGIN
Raiserror('Please enter valid date format ',11,1)
Return
End
If ISDATE(@DateMax) = 0
BEGIN
Raiserror('Please enter valid date format ',11,1)
Return
End
If (@DateMin is null) Begin
Raiserror('Please enter the MinimumDate ',11,1)
Return
End
If (@DateMax is null) Begin
Raiserror('Please enter the MaximumDate ',11,1)
Return
End
If you want to make the parameters required, remove the defaults from the parameter definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 7:16 pm
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply