如何从SQL Server中的存储过程返回表。 [英] How to return table from store procedure in SQL server.

问题描述

我希望在使用带有简单示例的存储过程对表执行插入或更新或删除时返回一个表。



我想要返回表格格式中的查询结果。



所以当我尝试下面的代码时，我得到返回0。



我尝试过：

  ALTER   PROCEDURE  [dbo]。[Sp_Create_employee] 
   -   在此处添加存储过程的参数 
  @ employeename   varchar （ 20 ），
  @ Isactive  位，
  @ CreatedOn   datetime ，
 < span class =code-sdkkeyword> @创建通过  int  
  AS  
  BEGIN  
  DECLARE   @ tab  表 
（
 Employeeid  int ，
 employeename  varchar （ 20 ），
 Isactive 位<​​/ span>，
 CreatedOn  datetime ，
 CreatedBy  int ）
 
  SET   NOCOUNT   ON ; 
 
  INSERT   INTO  [dbo]。[员工] 
（ [employeename] 
，[Isactive] 
，[CreatedOn] 
，[CreatedBy]）
  VALUES  
（<跨度类= 代码sdkkeyword> @ employeename ，<跨度类= 代码sdkkeyword> @ Isactive ，<跨度类= 代码sdkkeyword> @ CreatedOn ， @ CreatedBy ）
 
  insert   @标签 <跨度类= 代码关键字 >选择 [EMPLOYEEID]，[employeename]，[Isactive]，[CreatedOn]，[CreatedBy] <跨度类= 代码 - 关键字> FROM  [PACTS]。[dbo]。[Employee] 
 
  RETURN  选择 * 来自  @ tab  
 
  END  

解决方案

1。不要使用表变量。至少在你的例子中没有理由，它会减慢你的过程。

2.不要使用RETURN。只需编写一个select语句，然后当你从C＃执行它时，它将是一个表。

Quote：

  RETURN  选择 * 来自  @ tab  

这就是T-的耻辱SQL不要求使用分号来终止每个语句 - 在一行上放两个不同的语句太容易了，并假设它们是单个语句。

RETURN（Transact-SQL） | Microsoft Docs [ ^ ]

 RETURN [integer_expression] 

应用可选语句终止符，代码变为：

  RETURN ; 
 选择 * 来自  @ tab ; 

现在很明显这些是两个单独的陈述。 RETURN 退出存储过程，执行后不会执行任何操作，包括以下行中的 SELECT 语句。



假设您使用的是SQL Server 2005或更高版本，并且只需要新插入记录的详细信息，OUTPUT条款 [ ^ ]可能是最简单的选项：

  ALTER  程序 [dbo]。[Sp_Create_employee] 
  @ employeename   varchar （ 20 ） ，
  @ Isactive  位<​​/ span>，
  @CreatedOn   datetime ，
  @ CreatedBy   int  
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
 
  INSERT   INTO  [dbo]。[员工] 
（ 
 [employeename]，
 [Isactive]，
 [CreatedOn]，
 [CreatedBy] 
）
  OUTPUT  
插入。[Employeeid]，
插入。[employeename]，
插入。[Isactive]，
插入。[CreatedOn]，
插入[CreatedBy] 
 <跨度类= 代码关键字> VALUES  
（
 <跨度类= 代码sdkkeyword> @ employeename ，
'跨度类= 代码sdkkeyword> @ Isactive ，
 <跨度类= 代码sdkkeyword> @ CreatedOn ，
 <跨度类= code-sdkkeyword> @ CreatedBy  
）; 
  END  

否则，如果您想要整个表的数据，正如您的问题所示，在 INSERT 之后添加 SELECT 。但是不要把 RETURN 放在它前面！

你不需要为retrieve表创建表变量。 br />


您的代码如下：

  ALTER   PROCEDURE  [dbo]。[Sp_Create_employee] 
   -   在此处添加存储过程的参数 
  @ employeename   varchar （ 20 ），
  @ Isactive  位<​​/ span>，
  @ CreatedOn   datetime ，
  @ CreatedBy   int  
  AS  
  BEGIN  
 
  SET   NOCOUNT   ON < /跨度>; 
 
  INSERT   INTO  [dbo]。[员工] 
