1. There is a redundant condition check in your example. Once we start building the WHERE clause, we've already passed the first check, which would have prevented our getting down to the second check.
IF @LastName <> '' AND @NameID <> 0 ... RETURNEND...--Begin building WHERE clauseIF @LastName <> '' OR @NameID <> 0...
2. As has already been mentioned, performance of a dynamic SP will be poorer than a static SP. It would make more sense to generate the SP externally and then import them in a SQL stream, or to hook directly into SQL Server and do it programmatically. There's been a lot of work done in the realm of code generation (heck, entire system generation) that could serve as a model for this. As with most things, we trade performance for flexibilty and vice versa.
3. It should be noted that SQLServer2005 removes the 8000 character limit. This alone should be worth the migration.
4. How does the following statement provide SQL injection attack protection?SET @LastName = REPLACE (@LastName, '''', '''''')
When printing out the SQL statements, CHAR(10) does indeed make it easier to read. Maintenance on any sproc that uses dynamic sql however, depends on the complexity of the code.
From the examples given, I see no reason to use dynamic sql. There is no top clause, you do not need to dynamically determine a table name. If your production stored procedurees are setup like this, I would redesign them to take advantage of the sql engine.
I don't see any advantage to using dynamic store procedures. Also, it would be more difficult to tune and troubleshoot dynamic stored procedures if they constantly change. In addition, data validation should be done at the client side and not on the server side to save yourself a round trip.
