4 added 19 characters in body
source | link

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values - you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here. (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.entry = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and writing a slightly different join (thanks @ypercube).

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY
  AS
  BEGIN
    SET NOCOUNT ON;
    SELECT <cols> FROM dbo.<table> AS t
      INNER JOIN @t AS tvp
      ON  t.entry BETWEEN>= tvp.LowerBound 
      AND t.entry <= tvp.UpperBound;
  END
  GO

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values - you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here. (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.entry = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and writing a slightly different join (thanks @ypercube).

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY
  AS
  BEGIN
    SET NOCOUNT ON;
    SELECT <cols> FROM dbo.<table> AS t
      INNER JOIN @t AS tvp
      ON t.entry BETWEEN tvp.LowerBound AND tvp.UpperBound;
  END
  GO

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values - you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here. (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.entry = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and writing a slightly different join (thanks @ypercube).

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY
  AS
  BEGIN
    SET NOCOUNT ON;
    SELECT <cols> FROM dbo.<table> AS t
      INNER JOIN @t AS tvp
      ON  t.entry >= tvp.LowerBound 
      AND t.entry <= tvp.UpperBound;
  END
  GO
3 deleted 639 characters in body
source | link

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values (you- you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here). (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.<col>entry = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and building the query dynamically based on the rangeswriting a slightly different join (thanks @ypercube).

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY,
    @debug bit = 0
  AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max) = N'SELECTSELECT <cols>
      FROM dbo.<table> AS t
      WHEREINNER 1JOIN =@t 0AS ';
tvp
    SELECT @sql += N'
     ORON (t.entry BETWEEN '
       + RTRIM(tvp.LowerBound) + N' AND '
       + RTRIM(UpperBound) + N')' 
    FROM @t;

    IF @debug = 1
    BEGIN
      PRINT @sql;
    END
    ELSE
    BEGIN
      EXEC systvp.sp_executesql @sql;
    ENDUpperBound;
  END
  GO

To test:

  DECLARE @test dbo.optionB;
  INSERT @test(LowerBound, UpperBound) VALUES
    (1,3), (6,10), (12,13), (15,20);
  EXEC dbo.procedure_optionB @t = @test, @debug = 1;

Result:

SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 
     OR (entry BETWEEN 1 AND 3)
     OR (entry BETWEEN 6 AND 10)
     OR (entry BETWEEN 12 AND 13)
     OR (entry BETWEEN 15 AND 20)

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values (you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here). (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.<col> = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and building the query dynamically based on the ranges.

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY,
    @debug bit = 0
  AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max) = N'SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 ';

    SELECT @sql += N'
     OR (entry BETWEEN '
       + RTRIM(LowerBound) + N' AND '
       + RTRIM(UpperBound) + N')' 
    FROM @t;

    IF @debug = 1
    BEGIN
      PRINT @sql;
    END
    ELSE
    BEGIN
      EXEC sys.sp_executesql @sql;
    END
  END
  GO

To test:

  DECLARE @test dbo.optionB;
  INSERT @test(LowerBound, UpperBound) VALUES
    (1,3), (6,10), (12,13), (15,20);
  EXEC dbo.procedure_optionB @t = @test, @debug = 1;

Result:

SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 
     OR (entry BETWEEN 1 AND 3)
     OR (entry BETWEEN 6 AND 10)
     OR (entry BETWEEN 12 AND 13)
     OR (entry BETWEEN 15 AND 20)

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values - you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here. (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.entry = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and writing a slightly different join (thanks @ypercube).

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY
  AS
  BEGIN
    SET NOCOUNT ON;
    SELECT <cols> FROM dbo.<table> AS t
      INNER JOIN @t AS tvp
      ON t.entry BETWEEN tvp.LowerBound AND tvp.UpperBound;
  END
  GO
2 added 112 characters in body
source | link

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values (you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here). (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.<col> = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and building the query dynamically based on the ranges.

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY,
    @debug bit = 0
  AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max) = N'SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 ';

    SELECT @sql += N'
     OR (entry BETWEEN '
       + RTRIM(LowerBound) + N' AND '
       + RTRIM(UpperBound) + N')' 
    FROM @t;

    IF @debug = 1
    BEGIN
      PRINT @sql;
    END
    ELSE
    BEGIN
      EXEC sys.sp_executesql @sql;
    END
  END
  GO

To test:

  DECLARE @test dbo.optionB;
  INSERT @test(LowerBound, UpperBound) VALUES
    (1,3), (6,10), (12,13), (15,20);
  EXEC dbo.procedure_optionB @t = @test, @debug = 1;

Result:

SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 
     OR (entry BETWEEN 1 AND 3)
     OR (entry BETWEEN 6 AND 10)
     OR (entry BETWEEN 12 AND 13)
     OR (entry BETWEEN 15 AND 20)

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values (you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here).

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.<col> = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and building the query dynamically based on the ranges.

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY,
    @debug bit = 0
  AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max) = N'SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 ';

    SELECT @sql += N'
     OR (entry BETWEEN '
       + RTRIM(LowerBound) + N' AND '
       + RTRIM(UpperBound) + N')' 
    FROM @t;

    IF @debug = 1
    BEGIN
      PRINT @sql;
    END
    ELSE
    BEGIN
      EXEC sys.sp_executesql @sql;
    END
  END
  GO

To test:

  DECLARE @test dbo.optionB;
  INSERT @test(LowerBound, UpperBound) VALUES
    (1,3), (6,10), (12,13), (15,20);
  EXEC dbo.procedure_optionB @t = @test, @debug = 1;

Result:

SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 
     OR (entry BETWEEN 1 AND 3)
     OR (entry BETWEEN 6 AND 10)
     OR (entry BETWEEN 12 AND 13)
     OR (entry BETWEEN 15 AND 20)

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values (you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here). (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.<col> = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and building the query dynamically based on the ranges.

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY,
    @debug bit = 0
  AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max) = N'SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 ';

    SELECT @sql += N'
     OR (entry BETWEEN '
       + RTRIM(LowerBound) + N' AND '
       + RTRIM(UpperBound) + N')' 
    FROM @t;

    IF @debug = 1
    BEGIN
      PRINT @sql;
    END
    ELSE
    BEGIN
      EXEC sys.sp_executesql @sql;
    END
  END
  GO

To test:

  DECLARE @test dbo.optionB;
  INSERT @test(LowerBound, UpperBound) VALUES
    (1,3), (6,10), (12,13), (15,20);
  EXEC dbo.procedure_optionB @t = @test, @debug = 1;

Result:

SELECT <cols>
      FROM dbo.<table> AS t
      WHERE 1 = 0 
     OR (entry BETWEEN 1 AND 3)
     OR (entry BETWEEN 6 AND 10)
     OR (entry BETWEEN 12 AND 13)
     OR (entry BETWEEN 15 AND 20)
1
source | link