STORE PROCEDURE BASIC PROGRAMMING - PART 1

 

1. Create a Stored Procedure

CREATE PROCEDURE sp_GreetUser
AS
BEGIN
    PRINT 'Welcome to SQL Server Stored Procedure Programming';
END;


================================================================================

Stored Procedure with Parameters


Create Procedure


CREATE PROCEDURE sp_AddNumbers
    @Num1 INT,
    @Num2 INT
AS
BEGIN
    DECLARE @Result INT;
    
    SET @Result = @Num1 + @Num2;
    
    PRINT 'Addition Result = ' + CAST(@Result AS VARCHAR(10));
END;


Execute Procedure


EXEC sp_AddNumbers 10, 20;

==========================================================================


Stored Procedure with Parameters


Create Procedure


CREATE PROCEDURE sp_AddNumbers
    @Num1 INT,
    @Num2 INT
AS
BEGIN
    DECLARE @Result INT;
    
    SET @Result = @Num1 + @Num2;
    
    PRINT 'Addition Result = ' + CAST(@Result AS VARCHAR(10));
END;


Execute Procedure


EXEC sp_AddNumbers 10, 20;


==============================================================================

Loop Using Stored Procedure in SQL Server


You can use a WHILE loop inside a stored procedure to execute statements repeatedly.


Example 1: Print Numbers from 1 to 10


CREATE PROCEDURE sp_PrintNumbers
AS
BEGIN
    DECLARE @Counter INT = 1;

    WHILE @Counter <= 10
    BEGIN
        PRINT 'Number = ' + CAST(@Counter AS VARCHAR(5));

        SET @Counter = @Counter + 1;
    END
END;

=====================================================================================




Example 2: Display Even Numbers from 2 to 20


CREATE PROCEDURE sp_EvenNumbers
AS
BEGIN
    DECLARE @Num INT = 2;

    WHILE @Num <= 20
    BEGIN
        PRINT 'Even Number = ' + CAST(@Num AS VARCHAR(5));

        SET @Num = @Num + 2;
    END
END;

===============================================================================

























Comments

Post a Comment

Popular posts from this blog

Python Introduction

PYTHON PROGRAMING https://thonny.org/ Python supports multiple programming paradigms, including object-oriented, procedural, and functional programming . It provides a rich standard library and a large ecosystem of third-party packages, which helps developers to build applications faster and more efficiently. Python is platform-independent, meaning Python programs can run on Windows, Linux, and macOS without modification. Due to its flexibility and powerful features, Python is widely used in fields such as: ·          Web Development ·          Data Science & Data Analytics ·          Artificial Intelligence & Machine Learning ·          Automation & Scripting ·          Desktop Application Development · ...
Read more

STORE PROCEDURE ADVANCE CONCEPT - PART 2

  List All Stored Procedures SELECT name FROM sys.procedures; ================================================================================ Stored Procedure Calling Another Stored Procedure with Parameters in SQL Server You cannot create a stored procedure inside another stored procedure, but you can call one stored procedure from another and pass parameters . Step 1: Create Table CREATE TABLE Employee ( EmpID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , EmpName VARCHAR ( 100 ), Salary DECIMAL ( 10 , 2 ) ); Step 2: Insert Sample Data INSERT INTO Employee(EmpName, Salary) VALUES ( 'Nilesh Gupta' , 50000 ), ( 'Rahul Sharma' , 45000 ), ( 'Priya Patel' , 55000 ); ====================================================================================== Procedure 1: Get Employee by ID CREATE PROCEDURE sp_GetEmployeeByID ( @EmpID INT ) AS BEGIN SELECT * FROM Employee WHERE EmpID = @EmpID; END ; GO Execute EXEC sp_GetEmployeeByI...
Read more