//
you're reading...
Microsoft SQL

SQL Search query example

It took me a long while to find out how to do this a few years back, it may be easier to do now than it was but I keep this code handy should I ever need it again. It basically lets you query MSSQL by passing optional search parameters (ClientId, ProjectID etc) and will then get the results. Hope it helps someone else out

CREATE PROCEDURE DrawingSearch
(
@ClientID int = NULL,
@ProjectID int = NULL,
@ProjectManager int = NULL,
@DocumentType int = NULL,
@AreaID int = NULL,
@SearchString varchar(50) = NULL
)
AS
SELECT

tblClientCompany.CompanyName, tblDocuments.ProjectID, tblGeographicArea.GeographicArea, tblProjectManager.ProjectManagerName, tblDocuments.DocumentTitle, tblDocumentType.DocumentType
FROM
tblDocuments
LEFT OUTER JOIN tblClientCompany
on tblDocuments.ClientID = tblClientCompany.ClientID
LEFT OUTER JOIN tblGeographicArea
on tblDocuments.GeographicAreaID = tblGeographicArea.GeographicAreaID
LEFT OUTER JOIN tblProjectManager
on tblDocuments.CreatedByProjectManagerID = tblProjectManager.ProjectManagerID
LEFT OUTER JOIN tblDocumentType
on tblDocuments.DocumentTypeID = tblDocumentType.DocumentTypeID
WHERE
((@ClientID IS NULL) OR (tblDocuments.ClientID like ‘%’ + cast(@ClientID as varchar(10)) + ‘%’)) AND
((@ProjectID IS NULL) OR (tblDocuments.ProjectID like ‘%’ + cast(@ProjectID as varchar(10)) + ‘%’)) AND
((@ProjectManager IS NULL) OR (tblDocuments.CreatedByProjectManagerID like ‘%’ + cast(@ProjectManager as varchar(10)) + ‘%’)) AND
((@DocumentType IS NULL) OR (tblDocuments.DocumentTypeID like ‘%’ + cast(@DocumentType as varchar(10)) + ‘%’)) AND
((@AreaID IS NULL) OR (tblDocuments.GeographicAreaID like ‘%’ + cast(@AreaID as varchar(10)) + ‘%’)) AND
((@SearchString IS NULL) OR (DocumentTitle like ‘%’ + @SearchString + ‘%’))

This query searches tblDocuments, if no matches are found on the JOINS it will still pull the record as using LEFT OUTER JOIN

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: