I am kind of new in writing Procedure's or dynamic SQL's. Any suggestion that you could help me out with.
A procedure should do a single thing. The academic term is 'single responsibility'
If writing in C# or java or the like, you wouldn't write a class that can do one of 20 things depending on the parameters, that would be against so many coding practices. Same thing with SQL, don't write a procedure that depending on the parameters passed selects an employee, updates an order, deletes a product or drops the database.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass