SQL

The Story

I  want to search or filter records from the database based on some parameters like First Name, Company Name, Category etc.

However, using static or hard-coded SQL statement just won’t cut it. I need to write a dynamic SQL statement so I can use a flexible statement that is created and executed on-the-fly or on run-time.

Solution

First, I need to write a stored procedure that accepts different parameters.
Then, I am going to construct the dynamic SQL statement by concatenating strings.
Lastly, I am going to use the EXEC or sp_executesql to execute the created dynamic SQL statement.

Below is a sample implementation of dynamic SQL via stored procedure:

CREATE PROCEDURE [dbo].[PeopleSearch]
@FirstName nvarchar(250),
@LastName nvarchar(700),
@JobTitle varchar(300),
@EmailAddress varchar(300),
@CompanyName nvarchar(250),
@CompanyCity nvarchar(50),
@CompanyState nvarchar(50),
@CompanyZip nvarchar(50),
@Longitude decimal(11, 8),
@Latitude decimal(10, 8),
@Radius decimal(11, 8),
@Category smallint,
@ActiveEmpMin int,
@ActiveEmpMax int,
@CompanyEmailAddress varchar(3000),
@OperatorFlag bit = 0
AS
BEGIN

DECLARE @Where varchar(8000)
DECLARE @Operator varchar(6)
DECLARE @WildCard varchar(1)

IF @OperatorFlag = 0
BEGIN
SET @Operator = ' LIKE '
SET @WildCard = '%'
END
ELSE
BEGIN
SET @Operator = ' = '
SET @WildCard = ''
END

SET @Where = ''

IF LEN(ISNULL(@FirstName, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'p.FirstName' + @Operator + '''' + @WildCard + @FirstName + @WildCard + ''''
END

IF LEN(ISNULL(@LastName, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'p.LastName' + @Operator + '''' + @WildCard + @LastName + @WildCard + ''''
END

IF LEN(ISNULL(@JobTitle, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'p.JobTitle' + @Operator + '''' + @WildCard + @JobTitle + @WildCard + ''''
END

IF LEN(ISNULL(@EmailAddress, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'p.EmailAddress' + @Operator + '''' + @WildCard + @EmailAddress + @WildCard + ''''
END

IF LEN(ISNULL(@CompanyName, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_NAME' + @Operator + '''' + @WildCard + @CompanyName + @WildCard + ''''
END

IF LEN(ISNULL(@CompanyCity, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_CITY' + @Operator + '''' + @WildCard + @CompanyCity + @WildCard + ''''
END

IF LEN(ISNULL(@CompanyState, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_STATE' + @Operator + '''' + @WildCard + @CompanyState + @WildCard + ''''
END

IF LEN(ISNULL(@CompanyZip, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_ZIP' + @Operator + '''' + @WildCard + @CompanyZip + @WildCard + ''''
END

IF LEN(ISNULL(@Latitude, '')) > 0 AND LEN(ISNULL(@Longitude, '')) > 0 AND LEN(ISNULL(@Radius, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + '(s.SPONSOR_LATITUDE Is Not Null AND s.SPONSOR_LONGITUDE Is Not Null)'
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
SET @Where = @Where + 'dbo.GetDistance(' + CONVERT(varchar, @Latitude) + ', ' + CONVERT(varchar, @Longitude) + ', CAST(s.SPONSOR_LATITUDE as float(8)), CAST(s.SPONSOR_LONGITUDE as float(8))) <= ' + CONVERT(varchar, @Radius)
END

IF @Category IS NOT NULL
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_BUSINESS_CODE' + ' = ' + @Category
END

IF @ActiveEmpMin IS NOT NULL
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'd.Total_Active_Participants' + ' >= ' + CONVERT(varchar, @ActiveEmpMin)
END

IF @ActiveEmpMax IS NOT NULL
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'd.Total_Active_Participants' + ' <= ' + CONVERT(varchar, @ActiveEmpMax)
END

IF LEN(ISNULL(@CompanyEmailAddress, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'c.EmailAddress' + @Operator + '''' + @WildCard + @CompanyEmailAddress + @WildCard + ''''
END

DECLARE @Query varchar (MAX)

SET @Query =
'SELECT p.FirstName, p.LastName, p.JobTitle, e.EmailAddress, e.Verified, p.PrimaryContact, p.IsAdmin, p.Url, p.BloombergKeyExecUrl, p.ProfileId
FROM PeopleData d
INNER JOIN Sponsors s
ON d.SPONSOR_ID = s.SPONSOR_ID
INNER JOIN Contacts.Companies c
ON s.SPONSOR_EXTID = c.ExtId
INNER JOIN Contacts.People p
ON c.ExtId = p.ExtId
INNER JOIN Contacts.EmailAddresses e
ON p.ProfileId = e.ProfileId' +
@Where

EXEC (@Query)

END

WITH CTE AS(
SELECT referenceNo, RN = ROW_NUMBER()OVER(PARTITION BY referenceNo ORDER BY referenceno )
FROM dbo.[Order]
)
DELETE FROM CTE WHERE RN > 1