Monday, August 24, 2009

SQL Search

CREATE PROCEDURE [dbo].[GetSparePartsECRETS]
(
@pnEcretsNum INT = NULL
,@pnvcEcretsTitle NVARCHAR(100) = NULL
,@pnvcOriginatorID NVARCHAR(50) = NULL
)
AS

BEGIN

/*SET NOCOUNT TO ON AND NO LONGER DISPLAY THE COUNT MESSAGE*/
SET NOCOUNT ON

/*START THE TRY BLOCK*/
BEGIN TRY
IF @pnEcretsNum = '' SET @pnEcretsNum = NULL
IF @pnvcEcretsTitle = '' SET @pnvcEcretsTitle = NULL
IF @pnvcOriginatorID = '' SET @pnvcOriginatorID = NULL

SELECT
TBLE.ECRETSID,
TBLE.[Title],
[TBEI].[FirstName] +' '+COALESCE([TBEI].[LastName],'') AS Originator
FROM dbo.tblECRETS TBLE,
dbo.TblEmployeeInfo TBEI
WHERE (TBLE.ECRETSID = @pnEcretsNum OR @pnEcretsNum IS NULL)
AND (TBLE.Title = @pnvcEcretsTitle OR @pnvcEcretsTitle IS NULL)
AND (TBLE.OriginatorID = @pnvcOriginatorID OR @pnvcOriginatorID IS NULL)
AND TBLE.OriginatorID = TBEI.FQN

END TRY
BEGIN CATCH
IF @@ERROR != 0
BEGIN
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
END
END CATCH

/*Reset SET NOCOUNT to OFF*/
SET NOCOUNT OFF
END