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