GilaMonster (9/25/2013)

---

enriquezreyjoseph (9/24/2013)

---

But some post say that..Dynamic Sql is a powerful tool...

Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time

can you give me a strong justification why should i stop using dynamic SQL?? PLEASE 🙁

Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.

Now, if you want to do a dynamic search, you will need dynamic SQL, but not the very convoluted, insecure way you've got.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Oh, and if you want to know why the way you've written it is bad, try running this:

EXEC [dbo].[SearchBiography] @firstname = 'Gary'

@middlename = ''

@lastname = 'White''; shutdown with nowait --'

@sex = ''

@status = ''

@sexID = 0

@statusID = 0;

Edit: one quote too many