动态查询和返回值 [英] Dynamic query and return value

问题描述

我在存储过程中有一个动态查询(有效).它从较大的记录集中获取从记录号X开始的N条记录.现在，我需要将较大记录集的计数返回给调用应用程序，并且我不想创建另一个存储的proc来执行此操作.

我在store proc的原型中定义了OUT参数，但是我一直无法找到一种方法来将计数添加到该参数中.它一直告诉我我必须定义标量参数@totalRecords，但是已经定义了.

我的查询看起来像这样"

存储过程如下所示:

I have a dynamic query (that works) in a stored procedure. It gets N records starting at record number X from a larger set of records. I now need to return the count of the larger set of records to the calling application, and I don''t want to have to create another stored proc to do it.

I defined the OUT parameter in the store proc''s prototype, but I haven''t been able to find a way to get the count into the parameter. It keeps telling me I have to define the scalar parameter @totalRecords, but it IS already defined.

My query looks something like this"

The stored procedure looks something like this:

sp_mySP( parameters..., @totalRecords int OUT)
as
begin
    -- the query string essentially looks like this (comments added for clarity):

    set @queryStr = 
    -- drop temptable if it exists
    + 'if #temptable exists drop #temptable; '

    -- gets the data specified by parameters
    + 'select * into #temptable from mytable where blah blah; '

    -- dets the data in the specified rows
    + 'select * from #temptable where blah blah; '

    -- counts the records in #temptable and stores them in out parameter
    + ' set @totalRecords = (select count(*) from #temptable); '

    -- at this point, I call 
    exec (@queryStr)
end

如何在动态查询中设置@totalRecords?

编辑===============

我也尝试过:

将字符串的最后一部分更改为此:

How do I set @totalRecords inside a dynamic query?

EDIT ===============

I''ve also tried this:

Change the last part of the string to this:

+ ' set @outCount = (select count(*) from #temptable); '

然后这样做:

and then did this:

declare @outCounter int
exec sp_executesql @queryStr, N'@outCount int OUTPUT', @outCounter OUPUT
select @outCounter as counter

>

这导致outCounter设置为NULL

This results in outCounter being set to NULL

推荐答案

我最终这样做:

I ended up doing this:

sp_mySP( parameters..., @totalRecords int OUT)
as
begin
    -- the query string essentially looks like this (comments added for clarity):

    set @queryStr = 
    -- drop temptable if it exists
    + 'if #temptable exists drop #temptable; '
 
    -- gets the data specified by parameters
    + 'select * into #temptable from mytable where blah blah; '
 
    -- dets the data in the specified rows
    + 'select * from #temptable where blah blah; '
 
    -- counts the records in #temptable and stores them in out parameter
    -- I changed the variable here from the sp's out parameter to @outCount <<--------
    + ' set @outCount = (select count(*) from #temptable); '
 
    -- at this point, I call 
    declare @outCounter Int

    -- and change this line to be this: <<--------------
    execute (sp_executesql @queryStr, N'outCount int OUTPUT', @outCounter OUTPUT)

    -- and finally, I did this: <<--------------
    set @totalrecords = @outCounter
end

我尝试过这样对我有用的

i tried like this it worked for me

declare @cahr int
exec('select '+@cahr  + ' count(*) from dbo.Users')

我发现这篇文章是不错的参考.下面的示例摘自它

动态SQL [

This article I have found to be a good reference. The example below is taken from it

Dynamic SQL[^]

SELECT @sql =
  N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
  N' WHERE LastUpdated BETWEEN @fromdate AND ' +
  N'                           coalesce(@todate, ''99991231'')'
  SELECT @params = N'@fromdate datetime, ' +
                   N'@todate   datetime = NULL, ' +
                   N'@cnt      int      OUTPUT'
  EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

这篇关于动态查询和返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！