且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

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

更新时间:2023-09-09 21:02:52

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

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


Quote:

  RETURN  选择 * 来自  @ tab  



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

 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 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 [ 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