Check functionality of conversion of dynamic sql code to ordinary sql

Hello, I have a SQL Server 2008 stored procedure ([login to view URL]) that intermittently runs very slowly. Normally it completes in under 2 seconds, but occasionally it takes 75 seconds to complete. It runs dynamic SQL (i.e. it builds a string inside the procedure and runs it as a sql command). I think that the reason it sometimes runs slow is that SQL Server builds a new query plan each time it runs, and sometimes this plan is bad, causing the slow run. Although I could try to get SQL Server to store a saved plan by replacing: exec sql with: sp_executesql I have decided instead to rewrite it as a normal query (i.e. without using dynamic SQL). The purpose of this project is to ask you to compare the old one with the new one I've written, and check that the functionality of the new one is exactly the same as the old one, whatever the parameters. Really just to check that I haven't made any silly mistakes. The old stored procedure is in: [login to view URL] The new stored procedure is in: [login to view URL] and the new one uses a function "udf_CommaStringToTable" in: [login to view URL] Any other thought or suggestions would be welcomed. An example of how the stored procedure is called, with parameters, is in the comments section of the new stored procedure. These parameters can vary. Note I have substituted for the at character in this listing, because rentacoder doesn't like that character. Many Thanks, Neil Miller Windmill IT Ltd ## Deliverables 1) All deliverables will be considered "work made for hire" under U.S. Copyright law. Employer will receive exclusive and complete copyrights to all work purchased. (No 3rd party components unless all copyright ramifications are explained AND AGREED TO by the employer on the site per the worker's Worker Legal Agreement). ## Platform SQL Server 2008 (not RC2) running on Windows 2008 DataCentre edition. select version gives: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)