/****** Object:  StoredProcedure [dbo].[GetPagedDataByTableNameWithColumns]    Script Date: 17.04.2024 21:52:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetPagedDataByTableNameWithColumns]
    @TabloAdi NVARCHAR(100),
    @Alanlar NVARCHAR(MAX),
    @SayfaBoyutu INT,
    @SayfaNumarasi INT
	
AS

    SET NOCOUNT ON

    DECLARE @PrimaryKeyColumn NVARCHAR(100);
    DECLARE @Offset INT = (@SayfaNumarasi - 1) * @SayfaBoyutu;
    DECLARE @SqlQuery NVARCHAR(MAX);

    -- Tablonun anahtar sütununu belirleme
    SELECT @PrimaryKeyColumn = COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
    AND TABLE_NAME = @TabloAdi

    -- Anahtar sütunu bulunamazsa hata fırlat
    IF @PrimaryKeyColumn IS NULL
    BEGIN
        RAISERROR('Primary key column not found for table %s.', 16, 1, @TabloAdi);
        RETURN ;
    END

    SET @SqlQuery = '
        DECLARE @StartIndex INT = ' + CAST(@Offset + 1 AS NVARCHAR) + ';
        DECLARE @EndIndex INT = ' + CAST(@Offset + @SayfaBoyutu AS NVARCHAR) + ';

        WITH OrderedData AS (
            SELECT ' + @Alanlar + ',
                   ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@PrimaryKeyColumn) + ') AS RowNumber
            FROM ' + QUOTENAME(@TabloAdi) + '
        )
        SELECT *
        FROM OrderedData
        WHERE RowNumber BETWEEN @StartIndex AND @EndIndex;'

    EXEC sp_executesql @SqlQuery;
/*
for using example:

	EXEC GetPagedDataByTableNameWithColumns 'SampleTable','Name',50000, 80;

*/