Cecil,

I see a few problems.

1. In your exists check you don't need to use ORDER BY -- it's going to be ignored anyway, but somehow obscures the problem

2. After IF NOT EXISTS you may want

     begin
        raiserror('There are no invoices in the specified date range: %s %s',16,1, @DateMin, @DateMax) -- verify syntax for Raiserror
        return -4 -- this is also a bad case
   end

I also don't see much point in TRY/CATCH in the code that executes the SP.

BTW, check your other topic (Dynamic SQL) as well.


>I know that I am having a problem where I use the RAISERROR Function. This was working until I put in RAISERROR(). We are required to use RAISERROR() and TRY / CATCH in the calling SQL Script of the below stored procedure. I am not sure what I should do at this point.
>
>This part might be a problem:
>
>

>IF NOT EXISTS (SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
>		(InvoiceTotal - CreditTotal - PaymentTotal) AS Balance
>	FROM Invoices
>	WHERE InvoiceDate BETWEEN @DateMin AND @DateMax AND
>		(InvoiceTotal - CreditTotal - PaymentTotal) > 0
>	ORDER BY InvoiceDate)
>	
>	RAISERROR ('Invalid something here', 11, 1))
>

>
>Here's the whole enchilada (Stored Procedure) and also its calling script:
>

>-- 1. Create a stored procedure named spDateRange that accepts two
>-- parameters, @DateMin and @DateMax, with data type VARCHAR and
>-- default value null.
>--
>-- If called with no parameters or with null values, the procedure
>-- should return an error message describing the syntax.
>-- RAISERROR and TRY…CATCH must be used.
>
>-- Write a script to call the procedure spDateRange.
>-- If called with non-null values, validate the parameters.
>-- Test that the literal strings are valid date ranges and
>-- test that @DateMin is earlier than @DateMax.
>--
>-- If the parameters are valid, 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 earliest invoice first.
>--
>-- The result set should contain invoices with balance only.
>--
>-- Use the following test data in the calling program to produce
>-- 4 results:
>USE AP
>GO
>
>ALTER PROCEDURE spDateRange
>	@DateMin VARCHAR(20) = NULL,
>	@DateMax VARCHAR(20) = NULL
>AS
>
>-- Declare Error Variables.
>DECLARE @NullDateError INT,
>	@BadDateError INT,
>	@MinDateLargerThanMaxDateError INT
>
>-- Initialize Error Variables.
>SELECT 
>	@NullDateError = 1,
>	@BadDateError  = 2,
>	@MinDateLargerThanMaxDateError = 3
>	
>-- Test for one or both values containing NULL. IF either value
>-- contains NULL, there is no way to continue this stored procedure.
>-- If any of the following errors occurs, the stored procedure stops.
>IF @DateMin IS NULL OR @DateMax IS NULL
>	RETURN @NullDateError
>
>IF ISDATE(@DateMin) = 0 OR ISDATE(@DateMax) = 0
>	RETURN @BadDateError
>
>IF CAST(@DateMin AS DATETIME) > CAST(@DateMax AS DATETIME)
>	RETURN @MinDateLargerThanMaxDateError
>
>-- If you've gotten this far, then the MIN/MAX dates are good.
>IF NOT EXISTS (SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
>		(InvoiceTotal - CreditTotal - PaymentTotal) AS Balance
>	FROM Invoices
>	WHERE InvoiceDate BETWEEN @DateMin AND @DateMax AND
>		(InvoiceTotal - CreditTotal - PaymentTotal) > 0
>	ORDER BY InvoiceDate)
>	
>	RAISERROR ('Invalid something here', 11, 1))
>
>-----------------------------------------------------------
>-- T-SQL SCRIPT to Call the Stored Procedure, spDateRange.
>-----------------------------------------------------------
>DECLARE @Return INT
>BEGIN TRY
>	EXEC @Return = dbo.spDateRange '07/40/08', '08/01/08' -- Invalid Date.
>	IF @Return = 0
>		BEGIN
>			PRINT 'Successful! Invoice Balances were found!'
>		END
>END TRY
>
>BEGIN CATCH
>	PRINT 'An error occurred.'
>	PRINT 'Message: ' + ERROR_MESSAGE()
>
>	IF @Return = 1
>		PRINT 'INVALID DATE! One or both dates had a NULL value.'
>
>	IF @Return = 2
>		PRINT 'INVALID DATE: One, or both, of the date parameters is invalid.'
>
>	IF @Return = 3
>		PRINT 'Parameter [@DateMin] was greater than [@DateMax].'
>END CATCH
>----------------------------------------------------------------------------
>-- Other calls that can be used to demonstrate all of the above scenarios:
>EXEC spDateRange NULL, '08/01/08'		-- Test for one value containing NULL.
>EXEC spDateRange '08/10/08', '07/10/08' -- Test for Min Date larger than Max Date.
>EXEC spDateRange '07/40/08', '08/01/08' -- Test for an invalid date.
>
>