T-SQL Stuff I've Learned

index

T-SQL Stuff I've Learned

NULL or a value: If you want to be able to pass in a value to a proc for a filter, or null to get all records (not filtered by parameter):

WHERE
	(@intProjectID IS NULL OR I.ProjectId = @intProjectID)
	AND
	(@intWorkflowStepID IS NULL OR I.WorkflowStepId = @intWorkflowStepID)
	AND
	(@dtFilterDate IS NULL OR i.CreatedDateTime >= @dtFilterDate)
	AND
	(@chvSubordinates IS NULL  OR ',' + @chvSubordinates + ',' LIKE '%,' + CAST(Custom_234 AS VARCHAR(MAX)) + ',%')

DECLARE @chvStateAbbreviation varchar(255);
SET @chvStateAbbreviation = 'AZ';

SELECT 
	VS.Name AS State
	, VC.Name AS County
	, COUNT(B.Name) AS BrokerCount
FROM Businesses AS B
	INNER JOIN BusinessLocations AS BL ON BL.BusinessID = B.BusinessID AND BL.IsDeleted = 0
	INNER JOIN ValidCounties AS VC ON BL.PhysicalCountyID = VC.CountyID
	INNER JOIN dbo.ValidStates AS VS ON VC.StateID = VS.StateID 
WHERE (B.BusinessSubTypeID = 5) 
	AND (B.IsDeleted = 0)
	AND (
			(@chvStateAbbreviation IS NULL AND BL.PhysicalStateID IN ('IN', 'OH', 'MI', 'PA'))
			OR 
			(BL.PhysicalStateID = @chvStateAbbreviation) 
		)
		
GROUP BY VS.Name, VC.Name
ORDER BY VS.Name, County

To add "All" to a dropdownlist:

SELECT DISTINCT Businesses.BusinessID, Businesses.Name 
FROM Businesses 
INNER JOIN EquatorAssets ON Businesses.BusinessID = EquatorAssets.ClientID
UNION
SELECT NULL, 'All'
ORDER BY 1, 2

Switching T/F to Y/N

, CASE WHEN B.WomanOwned = 1 THEN 'Yes' ELSE 'No' END AS WomanOwned
, CASE WHEN B.MinorityOwned = 1 THEN 'Yes' ELSE 'No' END AS MinorityOwned
, CASE WHEN B.MinorityOwned = 1  THEN VMC.Name ELSE 'N/A' END AS MinorityClassification
, CASE WHEN B.WomanOwned = 1 OR  B.MinorityOwned = 1 THEN 'Yes' ELSE 'No' END AS MinorityOrWomanOwned

DECLARE @chvStateAbbreviation varchar(255);
SET @chvStateAbbreviation = 'AZ';

SELECT 
	VS.Name AS State
	, VC.Name AS County
	, COUNT(B.Name) AS BrokerCount
FROM Businesses AS B
	INNER JOIN BusinessLocations AS BL ON BL.BusinessID = B.BusinessID AND BL.IsDeleted = 0
	INNER JOIN ValidCounties AS VC ON BL.PhysicalCountyID = VC.CountyID
	INNER JOIN dbo.ValidStates AS VS ON VC.StateID = VS.StateID 
WHERE (B.BusinessSubTypeID = 5) 
	AND (B.IsDeleted = 0)
	AND (
			(@chvStateAbbreviation IS NULL AND BL.PhysicalStateID IN ('IN', 'OH', 'MI', 'PA'))
			OR 
			(BL.PhysicalStateID = @chvStateAbbreviation) 
		)
		
GROUP BY VS.Name, VC.Name
ORDER BY VS.Name, County

Null or a value:

WHERE
	EA.IsDeleted=0
	AND ((@chrStateID IS NULL) OR (EA.PropertyState = @chrStateID))
	AND ((@intClientID IS NULL) OR (EA.ClientID = @intClientID))
	AND ((@intVendorID IS NULL) OR (B.BusinessID = @intVendorID))

To add "All" to a dropdownlist:

SELECT DISTINCT Businesses.BusinessID, Businesses.Name 
FROM Businesses 
INNER JOIN EquatorAssets ON Businesses.BusinessID = EquatorAssets.ClientID
UNION
SELECT NULL, 'All'
ORDER BY 1, 2