（ [employeename] 
，[Isactive] 
，[CreatedOn] 
，[CreatedBy]）
  VALUES  
（<跨度类= 代码sdkkeyword> @ employeename ，<跨度类= 代码sdkkeyword> @ Isactive ，<跨度类= 代码sdkkeyword> @ CreatedOn ， @ CreatedBy ）
 
  SELECT  [Employeeid]，[employeename] ，[Isactive]，[CreatedOn]，[CreatedBy]  FROM  [PACTS]。[dbo]。[Employee] 
 
  END  

I want a table to be returned when an insert or update or delete is performed on the table using a stored procedure with a simple example.

I want return result of the query in table formate.

so when i am trying the below code i am getting get return 0 .

What I have tried:

ALTER PROCEDURE [dbo].[Sp_Create_employee]
	-- Add the parameters for the stored procedure here
	@employeename varchar(20), 
	@Isactive bit,
	@CreatedOn  datetime,
	@CreatedBy int
AS
BEGIN
	 DECLARE @tab table 
 (
 Employeeid int,
 employeename varchar(20),
 Isactive bit,
 CreatedOn datetime,
 CreatedBy int)

	SET NOCOUNT ON;

	 INSERT INTO [dbo].[Employee]
           ([employeename ]
           ,[Isactive]
           ,[CreatedOn]
           ,[CreatedBy])
     VALUES
           (@employeename,@Isactive,@CreatedOn,@CreatedBy)
   
   insert @tab SELECT [Employeeid],[employeename],[Isactive],[CreatedOn],[CreatedBy] FROM [PACTS ].[dbo].[Employee]    
   
    RETURN select * from @tab
	
  END

解决方案

1. Do not use a table variable. At least in your example here there is no reason for it and it will slow down your process.
2. Do not use RETURN. Simply write a select statement and then when you execute it from C# it will be a table.

Quote:

RETURN select * from @tab

This is why it's a shame that T-SQL doesn't mandate semi-colons to terminate every statement - it's too easy to put two different statements on one line, and assume they're a single statement.

RETURN (Transact-SQL) | Microsoft Docs[^]

RETURN [ integer_expression ]

Applying the optional statement terminators, your code becomes:

RETURN;
select * from @tab;

Now it should be obvious that these are two separate statements. The RETURN exits the stored procedure, and nothing that follows it will be executed, including the SELECT statement on the following line.

Assuming you're using SQL Server 2005 or later, and you only want the details of the newly-inserted record, the OUTPUT Clause[^] would probably be the simplest option:

ALTER PROCEDURE [dbo].[Sp_Create_employee]
    @employeename varchar(20), 
    @Isactive bit,
    @CreatedOn  datetime,
    @CreatedBy int
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO [dbo].[Employee]
    (
        [employeename ],
        [Isactive],
        [CreatedOn],
        [CreatedBy]
    )
    OUTPUT
        inserted.[Employeeid],
        inserted.[employeename],
        inserted.[Isactive],
        inserted.[CreatedOn],
        inserted.[CreatedBy]
    VALUES
    (
        @employeename,
        @Isactive,
        @CreatedOn,
        @CreatedBy
    );
END

Otherwise, if you want the data for the entire table, as your question shows, add a SELECT after the INSERT. But don't put RETURN in front of it!

You don't have need to create table variable for retrieve table.

Your code should like following:

ALTER PROCEDURE [dbo].[Sp_Create_employee]
	-- Add the parameters for the stored procedure here
	@employeename varchar(20), 
	@Isactive bit,
	@CreatedOn  datetime,
	@CreatedBy int
AS
BEGIN
	 
	SET NOCOUNT ON;
 
	 INSERT INTO [dbo].[Employee]
           ([employeename ]
           ,[Isactive]
           ,[CreatedOn]
           ,[CreatedBy])
     VALUES
           (@employeename,@Isactive,@CreatedOn,@CreatedBy)
   
   SELECT [Employeeid],[employeename],[Isactive],[CreatedOn],[CreatedBy] FROM [PACTS].[dbo].[Employee]

  END

这篇关于如何从SQL Server中的存储过程返回表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！