InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 01:39:34 GMT20http://www.sqlservercentral.com/Forums/Topic610759-1291-1.aspxI'd guess there's no data for the period in 2008 within the invoices table.Sat, 29 Nov 2008 14:57:07 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic610759-1291-1.aspxNevermind, I got some results with this exec:Exec spDateRange '20060501','20060530'Sat, 29 Nov 2008 14:43:50 GMTt-pintohttp://www.sqlservercentral.com/Forums/Topic610759-1291-1.aspxI 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'Sat, 29 Nov 2008 14:37:45 GMTt-pintohttp://www.sqlservercentral.com/Forums/Topic610759-1291-1.aspx[quote]If @DateMin < @DateMaxBegin[color="#FF0000"]([/color]Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS Balance[/quote]That bracket right after the begin that shouldn't be there.[quote]InvoiceDate < @DateMax+1[/quote]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 thereWhat should happen if Datemin = DateMax? Currently the proc will do nothing if that happens.Sat, 29 Nov 2008 14:06:12 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic610759-1291-1.aspxI 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 = NULLASIf (@DateMin is null) begin Raiserror('Please enter the MinimumDate ',16,1) ReturnEndIf (@DateMax is null) begin Raiserror('Please enter the MaximumDate ',16,1) ReturnEndIf (@DateMin > @DateMax) begin Raiserror('Minimum Date can’t be later then maximum date ',16,1) ReturnEndIf @DateMin < @DateMaxBegin(Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS BalanceFrom InvoicesWhere InvoiceDate >= @DateMin and InvoiceDate < @DateMax+1Order By InvoiceDateEND--Error MessageIncorrect syntax near the keyword 'Order'.Sat, 29 Nov 2008 14:02:27 GMTt-pintohttp://www.sqlservercentral.com/Forums/Topic610759-1291-1.aspx[quote][b]t-pinto (11/29/2008)[/b][hr]If @DateMin < @DateMaxBegin(Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS BalanceFrom InvoicesOrder By InvoiceDateEND[/quote]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)Sat, 29 Nov 2008 13:56:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic610759-1291-1.aspxWhy are you using Varchar's for your dates? why not use Datetime datatype?Sat, 29 Nov 2008 13:23:05 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic610759-1291-1.aspxHello,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 = NULLIf (@DateMin is null) begin Raiserror('Please enter the MinimumDate ',16,1) ReturnIf (@DateMax is null) begin Raiserror('Please enter the MaximumDate ',16,1) ReturnIf (@DateMin < @DateMax) begin Raiserror('Minimum Date can’t be later then maximum date ',16,1) ReturnIf @DateMin < @DateMaxBegin(Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS BalanceFrom InvoicesOrder By InvoiceDateENDExec sp_DateRange 10-1-2008, 10-15-2008Sat, 29 Nov 2008 13:08:41 GMTt-pinto