locked
Invalid column name RowNum RRS feed

  • Question

  • User-1499457942 posted

    Hi

     Below is the Stored Procedure

    USE [TEST]
    GO
    /****** Object:  StoredProcedure [dbo].[Sp_GetTransporter]    Script Date: 2015-12-23 09:53:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[Sp_GetTransporter]
    @displayLength int,
    @displayStart int,
    @SortCol int,
    @SortDir nvarchar(10),
    @Search nvarchar(250) = null
        -- Add the parameters for the stored procedure here
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        Declare @FirstRec int, @LastRec int
        Set @FirstRec = @DisplayStart;
        Set @LastRec = @DisplayStart + @DisplayLength;
        With CTE_Transporter as
        (
            Select ROW_NUMBER() over (order by
                case when (@SortCol = 0 and @SortDir='asc')
                    then trcode
                end ,
                case when (@SortCol = 0 and @SortDir='desc')
                    then trcode
                end ,
                case when (@SortCol = 1 and @SortDir='asc')
                    then trname
                end ,
                case when (@SortCol = 1 and @SortDir='desc')
                    then trname
                end
            )
            as RowNum,
            COUNT(*) over() as TotalCount,
            trcode,
            trname
            from Transporter where (@Search IS NULL
            or trcode like '%' + @Search + '%'
            or trname like '%' + @Search + '%'
            )
        )
        -- Insert statements for procedure here
        select trcode,trname from Transporter
        where RowNum > @FirstRec and RowNum <= @LastRec
        SET NOCOUNT OFF
    end

    Thanks

    Wednesday, December 23, 2015 5:37 AM

Answers

  • User-62323503 posted

    Change table name in select statement

    select trcode,trname from CTE_Transporter
    where RowNum > @FirstRec and RowNum <= @LastRec

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 23, 2015 5:46 AM