CREATE PROCEDURE [dbo].[ps_Customer_SEARCH]
---Declare input variables
@OrderBy nvarchar(50)= NULL, --column name to odrder by
@OrderByDir nvarchar(1) = NULL, --Direction of sort 'A' = ascending 'D' = Descending
@CustomerID int = NULL,
@CompanyID int = NULL,
@FirstName nvarchar(50)= NULL,
@LastName nvarchar(50)= NULL,
@Billing nvarchar(50)= NULL,
@AccountStatus int = NULL,
@SDBID int = NULL,
@ERUserID int = NULL,
@Erequest bit = NULL,
@Mail bit = NULL,
@Created datetime = NULL,
@Name nvarchar(50)= NULL,
@Branch nvarchar(50)= NULL,
@LocalRows int = 0 OUTPUT,
@ReturnValue int = 0 OUTPUT,
@LocalError int = 0 OUTPUT,
@OutMessage nvarchar(500) OUTPUT
AS
BEGIN TRY
---SET NOCOUNT ON added to prevent extra result sets from
---interfering with SELECT statements.
SET NOCOUNT ON;
---Declare local scope variables
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
---Create the SQL string that will be dynamic
SELECT @sql = 'SELECT dbo.Customer.intCustomerID, dbo.Customer.intCompanyID, dbo.Customer.chrFirstName,
dbo.Customer.chrLastName, dbo.Customer.chrBilling, dbo.Customer.intAccountStatus, dbo.Customer.intSDBID,
dbo.Customer.intERUserID, dbo.Customer.blnErequest, dbo.Customer.blnMail, dbo.Customer.dtCreated,
dbo.Customer.intRecCount, dbo.Company.chrName, dbo.Company.chrBranch, dbo.AccountStatus.chrStatus,
dbo.Customer.chrLastName + '', '' + dbo.Customer.chrFirstName AS chrCustomer,
dbo.Company.chrName + '' '' + ISNULL(dbo.Company.chrBranch, '''') AS chrCompany
FROM
dbo.Company
INNER JOIN dbo.Customer ON dbo.Company.intCompanyID = dbo.Customer.intCompanyID
INNER JOIN dbo.AccountStatus ON dbo.Customer.intAccountStatus = dbo.AccountStatus.intAccountStatusID
WHERE 1 = 1'
---Use is null to determin if parameter was passed, if so append to SQL string...
IF @CustomerID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intCustomerID = @CustomerID'
IF @CompanyID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intCompanyID = @CompanyID'
IF @FirstName IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.chrFirstName LIKE ''%'' + @FirstName + ''%'''
IF @LastName IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.chrLastName LIKE ''%'' + @LastName + ''%'''
IF @Billing IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.chrBilling LIKE ''%'' + @Billing + ''%'''
IF @AccountStatus IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intAccountStatus = @AccountStatus'
IF @SDBID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intSDBID = @SDBID'
IF @ERUserID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intERUserID = @ERUserID'
IF @Erequest IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.blnErequest = @Erequest'
IF @Mail IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.blnMail = @Mail'
IF @Created IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.dtCreated >= @Created AND dbo.Customer.dtCreated < @Created + 1' --Selects records created after this date
IF @Name IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Company.chrName LIKE ''%'' + @Name + ''%'''
IF @Branch IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Company.chrBranch LIKE ''%'' + @Branch + ''%'''
---Set the order by staement
IF @OrderBy IS NOT NULL
SELECT @sql = @sql + ' ORDER BY' +
CASE
WHEN @OrderBy = 'FirstName' AND @OrderByDir = 'D' THEN ' dbo.Customer.chrFirstName DESC'
WHEN @OrderBy = 'FirstName' AND @OrderByDir != 'D' THEN ' dbo.Customer.chrFirstName'
WHEN @OrderBy = 'LastName' AND @OrderByDir = 'D' THEN ' dbo.Customer.chrLastName DESC'
WHEN @OrderBy = 'LastName' AND @OrderByDir != 'D' THEN ' dbo.Customer.chrLastName'
WHEN @OrderBy = 'Billing' AND @OrderByDir = 'D' THEN ' dbo.Customer.chrBilling DESC'
WHEN @OrderBy = 'Billing' AND @OrderByDir != 'D' THEN ' dbo.Customer.chrBilling'
WHEN @OrderBy = 'SDBID' AND @OrderByDir = 'D' THEN ' dbo.Customer.intSDBID DESC'
WHEN @OrderBy = 'SDBID' AND @OrderByDir != 'D' THEN ' dbo.Customer.intSDBID'
WHEN @OrderBy = 'ERUserID' AND @OrderByDir = 'D' THEN ' dbo.Customer.intERUserID DESC'
WHEN @OrderBy = 'ERUserID' AND @OrderByDir != 'D' THEN ' dbo.Customer.intERUserID'
WHEN @OrderBy = 'AccountStatus' AND @OrderByDir = 'D' THEN ' dbo.Customer.intAccountStatus DESC'
WHEN @OrderBy = 'AccountStatus' AND @OrderByDir != 'D' THEN ' dbo.Customer.intAccountStatus'
WHEN @OrderBy = 'Created' AND @OrderByDir = 'D' THEN ' dbo.Customer.dtCreated DESC'
WHEN @OrderBy = 'Created' AND @OrderByDir != 'D' THEN ' dbo.Customer.dtCreated'
---Use default sort order for returned recordset.
ELSE ' dbo.Company.chrName, dbo.Company.chrBranch, dbo.Customer.chrLastName, dbo.Customer.chrFirstName'
END
ELSE
---Use default sort order for returned recordset.
SELECT @sql = @sql + ' ORDER BY dbo.Company.chrName, dbo.Company.chrBranch, dbo.Customer.chrLastName, dbo.Customer.chrFirstName'
----Set the parameter list for the sp_executesql call
SELECT @paramlist = '@CustomerID int,
@CompanyID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Billing nvarchar(50),
@AccountStatus int,
@SDBID int,
@ERUserID int,
@Erequest bit,
@Mail bit,
@Created datetime,
@Name nvarchar(50),
@Branch nvarchar(50)'
---Execute the function sp_executesql that takes the following parameters:
--- sql string, parameter list, and then the declared parameters in the same order as the parmaeter list
EXEC sp_executesql @sql, @paramlist,
@CustomerID, @CompanyID, @FirstName, @LastName, @Billing, @AccountStatus,
@SDBID, @ERUserID, @Erequest, @Mail, @Created, @Name, @Branch
---if no errors, set the return value to 0 and return the number of rows in the search results
SELECT @LocalRows = @@ROWCOUNT, @ReturnValue = 0
SELECT @LocalRows, @ReturnValue
END TRY
BEGIN CATCH
---If errors occured - capture error information and return to calling application
SELECT @ReturnValue = 1, @LocalError = ERROR_NUMBER(), @OutMessage = ('ROWS AFFECTED = ' + CAST(@@ROWCOUNT as nvarchar(6)) + ' SEVERITY = ' + CAST(ERROR_SEVERITY() as nvarchar(3)) + ' STATE = ' + CAST(ERROR_STATE() as nvarchar(2)) + ' PROCEDURE = ' + ERROR_PROCEDURE() + ' LINE = ' + CAST(ERROR_LINE() as nvarchar(3)) + ' MESSAGE ' + ERROR_MESSAGE())
SELECT @LocalError, @ReturnValue, @OutMessage
END CATCH
![[wink]](/data/assets/smilies/wink.gif "[wink] [wink]")
