Dynamic tsql help

• lleemon13

  Ten Centuries

  Points: 1063

  More actions

  September 1, 2017 at 6:58 am

  #338909

  have a form that had 5 rows with 3 dropdowns per row. The first dropdown in each row is list of categories (None (0), Scoring (1), Rebounds (2), etc...), second is a > (1) or = (0) option and then the third is 0 to 100 options.  If no category selected it just outputs all the data.  If row 1 category is only one selected it would only first by that category.  If row 2 category is also selected then the condition of both would be met, if row 3 category is selected then all three conditions must be met.  Looking to create a dynamic way.

  I have created a sample sql query to test below but can't get it to return results based on the categories that are selected.

  Thanks for any info you can provide to help me do this the proper way.

  
DECLARE @Players as TABLE (p_id int IDENTITY(1,1) PRIMARY KEY, p_username varchar(50))
INSERT INTO @Players (p_username) VALUES ('Jeff 1')
INSERT INTO @Players (p_username) VALUES ('John 2')
INSERT INTO @Players (p_username) VALUES ('Larry 3')
INSERT INTO @Players (p_username) VALUES ('Bill 4')

  DECLARE @category as TABLE (cat_id int IDENTITY(1,1), cat_name varchar(50))
INSERT INTO @category (cat_name) VALUES ('scoring 1')
INSERT INTO @category (cat_name) VALUES ('rebounds 2')
INSERT INTO @category (cat_name) VALUES ('steals 3')
INSERT INTO @category (cat_name) VALUES ('assists 4')
INSERT INTO @category (cat_name) VALUES ('free-throw attempts 5')
INSERT INTO @category (cat_name) VALUES ('free-throws made 6')

  DECLARE @points as TABLE (pt_id int IDENTITY(1,1), p_id int, pt_total int, cat_id int)
--Jeff scoring
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 10, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 15, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 12, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 17, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 10, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 11, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 9, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 8, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 14, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 13, 1)
--Jeff rebounding
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 2, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 3, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 1, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 1, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 1, 2)
--Jeff steals
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 1, 3)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 2, 3)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (1, 1, 3)

  --John scoring
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 20, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 15, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 22, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 17, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 10, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 11, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 19, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 18, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 14, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 13, 1)
--John rebounding
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 1, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 1, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 1, 2)
--John steals
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 3, 3)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 2, 3)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (2, 2, 3)

  --Larry scoring
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 10, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 10, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 14, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 13, 1)
--Larry rebounding
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 1, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 1, 2)
--Larry steals
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 5, 3)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 2, 3)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (3, 4, 3)

  --Bill scoring
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 20, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 20, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 24, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 23, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 12, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 11, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 13, 1)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 14, 1)
--Bill rebounding
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 12, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 10, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 3, 2)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 4, 2)
--Bill steals
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 1, 3)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 1, 3)
INSERT INTO @points (p_id, pt_total, cat_id) VALUES (4, 1, 3)

  /*SELECT * FROM @category
SELECT * FROM @Players
*/

  DECLARE @Cat1 int
DECLARE @Cat1Sign int
DECLARE @Cat1Value int
DECLARE @Cat2 int
DECLARE @Cat2Sign int
DECLARE @Cat2Value int
DECLARE @Cat3 int
DECLARE @Cat3Sign int
DECLARE @Cat3Value int

  --@Cat#Sign = 0 is equal sign, @Cat#Sign = 1 is greater then sign

  SET @Cat1 = 1
SET @Cat1Sign = 1
SET @Cat1Value = 100
SET @Cat2 = 2
SET @Cat2Sign = 1
SET @Cat2Value = 7
SET @Cat3 = 0
SET @Cat3Sign = 0
SET @Cat3Value = 0

  DECLARE @tmpPointsSumbyCat as TABLE (ID int IDENTITY(1,1), p_id int, pt_total int, cat_id int)
INSERT INTO @tmpPointsSumbyCat
SELECT p_id, sum(pt_total) as pt_total, cat_id
FROM @points
GROUP BY p_id, cat_id

  -- want to see everyone that has over 100 points (cat_id = 1) AND over 7 rebounds (cat_id = 2)
-- I could also have another filter with cat3 which all would have to meet criteria
-- not sure why this is not returning results

  SELECT *
