Website.SearchForCustomers
Documentation | WideWorldImporters |
Schema | Website |
Name | SearchForCustomers |
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | IN | SearchText | nvarchar(1000) | |
2 | IN | MaximumRowsToReturn | int |
Script
CREATE PROCEDURE Website.SearchForCustomers @SearchText nvarchar(1000), @MaximumRowsToReturn int WITH EXECUTE AS OWNER AS BEGIN SELECT TOP(@MaximumRowsToReturn) c.CustomerID, c.CustomerName, ct.CityName, c.PhoneNumber, c.FaxNumber, p.FullName AS PrimaryContactFullName, p.PreferredName AS PrimaryContactPreferredName FROM Sales.Customers AS c INNER JOIN [Application].Cities AS ct ON c.DeliveryCityID = ct.CityID LEFT OUTER JOIN [Application].People AS p ON c.PrimaryContactPersonID = p.PersonID WHERE CONCAT(c.CustomerName, N' ', p.FullName, N' ', p.PreferredName) LIKE N'%' + @SearchText + N'%' ORDER BY c.CustomerName FOR JSON AUTO, ROOT(N'Customers'); END; |
Exported: 2019-02-04 23:04, Last imported: 2018-08-10 09:51