none
Plan cache RRS feed

  • Question

  • I have following two sps.

    ALTER PROCEDURE dbo.withoutlike(@d VARCHAR(20))
    AS
    BEGIN
        DECLARE @sql VARCHAR(255)

    SET @sql='SELECT * FROM dbo.DISTRICT STS WHERE STS.district = '''+@d+''''

    EXECUTE(@sql)
    END
    GO

    EXECUTE dbo.withoutlike @d = 'kaski' 
    GO
    ALTER PROCEDURE dbo.withlike(@d VARCHAR(20))
    AS
    BEGIN
        DECLARE @sql VARCHAR(255)

    SET @sql='SELECT * FROM dbo.DISTRICT STS WHERE STS.district like '''+@d+''''
    EXECUTE(@sql)
    END

    go

    EXECUTE dbo.withlike @d = 'doti'

    Both sps have dynamic sql. When I run the sp with like operator query plan is stored in plan cache with a plan for each value of the like predicate. But when i run sp with = operator the plan is not stored in cache

    I tried this with both parameterized simple and forced in database setting.

    The sql server version is sql server 2016(sp1).


    • Edited by Curendra Wednesday, November 27, 2019 8:50 AM
    Wednesday, November 27, 2019 8:49 AM

Answers

All replies