Shahid Naeem

return specified record or all

Default Parameter & COALESCE

Exec ABC default, 5 —– two parameter first is default and second is supplied

COALESCE (Transact-SQL)

Returns the first non-null expression among its arguments.

--Get  result with parameter else return all
  create PROCEDURE  ABC
  @ID int =null;
  AS
  BEGIN
   select * from Production.Product as p
  where
  p.ProductID= coalesce (@ID  ,p.ProductID)
  END
  GO
  exec ABC
  exec ABC 3

--Trapping for errors using Try.....Catch
BEGIN TRY
		EXECUTE spDoesNotExist
END TRY
BEGIN CATCH
		SELECT
		ERROR_LINE() AS ErrorLine,
		ERROR_NUMBER() as ErrorNumber,
		ERROR_MESSAGE() as ErrorMessage,
		ERROR_SEVERITY() as ErrorSeverity,
		ERROR_STATE() as ErrorState
END CATCH

Tags: Stored Procedure

This entry was posted on Saturday, June 14th, 2008 at 4:32 am and is filed under SQL Server, Stored Procedure. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.