SQL Server Performance Forum – Threads Archive

Dynamic SQL in a stored procedure

HI all, I have a stored procedure that takes in 5 parameters. Each of these parameters default to null if no value is passed in. If a value is passed in then the SP will add the value to the selection criteria. And if it is null it will ignore it. However to do this the SP is building up an SQL statement and then executing the SQL Statement. Is there a better method of doing this? Below is a copy of the stored procedure, —————————————————————–
CREATE PROCEDURE [sp_ApcEnquiry_Search]
(
@BranchIdint = 0,
@CustomerNamevarchar(100) = ”,
@Addressvarchar(100) = ”,
@PostCodevarchar(100) = ”,
@SpecJobNovarchar(100) = ”,
@SalesLeadNovarchar(100) = ”,
@SecAuditNovarchar(100) = ”
) AS DECLARE @Selectnvarchar(4000)
DECLARE @NeedsWherebit
DECLARE @NeedsAndbit
SET @NeedsWhere= 0
SET @NeedsAnd = 1 SET @Select = ‘SELECT*
FROMtblApcEnquiry WITH (NOLOCK)
WHEREUserId > 0 ‘ /*Prevents referred enquiries being fetched without going through the referral browser. */ IF @BranchId > 0
SET @Select = @Select + ‘AND BranchId=’ + CAST(@BranchId AS nvarchar) + ‘ ‘/*Select only those records specific to the user’s branch. */ IF @CustomerName != ”
BEGIN
IF @NeedsWhere = 1
BEGIN
SET @Select = @Select + ‘WHERE ‘
SET @NeedsWhere = 0
END IF @NeedsAnd = 1
SET @Select = @Select + ‘ AND ‘ SET @Select = @Select + ‘(VisitCustomerName Like ”%’ + @CustomerName + ‘%”’
SET @Select = @Select + ‘OR SiteCustomerName Like ”%’ + @CustomerName + ‘%”’
SET @Select = @Select + ‘OR BillingCustomerName Like ”%’ + @CustomerName + ‘%”’
SET @Select = @Select + ‘OR CBSContractCustomerName Like ”%’ + @CustomerName + ‘%”)’
SET @NeedsAnd = 1 END IF @Address != ”
BEGIN
IF @NeedsWhere = 1
BEGIN
SET @Select = @Select + ‘WHERE ‘
SET @NeedsWhere = 0
END IF @NeedsAnd = 1
SET @Select = @Select + ‘ AND ‘ SET @Select = @Select + ‘(BillingAddress Like ”%’ + @Address + ‘%”’
SET @Select = @Select + ‘OR VisitAddress Like ”%’ + @Address + ‘%”’
SET @Select = @Select + ‘OR SiteAddress Like ”%’ + @Address + ‘%”)’
SET @NeedsAnd = 1
END IF @PostCode != ”
BEGIN
IF @NeedsWhere = 1
BEGIN
SET @Select = @Select + ‘WHERE ‘
SET @NeedsWhere = 0
END IF @NeedsAnd = 1
SET @Select = @Select + ‘ AND ‘ SET @Select = @Select + ‘(VisitPostCode Like ”%’ + @PostCode + ‘%”’
SET @Select = @Select + ‘OR SitePostCode Like ”%’ + @PostCode + ‘%”’
SET @Select = @Select + ‘OR BillingPostCode Like ”%’ + @PostCode + ‘%”’
SET @Select = @Select + ‘OR CBSContractPostCode Like ”%’ + @PostCode + ‘%”)’
SET @NeedsAnd = 1
END IF @SpecJobNo != ”
BEGIN
IF @NeedsWhere = 1
BEGIN
SET @Select = @Select + ‘WHERE ‘
SET @NeedsWhere = 0
END IF @NeedsAnd = 1
SET @Select = @Select + ‘ AND ‘
SET @Select = @Select + ‘SpecJobNo Like ”%’ + @SpecJobNo + ‘%”’
SET @NeedsAnd = 1
END IF @SalesLeadNo != ”
BEGIN
IF @NeedsWhere = 1
BEGIN
SET @Select = @Select + ‘WHERE ‘
SET @NeedsWhere = 0
END IF @NeedsAnd = 1
SET @Select = @Select + ‘ AND ‘ SET @Select = @Select + ‘SalesLeadNo Like ”%’ + @SalesLeadNo + ‘%”’
SET @NeedsAnd = 1
END IF @SecAuditNo != ”
BEGIN
IF @NeedsWhere = 1
BEGIN
SET @Select = @Select + ‘WHERE ‘
SET @NeedsWhere = 0
END IF @NeedsAnd = 1
SET @Select = @Select + ‘ AND ‘ SET @Select = @Select + ‘SecAuditNo Like ”%’ + @SecAuditNo + ‘%”’
SET @NeedsAnd = 1
END
SET @Select = @Select + ‘ ORDER BY Id’ EXEC sp_executesql @Select RETURN
GO —————————————————————— The above is not my code, it is from a project i have taken over, hoever i have in the past used the above method. I just think there is a much more efficient way of doing the above. Thank in advance for any advice. Regards Craig

I have found that dynamic SQL can almost always be replaced by more efficient code. The problem with dynamic sql, is that you end up losing all benefits of using a stored procedure in the first place, since you will have to recompile every time and you wont end up using a cached execution plan. Dynamic SQL is usually written because people want to be able to provide a different range of parameters, which is what you are trying to do (I think) One thing to look into is to use dynamic parameters. I will provide a small example: create proc ap_foo @firstname varchar(100) = NULL, @lastname varchar(100) = NULL AS select firstname, lastname from mytable where mytable.firstname = coalesce(@firstname,mytable.firstname) and mytable.lastname = coalesce(@lastname,mytable.lastname) GO This way, if a parameter is NULL, the WHERE clause will match the column to itself. Depending on the number of parameters, this can prove to be much more efficient for certain queries. Also, keep in mind that it isn’t always a bad idea to write redundant code to check certain parameters before executing complex SQL statements. For example: create proc ap_foo2 @bool1 bit, @bool2 bit AS if (@bool1 = 0 AND @bool2 = 0) begin — big complex select statement end else if (@bool1 = 0 AND @bool2 = 1) begin — slightly different big complex select end else if (@bool1 = 1 AND @bool2 = 0) … etc. GO Sometimes in these cases it is better (faster, more efficient) to drill down to the query you want to run by checking conditions first rather than having a "pretty" single SELECT statement with dynamic parameters or dynamic sql. Just remember that it all depends on the context of where it is being used. Remember to load test your procedures and find out which approach works best for your database environment. There is no "right" way, only "better" ways exist. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] -dataninja- "The normalcy of a database is inversely proportional to that of it’s DBA."

You should consider using the CASE statement in your WHERE clause. It would look something like this: WHERE BranchId = CASE WHEN @BranchId = 0 THEN BranchID ELSE @BranchID END
AND VisitCustomerName LIKE CASE WHEN @CustomerName = ” THEN VisitCustomerName ELSE ‘%’ + @CustomerName + ‘%’ END
AND …

]]>