如何从SQL存储过程返回 [英] How to return from SQL stored procedure

问题描述

我有一个名为A 
的存储过程，其中 返回结果 as  
 选择 '  1 '  as  '  id'，'  success'  as  description 
 
我正在调用这个 Proc 来自另一个名为B 
的proc想收集这个结果A 
 
如何做 i  吗？

我尝试过：



我做了很多谷歌搜索，但未找到相关答案

解决方案

  DECLARE   @ ParmDefinition   nvarchar （ 500 ）= N '  @retvalOUT VARCHAR（500）OUTPUT'， @ retval   VARCHAR （ 500 ）

  DECLARE  < span class =code-sdkkeyword> @ SqlTxt   NVARCHAR （MAX）= ' < span class =code-string>把你的SQL查询放在这里'; 

执行sp_executesql @SqlTxt，@ ParmDefinition，@ retvalOUT = @ retval OUTPUT; 
选择@retval; - 它让我得到输出

你可以根据你的要求修改它

  CREATE   PROCEDURE  [dbo]。[ProcA] 
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
 选择  1  为 < span class =code-string>'  id'，'  success'  as  description 
  END  

  CREATE   TABLE  [dbo]。[SampleData]（
 [id] [ int ]  NULL ，
 [描述] [ varchar ]（ 50 ） NULL  
）

 创建  PROCEDURE  [dbo]。[procB] 
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
 将 插入 SampleData  exec  ProcA 
  END  

有关详细信息，请参阅是否可以将TABLE作为存储过程中的输出参数传递 [ ^ ]

创建SP并添加以下脚本

  INSERT   INTO  #tab  EXEC  处理程序 

和select * from #tab

I have a stored proc named A
which is returning result as 
		select '1' as 'id', 'success' as description
		
I am calling this Proc from another proc named B
		I want to collect this result of A
		
		How do i do it?

What I have tried:

I did lot of google search but did not find the relevant answer

解决方案

DECLARE @ParmDefinition nvarchar(500)=N'@retvalOUT VARCHAR(500) OUTPUT',@retval VARCHAR(500) 

DECLARE @SqlTxt NVARCHAR(MAX)='Put your SQL Query here';

EXECUTE sp_executesql @SqlTxt, @ParmDefinition, @retvalOUT=@retval OUTPUT;
Select @retval;  --It worked for me to get output

You can modify it according to your's requirement

CREATE PROCEDURE [dbo].[ProcA] 	
AS
BEGIN	
	SET NOCOUNT ON;    
	select 1 as 'id', 'success' as description
END

CREATE TABLE [dbo].[SampleData](
	[id] [int] NULL,
	[description] [varchar](50) NULL
)

CREATE PROCEDURE [dbo].[ProcB] 	
AS
BEGIN	
	SET NOCOUNT ON;
    Insert into SampleData exec ProcA
END

For more details refer Is it possible to pass TABLE as the output parameter in stored procedure[^]

Create SP and add below script

INSERT INTO #tab EXEC Procedure

and Select * from #tab

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