In this article we will show you, How to use Input Parameters in SQL Stored Procedure , Or How to use Input Parameters in Select, Insert, and Update Stored Procedures with example. I suggest you to refer Introduction to Stored Procedure article to understand the basics of the stored procedure. For this demonstration, We are going to use the below shown SQL table
Input Parameters in SQL Stored Procedure Select Statement
In this example we will show you, How to use the Input Parameters in Select Stored procedures. Please refer Select Stored Procedure in SQL article to understand, How to write Select statement inside a stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Example for SQL Stored Procedure with Input Parameters USE [SQL Tutorial] GO CREATE PROCEDURE spGetEmployeeDetails @Occupation VARCHAR(50) AS BEGIN SET NOCOUNT ON; SELECT [FirstName] + ' ' + [LastName] AS [Full Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [EmployeeDuplicates] WHERE [Occupation] = @Occupation END |
OUTPUT
Let me execute the stored procedure. Here, we have to pass the value for the @Occupation parameter. So, let me pass the parameter value using any of the following ways
|
1 2 3 4 5 6 7 8 9 |
-- Example for SQL Stored Procedure with Input Parameters USE [SQL Tutorial] GO EXEC [dbo].[spGetEmployeeDetails] N'Management'; GO --OR You Can Write EXEC [dbo].[spGetEmployeeDetails] @Occupation = N'Professional'; GO |
OUTPUT
Input Parameters in SQL Stored Procedure Select Statement Example 2
In this example, we will use the Multiple Input parameters along with the Select statement inside a stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Example for SQL Stored Procedure with Input Parameters USE [SQL Tutorial] GO CREATE PROCEDURE spSelectEmployeeDetails @Education VARCHAR(50), @Occupation VARCHAR(50) AS BEGIN SET NOCOUNT ON; SELECT [FirstName] + ' ' + [LastName] AS [Full Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [EmployeeDuplicates] WHERE [Education] = @Education OR [Occupation] = @Occupation END |
OUTPUT
Following are the number of ways you can execute the stored procedure. Let me execute the sp.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Example for SQL Stored Procedure with Input Parameters USE [SQL Tutorial] GO EXEC [dbo].[spSelectEmployeeDetails] @Education = N'Masters Degree', @Occupation = N'Management'; GO -- OR You Can Write EXEC [dbo].[spSelectEmployeeDetails] @Occupation = N'Professional', @Education = N'Bachelors'; GO -- OR You Can Write. Order should be same as you mentioned while creating Stored Procedure EXEC [dbo].[spSelectEmployeeDetails] N'Bachelors', N'Professional'; GO |
OUTPUT
Input Parameters in SQL Stored Procedure Insert Statement
In this example we will show you, How to use the Input Parameters in Insert Stored procedures. Please refer Insert Stored Procedure in SQL article to understand, How to write Insert Statement inside a stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- Example for SQL Stored Procedure with Input Parameters USE [SQL Tutorial] GO CREATE PROCEDURE [dbo].[spInsertEmployeeDetails] @FirstName varchar(100), @LastName nvarchar(100), @Education nvarchar(200), @Occupation nvarchar(200), @YearlyIncome float, @Sales float AS BEGIN BEGIN TRY BEGIN TRANSACTION; INSERT INTO [EmployeeDuplicates] ([FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales], [HireDate]) VALUES (@FirstName, @LastName, @Education, @Occupation, @YearlyIncome, @Sales, GETDATE()) COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; THROW; END CATCH END |
OUTPUT
Let me execute the stored procedure. Here you can avoid the parameter names but you have to provide parameter values in the same order that you specified while creating procedure.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Example for SQL Stored Procedure with Input Parameters USE [SQL Tutorial] GO EXEC [dbo].[spInsertEmployeeDetails] @FirstName = 'Tutorial', @LastName = 'Gateway', @Education = 'Masters Degree', @Occupation = 'Management', @YearlyIncome = 120000, @Sales = 4580.987 |
OUTPUT
Let me show you the table.
Input Parameters in SQL Stored Procedure Update Statement
In this example we will show you, How to use the Input Parameters in Update Stored procedures. Please refer Update Stored Procedure in SQL article to understand, How to write Update Statement inside a stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- Example for SQL Stored Procedure with Input Parameters USE [SQL Tutorial] GO CREATE PROCEDURE spUpdateEmployeeDetails @Occupation VARCHAR(50), @Sales FLOAT AS BEGIN BEGIN TRY BEGIN TRANSACTION; SET NOCOUNT ON; UPDATE [EmployeeDuplicates] SET [FirstName] = N'SQL', [LastName] = N'Tutorial', [YearlyIncome] = 150000 WHERE [Occupation] = @Occupation OR [Sales] >= @Sales COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; THROW; END CATCH END |
OUTPUT
Following are the number of ways you can execute the stored procedure. Let me execute the sp.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Example for SQL Stored Procedure with Input Parameters USE [SQL Tutorial] GO EXEC [dbo].[spUpdateEmployeeDetails] @Sales = 4580.987, @Occupation = N'Management'; GO -- OR You Can Write EXEC [dbo].[spUpdateEmployeeDetails] @Occupation = N'Management', @Sales = 4580.987; GO -- OR You Can Write. Order should be same as you mentioned while creating Stored Procedure EXEC [dbo].[spUpdateEmployeeDetails] 4580.987, N'Management'; GO |
OUTPUT
Let me show you the table.
Thank you for Visiting Our Blog
Leave a Reply