Post reply

Using @@ROWCOUNT To Return Second Recordset in Sproc

  • July 8, 2019 at 9:38 pm

    #3660949

    I'm working with a Stored Procedure that hopefully will return two recordsets to a Classic ASP application.

    Currently, when I test my Stored Procedure, @@ROWCOUNT always returns the count of 1, even when the count is not 1.

    Here is my code for the Stored Procedure (below).

    Would I need to return the @row variable in a different way?  Thanks for any leads.

    USE [cop]
    GO
    /****** Object:  StoredProcedure [dbo].[01_cms_search_pg_select_news_items_4]    Script Date: 7/8/2019 4:23:52 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROCEDURE [dbo].[01_cms_search_pg_select_news_items_4]

    @InterestName varchar(50),
    @ActiveDate datetime,
    @ExpireDate datetime,
    @row int output


    AS
    SET NOCOUNT OFF
    DECLARE @sql nvarchar(4000)

    SELECT @sql = ' SELECT * ' +
                  ' FROM news ' +
      ' WHERE ' +
      ' bulletin_display_indicator = ''true'' '+
      ' AND ' +
      ' website_homepg_display_indicator= ''false'' ' 

      
     IF @InterestName is not null 
     SELECT @sql = @sql + ' AND (InterestName = @InterestName) ' 


     IF @ExpireDate is not null
     SELECT @sql = @sql + ' AND (expiredate between @ActiveDate and @ExpireDate) ' 


    SELECT @sql = @sql + '; '

    set @row = @@ROWCOUNT;
    SELECT @row;
    return @row;

    EXEC sp_executesql @sql, N'@InterestName varchar(50), @ActiveDate DateTime, @ExpireDate DateTime',@InterestName, @ActiveDate, @ExpireDate

  • July 8, 2019 at 9:57 pm

    #3660952

    Get the row count after executing the SQL statement instead of before.

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply