Are there existing, elegant templates for the optional TOP offer?

Question

Take the (simplified) stored procedure defined here:

create procedure get_some_stuffs
  @max_records int = null
as
begin
  set NOCOUNT on

  select top (@max_records) *
  from my_table
  order by mothers_maiden_name
end

I want to limit the number of records selected only under the condition @max_records.

Problems:

• The real request is nasty and big; I want to avoid duplication similar to this:

  if(@max_records is null)
  begin
    select *
    from {massive query}
  end
  else
  begin
    select top (@max_records)
    from {massive query}
  end
  

• An arbitrary set value does not have the right:

  select top (ISNULL(@max_records, 2147483647)) *
  from {massive query}
  

  For example, if @max_records- null, but {massive query}returns fewer 2147483647lines, then this will be identical:

  select * 
  from {massive query}
  

  or is there some kind of penalty for choosing top (2147483647) *from a table with only 50 rows?

Are there any other existing patterns that allow an optional set of results with an account limit without duplicating queries or sentinel values?

Answer 1

, IF Problem 1, . , CTE ( ):

CREATE PROC get_some_stuffs
(
    @max_records int = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    WITH staged AS (
        -- Only write the main query one time
        SELECT * FROM {massive query}
    )
    -- This part below the main query never changes:
    SELECT * 
    FROM (
        -- A little switcheroo based on the value of @max_records
        SELECT * FROM staged WHERE @max_records IS NULL
        UNION ALL
        SELECT TOP(ISNULL(@max_records, 0)) * FROM staged WHERE @max_records IS NOT NULL
    ) final
    -- Can't use ORDER BY in combination with a UNION, so move it out here
    ORDER BY mothers_maiden_name
END

, , UNION ALL, .

ISNULL(@max_records, 0) , TOP NULL , .

Answer 2

( ):

create procedure get_some_stuffs
  @max_records int = null
as
begin
  set NOCOUNT on

  select top (ISNULL(@max_records,1000)) *
  from my_table
  order by mothers_maiden_name
end

, ( 2), , ( ).

Answer 3

- ( , - )?

create procedure get_some_stuffs
  @max_records int = null
as
begin
  set NOCOUNT on

  select *, ROW_NUMBER(OVER order by mothers_maiden_name) AS row_num
  from {massive query}
  WHERE @max_records IS NULL OR row_num < @max_records
end

, { }, - ( ), , , .

Answer 4

, , , , , . , ORDER BY?

TOP (100) PERCENT View, PERCENT , ORDER BY, , SQL Server ORDER BY, .

, procs:

--Note the lack of a real parametrized column. See notes below.
IF OBJECT_ID('[dbo].[USP_TopQuery]', 'U') IS NULL
    EXECUTE('CREATE PROC dbo.USP_TopQuery AS ')
GO
ALTER PROC [dbo].[USP_TopQuery] @MaxRows NVARCHAR(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(4000) = N'SELECT * FROM dbo.ThisFile'
      , @Option NVARCHAR(50) = 'TOP (' + @MaxRows + ') *'
IF ISNUMERIC(@MaxRows) = 0
    EXEC sp_executesql @SQL     
ELSE
    BEGIN
        SET @SQL = REPLACE(@SQL, '*', @Option)
        EXEC sp_executesql @SQL
    END
END

IF OBJECT_ID('[dbo].[USP_TopQuery2]', 'U') IS NULL
    EXECUTE('CREATE PROC dbo.USP_TopQuery2 AS ')
GO
ALTER PROC [dbo].[USP_TopQuery2] @MaxRows INT NULL
AS
BEGIN
DECLARE @Rows INT;
    SET @Rows = @MaxRows;

IF @MaxRows IS NULL
    SELECT *
    FROM dbo.THisFile   
ELSE
    SELECT TOP (@Rows) *
    FROM dbo.THisFile
END

Sniffing,

IF OBJECT_ID('[dbo].[USP_TopQuery3]', 'U') IS NULL
    EXECUTE('CREATE PROC dbo.USP_TopQuery3 AS ')
GO
ALTER PROC [dbo].[USP_TopQuery3] @MaxRows INT NULL
AS
BEGIN

IF @MaxRows IS NULL
    SELECT *
    FROM dbo.THisFile   
ELSE
    SELECT TOP (@MaxRows) *
    FROM dbo.THisFile
END

, :

SQL Server Stored Procs , .

, SQL Server , , .

: , .

• • sp_executesql , SQL-.
  • , TOP ( , )
  • , , (ON, WHERE, HAVING)
  • RECOMPILE, .

• • , , , , ( , ).

• , , . , , SQL Server Scan vs Seek, . p >

• , , , USP_TopQuery3. .

  • , , , , :

, " , - , ! ...

" SELECT, - !

SQL SERVER - -

Answer 5

SET ROWCOUNT:

create procedure get_some_stuffs
  @max_records int = null
as
begin
  set NOCOUNT on

  IF @max_records IS NOT NULL
  BEGIN
     SET ROWCOUNT @max_records
  END

  select top (@max_records) *
  from my_table
  order by mothers_maiden_name

  SET ROWCOUNT 0

end