Home

Description

The SQL Server code rule "Functions and stored procedures should not have too many lines of code" is a best practice for developers to follow when creating functions and stored procedures. This rule is designed to help maintain code readability and reduce the complexity of the code. It is important to keep the code concise and easy to understand, as too many lines of code can lead to confusion and difficulty in debugging. Additionally, too much code can lead to an increase in the amount of time it takes to execute the code. By following this rule, developers can ensure their code is efficient and maintainable.

Key Benefits

• Easier to debug: Having fewer lines of code makes it easier to debug functions and stored procedures.
• Easier to maintain: Having fewer lines of code makes it easier to maintain functions and stored procedures.
• Better performance: Having fewer lines of code can lead to better performance.
• Reduced complexity: Having fewer lines of code can reduce complexity.

Non-compliant Code Example

Create PROCEDURE [DATA].INIT  --Non compliant code (Procedure is having line of code more than default limit of 100 )
@orderId int, 
@productId int,
@emp_Id int,
@cust_id int
AS
BEGIN
EXEC [DATA].GetAllOrders;

EXEC [DATA].LogCleaner;

EXEC [DATA].sp_customer_List;

EXEC [DATA].sp_retrieve_employees;

EXEC [DATA].GetAllProducts;

EXEC [DATA].GetOrderById @orderId;

EXEC [DATA].GetAllItems @orderId;

EXEC [DATA].GetProductById @productId;

EXEC [DATA].LogCleaner;

EXEC [DATA].GetManagerOfEmployeeById @emp_Id;

EXEC [DATA].DeleteCustomerById @cust_id;

EXEC [DATA].DeleteOrderById @orderId;

EXEC [DATA].DeleteOrderItemById @orderId;

EXEC [DATA].DeleteProductById @productId;

Declare @fname nvarchar(50);
Declare @lname nvarchar(50);

BEGIN

SET @fname = "Mark";
SET @lname = "Pencile";

EXEC [DATA].sp_GetFullname @fname,@lname;

EXEC [DATA].InsertCustomer @fname,@lname;

END

BEGIN

SET @fname = "James";
SET @lname = "Smith";

EXEC [DATA].InsertEmployee @fname,@lname;

END


BEGIN

SET @fname = "Maria";
SET @lname = "Garcia";

EXEC [DATA].UpdateCustomerName @cust_id,@fname,@lname;

END

BEGIN

SET @fname = "Maria";
SET @lname = "Hernandez";

EXEC [DATA].UpdateEmployeeName @emp_Id,@fname,@lname;

END

BEGIN

Declare @price int;
SET @price = 200;

EXEC [DATA].UpdateProductPrice @productId,@price;

END


BEGIN
-- Query the Product and Customer table by using the synonym.  
Select * from [DATA].CloneProduct;

Select * from [DATA].CloneCustomer;
INSERT INTO [DATA].CloneCustomer (fname, lname) VALUES (@fname, @lname);

SET @fname = "Maria";
SET @lname = "Hernandez";

UPDATE [DATA].CloneCustomer
	SET FNAME=@fname, LNAME=@lname
	WHERE Id = @cust_id;
	
DELETE [DATA].CloneCustomer where ID = @cust_id;

END


BEGIN
-- Query the Product table by using the sequences.  
SELECT NEXT VALUE FOR [DATA].ProductSeq;  

END

END
GO