I saw a nice post today, can't resist myself from copying and publishing it for my readers

Are You a Project Manager Or a Project Mangler?

Which one are you? An effective IT project manager, able to deliver software on time, according to specs, and within budget, or someone referred to by your peers as a project mangler? Find out with these Top 10 Signs You're a Project Mangler.
10. Your .mpp attachments are considered to be more harmful than the Netsky virus.
9. You think your job description is limited to running around and asking people "Are you done yet?"
8. Your record for the "longest consecutive number of days without changing your project plan" is 3, which was achieved over a weekend.
7. You don't publish your project plan for fear developers might find out what the REAL dates are.
6. When the first 90% of your project is done, the second 90% begins.
5. You couldn't write a line of code to save your life, yet you tell developers how long it will take them to complete their feature.
4. You only work from 9 to 5 but expect developers to work evenings and weekends to meet your deadlines.
3. Your best motivational skill: telling people you're working from home tomorrow.
2. You DO think that 9 people can have a baby in 1 month.
And the number one sign you're a project mangler...
1. Your name is R. U. Dunyet.

Avoid Dynamic Queries

 

A sample dynamic query goes like this,

CREATE PROCEDURE sp_Search_Employee
(
@iEmployeeId AS  INT = 0,
@sEmployeeName AS VARCHAR(50)=''
)
AS
BEGIN

DECLARE @sSQLSearch AS VARCHAR(1000)
SET @sSQLSearch = ' SELECT *  FROM tbl_Employee '
SET @sSQLSearch = @sSQLSearch + ' WHERE 1 = 1  AND ' 

IF @iEmployeeId <> 0
BEGIN
SET  @sSQLSearch = @sSQLSearch + ' EmployeeID = ' + CAST(@iEmployeeId AS  VARCHAR)
END

IF @sEmployeeName <> 0
BEGIN
SET @sSQLSearch = @sSQLSearch + ' EmployeeName = ' + @sEmployeeName
END

EXEC(@sSQLSearch)

END

Instead of the above Query, reforming the query without using Dynamic SQL can be done like this:

CREATE PROCEDURE sp_Search_Employee
(
@iEmployeeId  AS INT = 0,
@sEmployeeName AS VARCHAR(50)=''
)
AS
BEGIN

SELECT * FROM tbl_Employee
WHERE
(
@iEmployeeId = 0  OR
EmployeeId = @iEmployeeId
) 
AND
(
@sEmployeeName = '' OR
EmployeeName = @sEmployeeName
)

END

 ---------------------------------------------------------------------------------------

Dynamic SQL can be created easily but it comes along with a few drawbacks:

 
1. The main advantage of using Stored Procedures in SQL is Precompiliation. All the SPs are stored in a precompiled form and hence they are faster in execution. On the contrary, the main SELECT query in a dynamic SQL is NOT Precompiled. This hampers the performance.
2. The maximum length EXEC can handle is 8000 (if VARCHAR is used to build the query). Queries going beyond the limit can not be executed.

 

Wise Computing!!

Save Server Power, Save Energy, Save Earth