FROM @tmpPointsSumbyCat
WHERE (@Cat1 = 0 OR ((cat_id = @Cat1 AND @Cat1Sign = 1 AND pt_total > @Cat1Value) OR (cat_id = @Cat1 AND @Cat1Sign = 0 AND pt_total = @Cat1Value)))
AND (@Cat2 = 0 OR ((cat_id = @Cat2 AND @Cat2Sign = 1 AND pt_total > @Cat2Value) OR (cat_id = @Cat2 AND @Cat2Sign = 0 AND pt_total = @Cat2Value)))

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  September 1, 2017 at 9:08 am

  #1957850

  Well, the reason it is not returning results is your where clause.
  since @Cat1 does not equal 0 AND @Cat2 doesn't equal 0, one of your OR conditions needs to be met in each line.  Since cat_id cannot be both @Cat1 AND @Cat2 (unless @Cat1 = @Cat2 which it does not), your where clause is filtering out all possible results.

  As a thought (I did not test this), you should get results if you comment out the last line (the one that starts out with "AND (@Cat2 = 0 ..."), but it will only give results if @Cat1 is 0 and ignore the @Cat2 stuff.
  I think for the results you want though, you will want to change the last line to start with "OR" instead of "AND".  The problem with that is that in the event that @Cat2 is 0, it will return everything in the table.  I think you will want to remove the "AND (@Cat2 = 0 OR" and replace it with "OR (".

  So, I'd change the last where clause to:
  WHERE (@Cat1 = 0 OR ((cat_id = @Cat1 AND @Cat1Sign = 1 AND pt_total > @Cat1Value) OR (cat_id = @Cat1 AND @Cat1Sign = 0 AND pt_total = @Cat1Value)))
  OR (((cat_id = @Cat2 AND @Cat2Sign = 1 AND pt_total > @Cat2Value) OR (cat_id = @Cat2 AND @Cat2Sign = 0 AND pt_total = @Cat2Value)))
  and you should get expected results, no?

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• bonggaspar

  Valued Member

  Points: 74

  More actions

  September 3, 2017 at 4:03 pm

  #1957961

  You may want to PIVOT the points (pt_total) for each category (cat_id)

  SELECT p_id, SUM([1]) scores, SUM([2]) rebounds, SUM([3]) steals
FROM @points
PIVOT (SUM(pt_total)
   FOR cat_id IN ([1],[2], [3])
   ) p
GROUP BY p_id
HAVING SUM([1]) > 100
 AND SUM([2]) > 7

  Result
  p_id    scores    rebounds    steals
1       119       8           4
4       137       29          3

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  September 3, 2017 at 8:15 pm

  #1957968

  See what you think of the following...

  SET NOCOUNT ON;

  IF OBJECT_ID('tempdb..#Players', 'U') IS NOT NULL
DROP TABLE #Players;
GO
CREATE TABLE #Players (
    p_id int IDENTITY(1,1) PRIMARY KEY,
    p_username varchar(50)
    );
GO
INSERT INTO #Players (p_username) VALUES
    ('Jeff 1'), ('John 2'), ('Larry 3'), ('Bill 4');
GO

  IF OBJECT_ID('tempdb..#category', 'U') IS NOT NULL
DROP TABLE #category;
GO
CREATE TABLE #category (
    cat_id int IDENTITY(1,1),
    cat_name varchar(50)
    );
GO
INSERT INTO #category (cat_name) VALUES
    ('scoring 1'), ('rebounds 2'), ('steals 3'), ('assists 4'),
    ('free-throw attempts 5'), ('free-throws made 6');
GO

  IF OBJECT_ID('tempdb..#points', 'U') IS NOT NULL
DROP TABLE #points;
GO
CREATE TABLE #points (
    pt_id int IDENTITY(1,1),
    p_id int,
    pt_total int,
    cat_id INT
    );
GO
--Jeff scoring
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (1, 10, 1), (1, 15, 1), (1, 12, 1), (1, 17, 1), (1, 10, 1),
    (1, 11, 1), (1, 9, 1), (1, 8, 1), (1, 14, 1), (1, 13, 1);

  --Jeff rebounding
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (1, 2, 2), (1, 3, 2), (1, 1, 2), (1, 1, 2), (1, 1, 2);

  --Jeff steals
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (1, 1, 3), (1, 2, 3), (1, 1, 3);

  --John scoring
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (2, 20, 1), (2, 15, 1), (2, 22, 1), (2, 17, 1), (2, 10, 1),
    (2, 11, 1), (2, 19, 1), (2, 18, 1), (2, 14, 1), (2, 13, 1);

  --John rebounding
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (2, 1, 2), (2, 1, 2), (2, 1, 2);

  --John steals
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (2, 3, 3), (2, 2, 3), (2, 2, 3);

  --Larry scoring
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (3, 10, 1), (3, 10, 1), (3, 14, 1), (3, 13, 1);

  --Larry rebounding
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (3, 1, 2), (3, 1, 2);

  --Larry steals
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (3, 5, 3), (3, 2, 3), (3, 4, 3);

  --Bill scoring
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (4, 20, 1), (4, 20, 1), (4, 24, 1), (4, 23, 1),
    (4, 12, 1), (4, 11, 1), (4, 13, 1), (4, 14, 1);

  --Bill rebounding
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (4, 12, 2), (4, 10, 2), (4, 3, 2), (4, 4, 2);

  --Bill steals
