动态查询和返回值

动态查询和返回值

本文介绍了动态查询和返回值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在存储过程中有一个动态查询(有效).它从较大的记录集中获取从记录号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

推荐答案

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


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


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


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

08-19 02:08