Website.SearchForPeople
Documentation | WideWorldImporters |
Schema | Website |
Name | SearchForPeople |
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | IN | SearchText | nvarchar(1000) | |
2 | IN | MaximumRowsToReturn | int |
Script
CREATE PROCEDURE Website.SearchForPeople @SearchText nvarchar(1000), @MaximumRowsToReturn int AS BEGIN SELECT TOP(@MaximumRowsToReturn) p.PersonID, p.FullName, p.PreferredName, CASE WHEN p.IsSalesperson <> 0 THEN N'Salesperson' WHEN p.IsEmployee <> 0 THEN N'Employee' WHEN c.CustomerID IS NOT NULL THEN N'Customer' WHEN sp.SupplierID IS NOT NULL THEN N'Supplier' WHEN sa.SupplierID IS NOT NULL THEN N'Supplier' END AS Relationship, COALESCE(c.CustomerName, sp.SupplierName, sa.SupplierName, N'WWI') AS Company FROM [Application].People AS p LEFT OUTER JOIN Sales.Customers AS c ON c.PrimaryContactPersonID = p.PersonID LEFT OUTER JOIN Purchasing.Suppliers AS sp ON sp.PrimaryContactPersonID = p.PersonID LEFT OUTER JOIN Purchasing.Suppliers AS sa ON sa.AlternateContactPersonID = p.PersonID WHERE p.SearchName LIKE N'%' + @SearchText + N'%' ORDER BY p.FullName FOR JSON AUTO, ROOT(N'People'); END; |
Exported: 2019-02-04 23:04, Last imported: 2018-08-10 09:51