SQL Server 中的 SELECT 存储过程

如何在 SQL Server 中编写 SELECT 存储过程？或者如何编写带有示例的存储过程中的 SELECT 语句。对于此 SELECT 存储过程演示，我们将使用下面显示的数据。

SQL Server 中的 SELECT 存储过程示例

在此 SQL Server 示例中，我们将向您展示如何在存储过程中使用 SELECT 语句。我建议您参考 存储过程简介 文章来了解基础知识。

-- Example for SELECT Statement Inside the SQL Stored Procedure

IF OBJECT_ID ( 'SelectStoredProcedureFirstExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE SelectStoredProcedureFirstExample;  
GO

CREATE PROCEDURE SelectStoredProcedureFirstExample
AS
BEGIN
      SET NOCOUNT ON;
      SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
            ,[Education]
            ,[Occupation]
            ,[YearlyIncome]
            ,[Sales]
            ,[HireDate]
      FROM [Employee]
END
GO

从上面的代码片段中，您可以看到我们正在将名字和姓氏连接起来作为全名。我们使用 SPACE 函数 在名字和姓氏之间提供一个空格。

SELECT Stored Procedure in SQL Server 1

我将使用 EXEC 命令（执行命令）来执行存储过程以检查结果。

EXEC [dbo].[SelectStoredProcedureFirstExample]
GO

SQL 存储过程中的多个 Select 语句

此示例将向您展示如何在存储过程中使用多个 SELECT 语句。从下面的代码片段中，您可以看到，

  • 首先，我们按升序对员工按销售额进行排序，并选择前 6 条记录。
  • 接下来，我们按销售额降序选择员工的前 4 条记录。
IF OBJECT_ID ( 'SelectStoredProcedureSecondExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE SelectStoredProcedureSecondExample;  
GO

CREATE PROCEDURE SelectStoredProcedureSecondExample
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TOP 6 [FirstName] + ' ' + [LastName] AS [Full Name]
          ,[Education]
          ,[Occupation]
          ,[YearlyIncome]
          ,[Sales]
          ,[HireDate]
	FROM [Employee]
	ORDER BY [Sales] ASC
	
	SELECT TOP 4 [FirstName] + ' ' + [LastName] AS [Full Name]
          ,[Education]
          ,[Occupation]
          ,[YearlyIncome]
          ,[Sales]
          ,[HireDate]
	FROM [Employee]
	ORDER BY [Sales] DESC
END
GO

在单个存储过程查询中运行上述多个 Select 语句。

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

我将执行存储过程以检查结果。

EXEC [dbo].[SelectStoredProcedureSecondExample]
GO
SELECT Stored Procedure 4

存储过程中的 Select 语句带参数

此示例创建了带有参数的 Select 存储过程。

IF OBJECT_ID ( 'SelectStoredProcedureThirdExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE SelectStoredProcedureThirdExample;  
GO
CREATE PROCEDURE SelectStoredProcedureThirdExample
	@Occupation VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
          ,[Education]
          ,[Occupation]
          ,[YearlyIncome]
          ,[Sales]
          ,[HireDate]
	FROM [Employee]
	WHERE [Occupation] = @Occupation
END
GO
Messages
--------
Command(s) completed successfully.

我将执行 Select 存储过程。

EXEC [dbo].[SelectStoredProcedureThirdExample]
GO
Expect Parameter input

如您所见，它正在抛出错误，说明：我们尚未为 @Occupation 参数传递值。因此，让我们使用以下任何一种方式传递参数值：

EXEC [dbo].[SelectStoredProcedureThirdExample] N'Professional';
GO
--OR You Can Write
EXEC [dbo].[SelectStoredProcedureThirdExample] @Occupation = N'Professional';
GO
SELECT Stored Procedure 7

存储过程中的 Select 语句带多个参数

我们在存储过程中的 Select 语句中使用了多个参数。

IF OBJECT_ID ( 'SelectStoredProcedureFourthExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE SelectStoredProcedureFourthExample;  
GO

CREATE PROCEDURE SelectStoredProcedureFourthExample
	@Education VARCHAR(50),
	@Occupation VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
          ,[Education]
          ,[Occupation]
          ,[YearlyIncome]
          ,[Sales]
          ,[HireDate]
	FROM [Employee]
	WHERE [Occupation] = @Occupation OR
	[Education] = @Education
END
GO
Messages
--------
Command(s) completed successfully.

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

EXEC [dbo].[SelectStoredProcedureFourthExample] @Education = N'Masters Degree', @Occupation = N'Management';
GO
--OR You Can Write
EXEC [dbo].[SelectStoredProcedureFourthExample] @Occupation = N'Professional',  @Education = N'Masters Degree';
GO

--OR You Can Write
EXEC [dbo].[SelectStoredProcedureFourthExample] N'Masters Degree', N'Management';
GO

评论已关闭。