INSERT INTO #points (p_id, pt_total, cat_id) VALUES
    (4, 1, 3), (4, 1, 3), (4, 1, 3);

  --================================================================
--================================================================

  DECLARE
    @Cat1 INT, @Cat1Sign INT, @Cat1Value INT,
    @Cat2 INT, @Cat2Sign INT, @Cat2Value INT,
    @Cat3 INT, @Cat3Sign INT, @Cat3Value INT;

  --@Cat#Sign = 0 is equal sign, @Cat#Sign = 1 is greater then sign
SELECT
    @Cat1 = 1, @Cat1Sign = 1, @Cat1Value = 100,
    @Cat2 = 2, @Cat2Sign = 1, @Cat2Value = 7,
    @Cat3 = 0, @Cat3Sign = 0, @Cat3Value = 0;

  DECLARE
    @Category NVARCHAR(4000),
    @sql NVARCHAR(4000),
    @DeBug BIT = 0;        -- 0 = execute @sql ... 1 = print @sql

  SELECT
    @Category = CONCAT(@Category, N',
    [', c.cat_name, '] = SUM(CASE WHEN pt.cat_id = ', c.cat_id, ' THEN pt.pt_total END)')
FROM
    #category c
ORDER BY
    c.cat_id;

  --PRINT(@Category);
SET @sql = CONCAT(N'
SELECT
    p.p_id,
    p.p_username',
    @Category, N',
    [point total] = SUM(pt.pt_total)
FROM
    #Players p
    JOIN #points pt
        ON p.p_id = pt.p_id
GROUP BY
    p.p_id,
    p.p_username',
CASE WHEN NULLIF(@Cat1, 0) IS NULL AND NULLIF(@Cat2, 0) IS NULL AND NULLIF(@Cat3, 0) IS NULL THEN N'' ELSE CONCAT(N'
HAVING 1 = 1',
    CASE WHEN NULLIF(@Cat1, 0) IS NULL THEN N'' ELSE CONCAT(N'
    AND SUM(CASE WHEN pt.cat_id = @_Cat1 THEN pt.pt_total END) ', CASE WHEN @Cat1Sign = 0 THEN N'=' ELSE N'>' END, N' @_Cat1Value')
    END,
    CASE WHEN NULLIF(@Cat2, 0) IS NULL THEN N'' ELSE CONCAT(N'
    AND SUM(CASE WHEN pt.cat_id = @_Cat2 THEN pt.pt_total END) ', CASE WHEN @Cat1Sign = 0 THEN N'=' ELSE N'>' END, N' @_Cat2Value')
    END,
    CASE WHEN NULLIF(@Cat3, 0) IS NULL THEN N'' ELSE CONCAT(N'
    AND SUM(CASE WHEN pt.cat_id = @_Cat3 THEN pt.pt_total END) ', CASE WHEN @Cat1Sign = 0 THEN N'=' ELSE N'>' END, N' @_Cat3Value')
    END)
END, ';')
    

  IF @DeBug = 1
BEGIN
    PRINT(@sql);
END;
ELSE
BEGIN
    EXEC sys.sp_executesql @sql, N'@_Cat1 INT, @_Cat1Value INT, @_Cat2 INT, @_Cat2Value INT, @_Cat3 INT, @_Cat3Value INT', @Cat1, @Cat1Value, @Cat2, @Cat2Value, @Cat3, @Cat3Value;
END;

  When @DeBug  = 1, this is the printed @sql...
  SELECT
    p.p_id,
    p.p_username,
    [scoring 1] = SUM(CASE WHEN pt.cat_id = 1 THEN pt.pt_total END),
    [rebounds 2] = SUM(CASE WHEN pt.cat_id = 2 THEN pt.pt_total END),
    [steals 3] = SUM(CASE WHEN pt.cat_id = 3 THEN pt.pt_total END),
    [assists 4] = SUM(CASE WHEN pt.cat_id = 4 THEN pt.pt_total END),
    [free-throw attempts 5] = SUM(CASE WHEN pt.cat_id = 5 THEN pt.pt_total END),
    [free-throws made 6] = SUM(CASE WHEN pt.cat_id = 6 THEN pt.pt_total END),
    [point total] = SUM(pt.pt_total)
FROM
    #Players p
    JOIN #points pt
        ON p.p_id = pt.p_id
GROUP BY
    p.p_id,
    p.p_username
HAVING 1 = 1
    AND SUM(CASE WHEN pt.cat_id = @_Cat1 THEN pt.pt_total END) > @_Cat1Value
    AND SUM(CASE WHEN pt.cat_id = @_Cat2 THEN pt.pt_total END) > @_Cat2Value;

  When @DeBug  = 0, this is the executed @sql...