SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored procedure- help with date parameter validation


Stored procedure- help with date parameter validation

Author
Message
t-pinto
t-pinto
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 51
Hello,

I am creating a stored procedure that accepts two parameters,
@DateMin and @DateMax, ea.with data type varchar and default value null.

1.If called with no parameters or with null values, the procedure should return an error explaining the needed parameters.
2. If called with non-null values, I need to validate the parameters to ensure that the @DateMin is earlier than @DateMax, if valid return the result set, if invalid then display an error message.

This is what I have so far. I am stuck on how to proceed. Do I need Print statements?
Thanks for any help.

CREATE PROC sp_DateRange
@DateMin varchar = NULL
@DateMax varchar = NULL

If (@DateMin is null)
begin
Raiserror('Please enter the MinimumDate ',16,1)
Return

If (@DateMax is null)
begin
Raiserror('Please enter the MaximumDate ',16,1)
Return
If (@DateMin < @DateMax)
begin
Raiserror('Minimum Date can’t be later then maximum date ',16,1)
Return

If @DateMin < @DateMax
Begin
(Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS Balance
From Invoices
Order By InvoiceDate
END


Exec sp_DateRange 10-1-2008, 10-15-2008
RBarryYoung
RBarryYoung
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23786 Visits: 9518
Why are you using Varchar's for your dates? why not use Datetime datatype?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146867 Visits: 45860
t-pinto (11/29/2008)

If @DateMin < @DateMax
Begin
(Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS Balance
From Invoices
Order By InvoiceDate
END


The rest looks good (other than the varchar types)

What do you want to do with the resultset and the dates? I assume they should limit the returned rows. How?

Also, regarding varchar, DECLARE @SomeVar varchar will get you a varchar with length 1. Always define the length with a varchar columns. Helps avoid surprises.
I'd also suggest you pass the dates in an unambiguous form - yyyy-mm-dd, because the interpretation of the one you used differs in different places. Where I live, the two dates you passed are the 10th of January and invalid (there is no 15th month)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


t-pinto
t-pinto
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 51
I made some modifications to the code but cont to get an error message when creating the procedure. Here is the latest code and my error.

CREATE PROC sp_DateRange
@DateMin DateTime = NULL,
@DateMax DateTime = NULL
AS

If (@DateMin is null)
begin
Raiserror('Please enter the MinimumDate ',16,1)
Return
End


If (@DateMax is null)
begin
Raiserror('Please enter the MaximumDate ',16,1)
Return
End

If (@DateMin > @DateMax)
begin
Raiserror('Minimum Date can’t be later then maximum date ',16,1)
Return
End

If @DateMin < @DateMax
Begin
(Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS Balance
From Invoices
Where InvoiceDate >= @DateMin and InvoiceDate < @DateMax+1
Order By InvoiceDate
END

--Error Message
Incorrect syntax near the keyword 'Order'.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146867 Visits: 45860
If @DateMin < @DateMax
Begin
(Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS Balance

That bracket right after the begin that shouldn't be there.

InvoiceDate < @DateMax+1

Meaning 1 day later than the datemax that the user specified?

You shouldn't name procs sp_. That's for system stored procs and means that SQL will look first in one of the system databases to see if your proc is there

What should happen if Datemin = DateMax? Currently the proc will do nothing if that happens.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


t-pinto
t-pinto
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 51
I modified the parameter datatypes, stored procedure naming convention and added a rule to take into account the mindate = max date.

The procedure was created without errors.

If the parameters are valid I would like to return a result set that includes the InvoiceNumber, InvoiceDate, InvoiceTotal and Balance for each invoice for which the InvoiceDate is within the date range, sorted with the earliest invoice first.

I executed my procedure as follows with 2 date parameters but did not get any results.
exec spDateRange '2008-07-01', '2008-08-01'
t-pinto
t-pinto
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 51
Nevermind, I got some results with this exec:
Exec spDateRange '20060501','20060530'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146867 Visits: 45860
I'd guess there's no data for the period in 2008 within the invoices table.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


srvfanx
srvfanx
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
I had the same problem but was supposed to have 5 row(s) affected & the command ran successfully - but with 0 row(s) affected....:o(
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search