如何从SQL Server中的存储过程返回表。

本文介绍了如何从SQL Server中的存储过程返回表。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我希望在使用带有简单示例的存储过程对表执行插入或更新或删除时返回一个表。 我想要返回表格格式中的查询结果。 所以当我尝试下面的代码时，我得到返回0。 我尝试过： ALTER PROCEDURE [dbo]。[Sp_Create_employee] - 在此处添加存储过程的参数 @ employeename varchar （ 20 ）， @ Isactive 位， @ CreatedOn datetime ， @创建通过 int AS BEGIN DECLARE @ tab 表 （ Employeeid int ， employeename varchar （ 20 ）， Isactive 位， CreatedOn datetime ， CreatedBy int ） SET NOCOUNT ON ; INSERT INTO [dbo]。[员工] （ [employeename] ，[Isactive] ，[CreatedOn] ，[CreatedBy]） VALUES （ @ employeename ， @ Isactive ， @ 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 位， @CreatedOn datetime ， @ CreatedBy int AS BEGIN SET NOCOUNT ON ; INSERT INTO [dbo]。[员工] （ [employeename]， [Isactive]， [CreatedOn]， [CreatedBy] ） OUTPUT 插入。[Employeeid]，插入。[employeename]，插入。[Isactive]，插入。[CreatedOn]，插入[CreatedBy] VALUES （ @ employeename ，'跨度类= 代码sdkkeyword> @ Isactive ， @ CreatedOn ， @ CreatedBy ）; END 否则，如果您想要整个表的数据，正如您的问题所示，在 INSERT 之后添加 SELECT 。但是不要把 RETURN 放在它前面！ 你不需要为retrieve表创建表变量。 br /> 您的代码如下： ALTER PROCEDURE [dbo]。[Sp_Create_employee] - 在此处添加存储过程的参数 @ employeename varchar （ 20 ）， @ Isactive 位， @ CreatedOn datetime ， @ CreatedBy int AS BEGIN SET NOCOUNT ON ; INSERT INTO [dbo]。[员工] （ [employeename] ，[Isactive] ，[CreatedOn] ，[CreatedBy]） VALUES （ @ employeename ， @ Isactive ， @ 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 intASBEGIN 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 @tabThis 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 intASBEGIN 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 );ENDOtherwise, 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 intASBEGINSET 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中的存储过程返回表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！