Advantages of using Stored Procedures

Below listed are some of the advantages of using Stored Procedures. This is one of the common questions in job interviews. I have conducted lot of interviews and have heard candidates telling mostly only one answer. "Stored procedure is precompiled and hence has faster execution".

Below listed are the ones which you can now talk on and show your technical expertise. :)

• Execution plan retention and reuse
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Query parameterization
• Network bandwidth conservation
• Support for automatic execution at system start-up

Using Dynamic SQL Statements in Stored Procedures

SQL contains a command, EXEC (or EXECUTE), which can be used to dynamically execute a SQL statement. EXEC expects a string - the SQL statement you wish to execute. It will execute the SQL statement passed and returns the results.

Example:

CREATE PROCEDURE SP_GETELEMENTVALUESFORID
@ELEMENT varchar(100),
@EMPLOYEEID varchar(100)
AS
DECLARE @QUERY varchar(250)

IF(@ELEMENT = 'LOCATION')
SET @QUERY = 'SELECT DISTINCT LOCATION, TLOC.LOCATIONID From TBLLOCATION TLOC JOIN TBLACCESS TACC ON TLOC.LOCATIONID = TACC.LOCATIONID WHERE TACC.EMPLOYEEID = ' + @EMPLOYEEID
ELSE IF(@ELEMENT = 'VERTICAL')
SET @QUERY = 'SELECT DISTINCT VERTICAL, TVER.VERTICALID From TBLVERTICAL TVER JOIN TBLACCESS TACC ON TLOC.VERTICALID = TACC.VERTICALID WHERE TACC.EMPLOYEEID = ' + @EMPLOYEEID

EXEC (@QUERY)
GO

Do not Forget the bracket surrounding the @query. Without the brackets, you will get an error in when executing.