Tutorial :SQL that can pulls records by a possible three values or none at all



Question:

Say you have WIDGETS table:

  • WidgetID int
  • ColorID int (Lookup values: Red, Blue, Green, Yellow, Black, Brown)
  • SizeID int (Lookup values: Small, Med, Big, Large)
  • Weight int (Lookup values: UltraLight, Light, Normal, Heavy, UltraHeavy

Ok that's the general idea of the table. I don't need the lookup names, what I do have is the lookupvalue ID's. So from this table, I would to be able pull lists back that meet say the following crieria:

  1. Show me all widgets who are Red, or Blue, or Black
  2. Show me all widgets who are Red or Blue, and Small or Med
  3. Show me all widgets who are Heavy only
  4. Show me all widgets who are heavy and Yellow
  5. Show me all widgets who are heavy and yellow and small or big
  6. Show me all widgets who are Large only
  7. Show me all widgets who are Green only

Get the idea? I've been trying to work on a Stored Proc that allowed me to send in some type of parameters. Even tried dynamic sql but getting weird errors. can't remember now.

My Attempt #3, sort of works if I could get it working in a Proc and be able to condition out which Join's I want

Examples of what I've tried:

Note: In my examples: (CourseID is WidgetID) and (StateID is ColorID) and (CreditTypeID is SizeID) and (SubjectID is WeightID)

Attempt Method 1

ALTER PROCEDURE [dbo].[CourseListFullInfoByStateCreditSubject]    @StateIDs VARCHAR(200) = '',    @CreditTypeIDs VARCHAR(200) = '',    @SubjectTypeIDs VARCHAR(200) = ''  AS    BEGIN      DECLARE @SQL AS NVARCHAR(MAX)    SET @SQL = 'SELECT DISTINCT                       C.CourseID,                       LU.FirstName,                       LU.LastName,                       (SELECT COUNT(ReviewID) FROM Review AS R WHERE R.CourseID = C.CourseID) AS ReviewCount                  FROM [Course] AS C WITH(NOLOCK)                  JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID '    If @StateIDs IS NOT NULL AND @StateIDs <> '''' AND @StateIDs <> '0'  BEGIN    SET @SQL = @SQL + ' JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CS.StateID AS VARCHAR) + '','', '','' + @StateIDs + '','') > 0 '  END    If @CreditTypeIDs IS NOT NULL AND @CreditTypeIDs <> '' AND @CreditTypeIDs <> '0'  BEGIN    SET @SQL = @SQL + 'JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CC.CreditTypeID AS VARCHAR) + '','', '','' + @CreditTypeIDs + '','') > 0 '  END    If @SubjectTypeIDs IS NOT NULL AND @SubjectTypeIDs <> '' AND @SubjectTypeIDs <> '0'  BEGIN    SET @SQL = @SQL + 'JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CSu.SubjectID AS VARCHAR) + '','', '','' + @SubjectTypeIDs + '','') > 0 '  END    EXEC sp_executesql @SQL  

With Attempt 1 I try and send in the ID's:

[CourseListFullInfoByStateCreditSubject] ''1,2,4'', ''0'', ''0''  

...but I get an error, "Incorrect syntax near '1'."

Attempt Method 2, gives same error

DECLARE @SQL AS NVARCHAR(MAX)  SET @SQL = 'SELECT DISTINCT                     C.CourseID,                     LU.FirstName,                     LU.LastName,                     (SELECT COUNT(ReviewID)                        FROM Review AS R                       WHERE R.CourseID = C.CourseID) AS ReviewCount               FROM [Course] AS C WITH(NOLOCK)               JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID '    If @StateIDs IS NOT NULL AND @StateIDs <> '''' AND @StateIDs <> '0'  BEGIN    SET @SQL = @SQL + ' JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CS.StateID AS VARCHAR) + '','', '','' + @StateIDs + '','') > 0 '  END    If @CreditTypeIDs IS NOT NULL AND @CreditTypeIDs <> '' AND @CreditTypeIDs <> '0'  BEGIN    SET @SQL = @SQL + 'JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CC.CreditTypeID AS VARCHAR) + '','', '','' + @CreditTypeIDs + '','') > 0'  END    If @SubjectTypeIDs IS NOT NULL AND @SubjectTypeIDs <> '' AND @SubjectTypeIDs <> '0'  BEGIN    SET @SQL = @SQL + 'JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CSu.SubjectID AS VARCHAR) + '','', '','' + @SubjectTypeIDs + '','') > 0'  END  

Attempt Method 3 - Sort of Works This method only works if I send it ID's for each feld, I can't leave one blank to get "all" and I can't get it working in a stored proc, which is what I tried to do in Attempt #2

