-
SQL Installing SSMS
-
SQL Syntax
-
SQL Datatype
-
SQL Database
-
SQL Table
-
SQL Select
-
SQL Distinct
-
SQL Count
-
SQL Top
-
SQL Aliases
-
SQL In
-
SQL Insert
-
SQL Update
-
SQL Null
-
SQL Like
-
SQL Wildcards
-
SQL Nested Query
-
SQL Between
-
SQL Unique
-
SQL Primary Key
-
SQL Foreign Key
-
SQL Check
-
SQL Default
-
SQL Database Diagram
-
SQL Stored Procedures
-
SQL Joins
-
SQL Inner Join
-
SQL Left Join
-
SQL Right Join
-
SQL Full Join
-
SQL Trigger
SQL Stored Procedures
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
Example:
The following syntax is used to create the simple stored procedure in Structured Query Language:
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
The following syntax is used to execute the stored procedure in Structured Query Language:
EXEC Procedure_Name ;
Students table:
The following query creates the stored procedure which selects all the records from the above Student table:
CREATE PROCEDURE Show_All_Students
AS
SELECT * FROM Students
GO ;
Now, execute the stored procedure using the following query to see its output:
EXEC Show_All_Students;
Output:
The syntax for creating the stored procedure with one parameter:
CREATE PROCEDURE Procedure_Name @Parameter_Name Datatype (size of the Parameter)
AS
SQL Statement
The following query executes the above-stored procedure and shows the record of B.tech students in the output:
EXEC Show_Particular_Course_Student @Student_Course = ‘B.tech’;
Output:
The syntax for creating the stored procedure with more than one parameter:
CREATE PROCEDURE Procedure_Name @Parameter_Name_1 Datatype (size of the Parameter_1), @Parameter_Name_2 Datatype (size of the Parameter_2), ….., @Parameter_Name_N Datatype (size of the Parameter_N)
AS
SQL Statement
The syntax for executing the stored procedure with multiple parameters:
EXEC Procedure_Name @Parameter_Name1 = Value, @Parameter_Name2 = Value, ….., @Parameter_NameN = Value;
The following query creates the stored procedure which shows the students of a particular course with a particular age from the above table:
CREATE PROCEDURE Show_Particular_Course_Age_Student @Student_Course nvarchar(50), @Student_AgeINT
AS
SELECT * FROM Students WHERE Student_Course = @Student_Course AND Student_Age =@Student_Age
The following query executes the stored procedure and shows the record of those students in the output whose course is B.tech and Age is 20:
EXEC Show_Particular_Course_Student @Student_Course = ‘B.tech’, @Student_Age = 20;
Output:
Course Video
NOTE: Practice below practice questions on MSSQL SERVER, it will not execute on site editor.
1.Create one simple store procedure with getting all Human Resources Employee data
(290 rows count)
Output on SQL server
2. Write a SQL query to create a stored procedure that takes a city name as an input parameter and retrieves the data of Address ID, Address line 1, City, Postal Code, and Modified Date from the ‘Person.Address’ table where the city [ Bothell (26 rows), Monroe (16 rows), Ottawa (7 rows)] matches the input parameter.
Output on sql server
Output of city Bothell (26 rows)
Output of city Monroe (16 rows)
3. Create one simple table and create insert Store Procedure then insert value through Store Procedure.
4.Create a stored procedure to retrieve data from two tables, namely Person.EmailAddress and Person.Password.
The procedure should fetch only the top 10 records from each table separately without applying
any filtering or additional operations.
Hint: Use two different select statement to create procedure
5. Write a sql query to delete store procedure from the database
YouTube Reference :
1) SQL Stored Procedures in Hindi/Urdu
2) SQL Stored Procedures in English
