SQL Server Performance

Dynamic SQL in a stored procedure

Discussion in 'T-SQL Performance Tuning for Developers' started by cmellon, Oct 30, 2002.

  1. cmellon New Member

    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
  2. dataninja New Member

    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)<br /><br />One thing to look into is to use dynamic parameters. I will provide a small example:<br /><br />create proc ap_foo<br />@firstname varchar(100) = NULL,<br />@lastname varchar(100) = NULL<br />AS<br />select firstname, lastname<br />from mytable<br />where mytable.firstname = coalesce(@firstname,mytable.firstname)<br />and mytable.lastname = coalesce(@lastname,mytable.lastname)<br />GO<br /><br />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:<br /><br />create proc ap_foo2<br />@bool1 bit,<br />@bool2 bit<br />AS<br />if (@bool1 = 0 AND @bool2 = 0)<br />begin<br /> -- big complex select statement<br />end<br />else if (@bool1 = 0 AND @bool2 = 1)<br />begin<br /> -- slightly different big complex select<br />end<br />else if (@bool1 = 1 AND @bool2 = 0)<br />...<br />etc.<br />GO<br /><br />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.<br /><br />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=';)' />]<br /><br /><br /><br />-dataninja-<br /><br />"The normalcy of a database is inversely proportional to that of it's DBA."
  3. arb New Member

    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 ...

Share This Page