Conditional Search 1

[marina9]

Hello everyone,

I've created a stored procedure that accepts various input parameters and builds a dynamic SQL statement from them. Here is the procedure:

CREATE PROCEDURE dbo.sp_LPXCustomerLimitBrokerSearch

@Status VARCHAR(2),
@RegionCode VARCHAR(3),
@LincsCode VARCHAR(5),
@LocationCode VARCHAR(5),
@DailyLockLimit VARCHAR(10),
@OvernightLockLimit VARCHAR(10),
@BrokerName VARCHAR(50)

AS

DECLARE
@Sql VARCHAR(1000),
@Where VARCHAR(1000)

SET @Sql = 'SELECT [Broker Name], [Broker ID],
[Maximum Best Efforts Loan Lock Caps by Price File],
[Maximum Best Efforts Loan Lock Caps],
[Loan Lock Caps Start Time], [Loan Lock Caps End Time],
[Window Time Zone], [Seller Status], [Enable Second Liens Y/N], [FHA Loan Type ID], [VA Loan Type ID], [Warehouse Seller] FROM tblBrokerLPX'

SET @Where = ' WHERE [Seller Status] = ' + @Status

SET @Where = @Where + CASE WHEN @RegionCode IS NULL THEN '' ELSE ' AND [Branch ID] = ' + @RegionCode END

SET @Where = @Where + CASE WHEN @LincsCode IS NULL THEN '' ELSE ' AND LEFT([Broker ID], 5) = ' + @LincsCode END

SET @Where = @Where + CASE WHEN @LocationCode IS NULL THEN '' ELSE ' AND SUBSTRING([Broker ID], 6, 5) =' + @LocationCode END

SET @Where = @Where + CASE WHEN @DailyLockLimit IS NULL THEN '' ELSE ' AND [Maximum Best Efforts Loan Lock Caps by Price File] = ' + @DailyLockLimit END

SET @Where = @Where + CASE WHEN @OvernightLockLimit IS NULL THEN '' ELSE ' AND [Maximum Best Efforts Loan Lock Caps] = ' + @OvernightLockLimit END

SET @Where = @Where + CASE WHEN @BrokerName IS NULL THEN ''
ELSE ' AND [Broker Name] LIKE ' + @BrokerName + '%' END


SET @Sql = @Sql + @Where

Exec (@Sql)
GO


when I execute '1', null, null, null, null, null, 'Allied' in the query analyzer (for a partial BrokerName search) I get the following error:

Incorrect syntax near '%'.

I know the problem is with the final SET = @Where statement, but I can't figure out what the issue is. Can anyone please help me? Thank you very much!

 

[despierto]

Replace the line EXEC (@SQL) With PRINT @SQL, and run the procedure from the SQL Query Analyzer. This will print out the SQL string in the messages window, for you to review.

Alternately, if your doing this from say, VB, you might be able use RAISEERROR to trap the sql string and view it from inside your application.

 

[marina9]

Thanks despierto for your reply! That really helped isolate where the issue was. Turns out the SQL was not reading the quotes to indicate the field is a string. When I changed the last SET = @Where statement to

SET @Where = @Where + CASE WHEN @BrokerName IS NULL THEN ''
ELSE ' AND [Broker Name] LIKE "' + @BrokerName + '%"' END

it worked perfectly. Thanks again!

 

[ESquared]

Can't you do this without dynamic SQL?

...
WHERE
   (@BrokerName IS NULL OR [Broker Name] LIKE @BrokerName + '%')
   AND (@RegionCode IS NULL OR [Branch ID] = @RegionCode)
   AND (@LincsCode IS NULL OR [Broker ID] LIKE @LincsCode + '%')
   AND  (@OvernightLockLimit IS NULL OR [Maximum Best Efforts Loan Lock Caps] = @OvernightLockLimit)

It is good SQL programming practice to avoid dynamic SQL wherever possible. Certainly your constructed query will have the exact syntax necessary, but it has to be compiled. If you write this as a stored procedure in the format I am suggesting, then the compiled execution plan can be reused. It is also much more straightforward this way and easier to understand and maintain.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[marina9]

Thanks ESquared for your suggestion. I will definitely take it into consideration and see if I can revise the stored procedure as you suggested.