SQL存储过程中的输入参数

如何使用SQL存储过程中的输入参数，或使用示例在Select、Insert和Update存储过程中使用输入参数。

我建议您参考存储过程简介文章来了解存储过程的基础知识。在此输入参数演示中，我们将使用下面显示的SQL表。

SQL存储过程Select语句中的输入参数

在本例中，我们将向您展示如何在Select存储过程中使用输入参数。请参阅Select存储过程文章，了解如何在存储过程中编写Select语句。

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

Messages
--------
Command(s) completed successfully.

我将执行存储过程。在这里，我们需要为@Occupation参数传递值。所以，我将通过以下任何一种方式传递参数值。

EXEC [dbo].[spGetEmployeeDetails] N'Management';
GO
--OR You Can Write
EXEC [dbo].[spGetEmployeeDetails] @Occupation = N'Professional';
GO

Select存储过程示例2中的输入参数

在本例中，我们将在存储过程中的Select语句中使用多个输入参数。

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

Messages
--------
Command(s) completed successfully.

以下是执行存储过程的几种方法。我将执行sp。

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

SQL存储过程Insert语句中的输入参数

在本例中，我们将演示如何在Insert存储过程中使用输入参数。请参阅Insert存储过程文章，了解如何在存储过程中编写Insert语句。

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

运行存储过程输入参数查询

Messages
--------
Command(s) completed successfully.

我将执行存储过程。在这里，您可以省略参数名称。但是，您仍然必须按照创建过程中指定的相同顺序提供参数值。

EXEC  [dbo].[spInsertEmployeeDetails]
	@FirstName = 'Tutorial',
	@LastName = 'Gateway',
	@Education = 'Masters Degree',
	@Occupation = 'Management',
	@YearlyIncome = 120000,
	@Sales = 4580.987

我将向您展示该表。

存储过程Update语句中的输入参数

本例演示了如何在Update存储过程中使用输入参数。请参考Update存储过程文章，了解如何在存储过程中编写Update语句。

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

运行存储过程输入参数查询

Messages
--------
Command(s) completed successfully.

以下是执行存储过程的几种方法。我将使用输入参数执行sp。

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

我将向您展示该表。