DECLARE @StateIDs VARCHAR(200) = ''  DECLARE @CreditTypeIDs VARCHAR(200) = ''  DECLARE @SubjectTypeIDs VARCHAR(200) = ''  SET @StateIDs = '1,3,2,'  SET @CreditTypeIDs = '1,3'  SET @SubjectTypeIDs = '1,2,3,4'     SELECT DISTINCT         C.CourseID,         LU.FirstName,         LU.LastName,         (SELECT COUNT(ReviewID) FROM Review AS R WHERE R.CourseID = C.CourseID) AS ReviewCount    FROM [Course] AS C WITH(NOLOCK)    JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID     JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CS.StateID AS VARCHAR) + ',', ',' + @StateIDs + ',') > 0     JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CC.CreditTypeID AS VARCHAR) + ',', ',' + @CreditTypeIDs + ',') > 0    JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CSu.SubjectID AS VARCHAR) + ',', ',' + @SubjectTypeIDs + ',') > 0  


Solution:1

How about something like this:

Make a stored procedure with a comma-separated list of conditions, should look like

DECLARE @colorConditions VARCHAR(100)  SET @colorConditions = ' Red, Blue, Green, Yellow, Black, Brown, '    DECLARE @sizeConditions VARCHAR(100)  SET @sizeConditions = ' Small, Med, Big, Large, '    DECLARE @weightConditions VARCHAR(100)  SET @weightConditions = ' UltraLight, Light, Normal, Heavy, UltraHeavy, '    DECLARE @SQL VARCHAR(1000)  SET @SQL =        'SELECT *         FROM Widgets W        INNER JOIN Colors C           ON W.ColorID = C.ColorID AND ''' + @colorConditions + ''' LIKE ''% '' + C.Color + '', %''         INNER JOIN Sizes S           ON W.SizeID = S.SizeID AND ''' + @sizeConditions + ''' LIKE ''% '' + S.Size + '', %''        INNER JOIN Weight WT           ON W.WeightID = W.WeightID AND ''' + @weightConditions + ''' LIKE '' %'' + WT.Weight + '', %'''    EXEC (@SQL)  

Nothing special here. All it does is match each trait (Color, Size, Weight) against a comma-separated list of qualifying conditions. If you want to filter on Blue items, you should pass the string ' Blue, '. The flanking spaces and commas should be present because it's easier to have them there then to fight with SQL. The condition string is then compared to each color value like so:

Condition string:     ' Blue, Green, '    Colors:     Red  Blue  Green    ...AND ' Blue, Green, ' LIKE '% Red, %' -- fails  ...AND ' Blue, Green, ' LIKE '% Blue, %' -- succeeds  ...AND ' Blue, Green, ' LIKE '% Green, %' -- succeeds  

This must be executed dynamically, unless you split the conditions out somehow to a temporary table and use the IN operator.


Solution:2

Try a Left Join from Widgets to all the lookup tables and filter out the appropriate rows using a Where.


Solution:3

Try this... to call it, for each parameter, pass in null to ignore, or bitmask combination of values you want included. according to following chart...

-- ----Color ----  Red    =  1  Blue   =  2  Green  =  4  Yellow =  8  Black  = 16  Brown  = 32  Red Or Green = 5    -- ---- Size ---  Small  = 1  Medium = 2  Big    = 4  Large  = 8    -- ----Weight ----  UltraLight =  1  Light      =  2  Normal     =  4  Heavy      =  8  UltraHeavy = 16  

Also, change the lookup integer values in the database (both PKs in lookup tables, and FKs in the Widgets table), so that they are all the appropriate powers of two (1,2,4,,8,16... )

-- -------------------------  Create procedure GetWidgets  @Color TinyInt = Null,   @Size TinyInt = Null  @Weight TinyInt = Null  As  Set NoCount On    -- -------------------------------       Select * From Widgets     Where Color & IsNull(@Color, Color)> 0        And Size & IsNull (@Size,Size) > 0         And Weight & IsNull (@Weight, Weight ) > 0        Return 0  -- -------------------------  


Solution:4

The prerequisite for this solution is a split table-valued function. There are many examples on the net, but I do recommend using a variant that uses a Tally or "Numbers" table as it is the fastest. Once you have that, the solution is somewhat trivial:

Declare @StateIdList nvarchar(max)  Declare @CreditTypeIdList nvarchar(max)  Declare @SubjectTypeIds nvarchar(max)    Select F1....Fn  From Course As C  Where   (          @StateIdList Is Null           Or  Exists(                      Select 1                      From CourseToState As CS1                          Join dbo.Split(@StateIdList) As S1                              On S1.Id = CS1.Id                      Where CS1.CourseId = C.CourseId                      )          )      And (          @CreditTypeIdList Is Null           Or  Exists(                      Select 1                      From CourseToCreditType As CT1                          Join dbo.Split(@CreditTypeIdList) As S1                              On S1.Id = CT1.Id                      Where CT1.CourseId = C.CourseId                      )          )      And (          @SubjectTypeIds Is Null           Or  Exists(                      Select 1                      From CourseToSubject  As CSu1                          Join dbo.Split(@CreditTypeIdList) As S1                              On S1.Id = CSu1.Id                      Where CSu1.CourseId = C.CourseId                      )          )     

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »