Hi,
Can anyone please help me with this.
Below is a stored procedure given by Tika few days before in this group.
Could you please take a look at the bolded part at the end of the stored
procedure and tell me how can I retrieve the RECORDCOUNT value using C#
Command object.
Just scroll to the end of the stored procedure and you will find a
bolded statement. The stored procedure returns two record sets, and the
in the second record set is the value RECORDCOUNT.
CREATE PROCEDURE GetData_SP_V2(
@strFields varchar(4000),
@strPK varchar(100),
@strTables varchar(4000),
@intPageNo int = 1,
@intPageSize int = NULL,
@blnGetRecordCount bit = 0,
@strFilter varchar(8000) = NULL,
@strSort varchar(8000) = NULL,
@strGroup varchar(8000) = NULL)
/* Executes a SELECT statement that the parameters define,and returns
a particular page of data (or all
rows) efficiently. */
AS
DECLARE @blnBringAllRecords bit
DECLARE @strPageNo varchar(50)
DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilterCriteria varchar(8000)
DECLARE @strSimpleFilter varchar(8000)
DECLARE @strSortCriteria varchar(8000)
DECLARE @strGroupCriteria varchar(8000)
DECLARE @intRecordcount int
DECLARE @intPagecount int
/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute
a more efficient query, so we will
set a flag that will help avoid paging (blnBringAllRecords). */
IF @intPageNo < 1
SET @intPageNo = 1
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
IF @intPageSize IS NULL OR @intPageSize < 1 -- Bring all
records, don't do paging.
SET @blnBringAllRecords = 1
ELSE
BEGIN
SET @blnBringAllRecords = 0
SET @strPageSize = CONVERT(varchar(50), @intPageSize)
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
SET @strSkippedRows = CONVERT(varchar(50), @intPageSize *
(@intPageNo - 1))
END
/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting,
respectively, by executing more efficient
queries. */
IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilterCriteria = ''
END
IF @strSort IS NOT NULL AND @strSort != ''
SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
SET @strSortCriteria = ''
IF @strGroup IS NOT NULL AND @strGroup != ''
SET @strGroupCriteria = ' GROUP BY' + @strGroup + ' '
ELSE
SET @strGroupCriteria = ''
/* Now start doing the real work. */
IF @blnBringAllRecords = 1 -- Ignore paging and run a
simple SELECT.
BEGIN
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables +
@strFilterCriteria +
@strGroupCriteria + @strSortCriteria
)
END -- We had
to bring all records.
ELSE -- Bring
only a particular page.
BEGIN
IF @intPageNo = 1 -- In this case we
can execute a more efficient
--
query with no subqueries.
EXEC (
'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' +
@strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
ELSE
-- Execute a structure of
subqueries that brings the correct page.
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' +
@strPK + ' IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' +
@strTables +
' WHERE ' + @strPK + ' NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' ' + @strPK + '
FROM ' + @strTables +
@strFilterCriteria + @strGroupCriteria +
@strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria
)
END -- We had to
bring a particular page.
/* If we need to return the recordcount: */
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM '
+
@strTables + @strFilterCriteria + @strGroupCriteria + ')
AS tbl (id)'
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
@strFilterCriteria +
@strGroupCriteria
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor ---------------------~-->
Buy Ink Cartridges or Refill Kits for your HP, Epson, Canon or Lexmark
Printer at MyInks.com. Free s/h on orders $50 or more to the US & Canada.
http://www.c1tracking.com/l.asp?cid=5511
http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/saFolB/TM
---------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
