从具有多个返回值的 select 语句中检索输出参数值以及其他返回值 [英] Retrieving output parameter value along with other return values from select statement having more than one return value

问题描述

我有一个存储过程的输出参数，它的值必须从两个内部选择语句中返回.我有 @nTotalRecords 作为我的输出参数，该值将来自下面的选择语句我应该如何从别名表 TBL 中检索输出参数在这种情况下我试过这样

Hi i have an output parameter to a stored procedure and its value has to be returned from the two internal select statements. I have @nTotalRecords as my output parameter that value will come from the below select statement how should i retrieve the output parameter from the alias table TBL in this case i tried like this

create procedure [usp_GetMessagesbyReferenceID1]
(        
@nRowsPerPage               int,
@nPage                      int,
@nTotalRecords              int output 
) as
select  
    TBL.createdate,
    TBL.templateid,
    @nTotalRecords=TBL.TotalRecords
    from
     (
            select
                  message.createdate,
                  message.templateid,
                  count(1) over() as TotalRecords
            from
                  nts.Messages as [message]    
     )  as TBL

我正在尝试以这种方式设置输出参数，但它不起作用，它抛出以下错误为变量赋值的 SELECT 语句不得与数据检索操作结合使用. 有没有可能做到这一点.?或者我在语法上犯了什么错误，请帮帮我.

i am trying it this way to set the output parameter but it is not working it is throwing the following error A SELECT statement that assigns a value to a variable must not be combined with data retrieval operations.is there any possibility to achieve this. ? Or am i making any mistake syntactically , Please help me out.

注意:如果仅返回一个值(输出参数值)，则从 select 语句中检索输出参数可以正常工作，但我的要求是当我的 select 语句返回多个值(包括输出参数)时，它应该可以正常工作.

NOTE: The retrieving of output parameter from select statement works fine if it returns only one value(output parameter value), but my requirement is it should work properly when my select statement returns more than one value(including output parameter).

推荐答案

我已经尝试了很多来克服这个问题，但最终我通过这种方式实现了这一点，我又写了一个 select 语句来获取总记录数.我不确定答案是否完美和有效，但它对我来说效果很好.如果有其他方法，请告诉我.

I have tried a lot to conquer this, but atlast i have achieved this through this way, I have written one more select statement to get the totalrecords count. I'm not sure that whether the answer was perfect and efficient, but it just worked fine for me. Please let me know if there are any other ways.

create procedure [usp_GetMessagesbyReferenceID1]
(        
@nRowsPerPage               int,
@nPage                      int,
@nTotalRecords              int output 
) as
select  
   TBL.createdate,
   TBL.templateid
from
   (
      select
         message.createdate,
         message.templateid
      from
         nts.Messages as [message]    
   )  as TBL

 select 
    @nTotalRecords = count(1)
 from 
    nts.Messages as [message]

这篇关于从具有多个返回值的 select 语句中检索输出参数值以及其他返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！