问题描述
我在存储过程中有一个动态查询(有效).它从较大的记录集中获取从记录号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
这篇关于动态查询和返回值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!