how to catch input output parameter errors

• Pavas Malviya

  Ten Centuries

  Points: 1109

  More actions

  June 24, 2005 at 12:53 am

  #66298

  I am using sqlserver 2000.

  I want to raise an error

  if  i have not passed  Inout /output parameter.Can i throw a custome error message

  or return an eror code.

  If i call below stored procedure with following string

  EXEC YearSales

  How can i catch this type of errrors???

   

  CREATE PROCEDURE YearSales

    @Start   datetime,

    @End     datetime = NULL

  AS

  IF (@Start IS NULL OR @End IS NULL)

  BEGIN

    RAISERROR('NULL Values are not allowed',14,1)

    RETURN

  END

  SELECT ShippedDate,

         OrderID

    FROM Orders

  WHERE DATENAME (yyyy,ShippedDate) BETWEEN @Start AND @End

  GO

  EXEC YearSales @Start = '1997', @End = '1998'

  ---

  Kindest Regards,

  Pavas

  Dream The Dream
  Explore the World
  Experince The Exhilaration

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  June 24, 2005 at 1:30 am

  #568189

  CREATE PROCEDURE YearSales

    @Start   datetime,

    @End     datetime = NULL

  AS

  SET NOCOUNT ON

  DECLARE @Error

  IF (@Start IS NULL OR @End IS NULL)

  BEGIN

    RAISERROR('NULL Values are not allowed',14,1)

    RETURN 14

  END

  SELECT ShippedDate,

         OrderID

    FROM Orders

  WHERE DATENAME (yyyy,ShippedDate) BETWEEN @Start AND @End

  SET @Error = @@Error

  IF @Error = 0

     RETURN 0

  ELSE

     RETURN @Error

  GO

  DECLARE @myError INT

  EXEC @myError = YearSales @Start = '1997', @End = '1998'

  SELECT @myError

  Regards,
  gova