September 30, 2015 at 10:39 am
Hi,
I have a scenario where I need to develop a stored proc to identify invalid input provided.
Following is a sample scenario
Create table product (ProductId varchar(10),SizeId int,ProductColor varchar(10));
insert into Product
select 'Prod1',10,'Black' union ALL
select 'Prod1',10,'BLue' union ALL
select 'Prod2',20,'Green' union ALL
select 'Prod2',10,'Black' ;
CREATE table sizes (SizeId int, Size varchar(20));
INSERT INTO sizes
select 10,'S' union ALL
select 10,'M' union ALL
select 10,'L' union ALL
select 20,'XL' union ALL
select 20,'2XL' union ALL
select 20,'3XL' ;
--===================================================
-- Function Creation
--===================================================
create FUNCTION [dbo].[fnSplit]
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50) collate database_default )
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item VARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
In following TSql Code , Color and Size are optional. Both are provided as comma separated input.
I have provided "bbc" as wrong color and "MM" as wrong size. I want to identify if color is invalid or size (MM is in valid for Black and Blue) and to set flag accordingly.
I had tried out join but it is not serving needs. Can you please suggest something ?
---===========================================
-- Sql
--============================================
DECLARE
@ProdId varchar(10),
@color varchar(max) = Null,
@size varchar(max) = Null
BEGIN
set @ProdId='Prod1';
SET @color='Black,Blue,bbc' ; -- bbc is invalid
set @size='L,S,M,MM'; -- MM is invalid
SET NOCOUNT ON
DECLARE @cteColors TABLE (ProductId varchar(10),CatalogColor VARCHAR(14) PRIMARY KEY)
INSERT INTO @cteColors
SELECT @ProdId,
[CatalogColor] = Item
FROM dbo.fnSplit(@color, ',');
DECLARE @cteSizes TABLE (ProductId varchar(10),SizeDesc VARCHAR(10) PRIMARY KEY)
INSERT INTO @cteSizes
SELECT @ProdId,
[SizeDesc] = Item
FROM dbo.fnSplit(@size, ',');
--========================================================================
--== Inserting valid combinition to temp table
--========================================================================
SELECT distinct prod.ProductId,
prod.ProductColor,
Sizes.Size,
'1' as status
INTO #tmp
FROM product prod
INNER JOIN sizes sizes
ON prod.SizeID = sizes.SizeId
WHERE 1=1
AND (EXISTS (SELECT sizedesc FROM @cteSizes WHERE sizedesc = sizes.Size)
OR @size IS NULL)
AND (EXISTS (SELECT CatalogColor FROM @cteColors WHERE CatalogColor = prod.ProductColor)
OR @color IS NULL)
AND prod.ProductId = @ProdId
GROUP BY prod.ProductId,sizes.Size,prod.ProductColor
ORDER BY 1,2,4;
-- ==================================
-- Trying to find invalid combinition
--====================================
WITH mycte
as (
select color.ProductId,color.catalogcolor,sz.SizeDesc
from @cteColors color
cross join @cteSizes sz
)
SELECT my.ProductId,
my.catalogColor,
my.SizeDesc
,tmp.*
--,Case when tmp.ProductColor is null then '101'
--END as Status
FROM mycte my
LEFT JOIN #tmp tmp
ON my.ProductId = tmp.ProductId
AND my.CatalogColor = tmp.ProductColor
AND my.sizeDesc = tmp.Size;
--
drop table #tmp;
END
September 30, 2015 at 12:34 pm
Just to be clear I understand the requirements, you just want all possible combinations of the sizes and colors in the comma delimited lists, along with a flag that indicates whether that combination is valid?
If so, there are a few ways of doing this, and I quickly wrote up a few of them just to illustrate how they might work. The performance characteristics of each are different, so you'll want to test them to see how they perform with your actual data.
I'd also look at using one of the ultra-efficient splitters out there, like Jeff Moden's DelimitedSplit8k, and avoiding using ordinal column positions for ordering.
At any rate, this should give you a good idea of some ways this can be done.
DECLARE
@ProdId varchar(10),
@color varchar(max) = Null,
@size varchar(max) = Null
SET @ProdId='Prod1';
SET @color='Black,Blue,bbc' ; -- bbc is invalid
SET @size='L,S,M,MM'; -- MM is invalid
--Option using EXCEPT, INTERSECT, and UNION ALL
--I've included this mostly for completeness (and fun),
--as it's both harder to follow and slower
--than the others.
SELECT InputSizes.Item AS Size, InputColors.Item AS Color, 0 AS IsValid
FROM fnsplit(@size,',') InputSizes
CROSS APPLY fnsplit(@color,',') InputColors
EXCEPT
SELECT Size, ProductColor, 0 AS IsValid
FROM Sizes
CROSS JOIN Product
UNION ALL
SELECT InputSizes.Item AS Size, InputColors.Item AS Color, 1 AS IsValid
FROM fnsplit(@size,',') InputSizes
CROSS JOIN fnsplit(@color,',') InputColors
INTERSECT
SELECT Size, ProductColor, 1 AS IsValid
FROM Sizes
CROSS JOIN Product
PRINT 'Now NOT EXISTS'
--Option using NOT EXISTS
SELECT InputSizes.Item AS Size, InputColors.Item AS Color,
CASE
WHEN
NOT EXISTS (SELECT NULL FROM Sizes S WHERE s.size=InputSizes.Item)
OR
NOT EXISTS (SELECT NULL FROM Product P WHERE p.ProductColor=InputColors.Item) THEN 0 ELSE 1 END AS IsValid
FROM fnsplit(@size,',') InputSizes
CROSS JOIN fnsplit(@color,',') InputColors
PRINT 'Now NOT IN'
--Option using NOT IN.
SELECT InputSizes.Item AS Size, InputColors.Item AS Color,
CASE
WHEN
InputSizes.Item NOT IN (SELECT size FROM sizes)
OR
InputColors.Item NOT IN (SELECT ProductColor FROM Product) THEN 0 ELSE 1 END AS IsValid
FROM fnsplit(@size,',') InputSizes
CROSS JOIN fnsplit(@color,',') InputColors
PRINT 'Now LEFT JOIN'
--Option using LEFT JOIN
SELECT InputSizes.Item AS Size, InputColors.Item AS Color, CASE WHEN S.size+P.ProductColor IS NULL THEN 0 ELSE 1 END as IsValid
FROM fnsplit(@size,',') InputSizes
CROSS JOIN fnsplit(@color,',') InputColors
LEFT JOIN Sizes S ON S.size=InputSizes.Item
LEFT JOIN Product P ON P.ProductColor=InputColors.Item
Cheers!
September 30, 2015 at 12:49 pm
What is the desired output from the values you are using? Also, as stated previously ordering by ordinal position is just bad. Don't do it. And throw that splitter in the trash can. Using a while loop for a splitter is horribly inefficient and is the absolute slowest possible method of all splitters. In a perfect world you would pass in a table valued parameter instead of a delimited list and the problem becomes infinitely simpler.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2015 at 1:00 pm
Thank you Jacob , I will test them .
September 30, 2015 at 1:04 pm
Just a shot in the dark here. From the title it sounds like you want to find invalid combinations among the values being passed in. There are a number of ways to do this and even a number of ways of interpreting the question. Here is one way to accomplish what I think is all invalid combinations from the values passed in. This is using the DelimitedSplit8K table valued function. You can find it by following the link in my signature about splitting strings.
DECLARE @ProdId varchar(10) = 'Prod1',
@color varchar(max) = 'Black,Blue,bbc',
@size varchar(max) = 'L,S,M,MM';
with AllSizesAndColors as --this gets all combinations of color and size
(
select p.ProductColor, s.Size
from Product p
cross join sizes s
)
, RequestedSizesAndColors as --this gets all combinations from the values in the parameters
(
select c.Item as ProductColor
, s.Item as Size
from dbo.DelimitedSplit8K(@color, ',') c
cross join dbo.DelimitedSplit8K(@size, ',') s
)
select r.ProductColor
, r.size
from RequestedSizesAndColors r
left join AllSizesAndColors a on a.ProductColor = r.ProductColor
and a.Size = r.Size
where a.ProductColor IS NULL
order by r.ProductColor
, r.Size
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2015 at 1:09 pm
sean, I want to get output like following
-- If color invalid status should be 100
-- if color is valid but size is invalid status should be 101
-- if valid status should be 102
Product Color Size Status
Prod1 Black S 102
Prod1 Black M 102
Prod1 Black L 102
Prod1 Black MM 101
Prod1 Blue S 102
Prod1 Blue M 102
Prod1 Blue L 102
Prod1 Blue MM 101
Prod1 bbc S 100
Prod1 bbc M 100
Prod1 bbc L 100
Prod1 bbc MM 100
September 30, 2015 at 1:20 pm
thbaig (9/30/2015)
sean, I want to get output like following-- If color invalid status should be 100
-- if color is valid but size is invalid status should be 101
-- if valid status should be 102
Product Color Size Status
Prod1 Black S 102
Prod1 Black M 102
Prod1 Black L 102
Prod1 Black MM 101
Prod1 Blue S 102
Prod1 Blue M 102
Prod1 Blue L 102
Prod1 Blue MM 101
Prod1 bbc S 100
Prod1 bbc M 100
Prod1 bbc L 100
Prod1 bbc MM 100
Are there any other new rules? This seems very strange to me but certainly feasible.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2015 at 1:27 pm
This produces the expected output based on the sample data provided.
DECLARE @ProdId varchar(10) = 'Prod1',
@color varchar(max) = 'Black,Blue,bbc',
@size varchar(max) = 'L,S,M,MM';
with AllSizesAndColors as --this gets all combinations of color and size
(
select p.ProductColor, s.Size
from Product p
cross join sizes s
)
, RequestedSizesAndColors as --this gets all combinations from the values in the parameters
(
select c.Item as ProductColor
, s.Item as Size
from dbo.DelimitedSplit8K(@color, ',') c
cross join dbo.DelimitedSplit8K(@size, ',') s
)
select @ProdID as Product
, r.ProductColor
, r.size
, case when a.ProductColor IS NOT NULL then 102
when p.ProductColor IS NOT NULL then 101
else 100
end as Status
from RequestedSizesAndColors r
left join Product p on p.ProductColor = r.ProductColor
left join AllSizesAndColors a on a.ProductColor = r.ProductColor
and a.Size = r.Size
group by r.ProductColor
, r.size
, case when a.ProductColor IS NOT NULL then 102
when p.ProductColor IS NOT NULL then 101
else 100 end
order by r.ProductColor
, r.Size
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2015 at 7:30 am
Thank you Sean , it worked.
How can we make color and Size optional ? if either one or both provided it should return on input . If both not provide it should return all available combination.
October 1, 2015 at 7:50 am
thbaig (10/1/2015)
Thank you Sean , it worked.How can we make color and Size optional ? if either one or both provided it should return on input . If both not provide it should return all available combination.
You will have to explain these new rules a little better. I don't quite understand what you mean. Also, if there are any other little nuances this would be the time to share them instead of after I help you with a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2015 at 8:18 am
This is the only thing left.
Case-1
DECLARE @ProdId varchar(10) = 'Prod1',
@color varchar(max) = NULL, -- Will make it optional and logic will work on all available color for Product.
@size varchar(max) = 'L,S,M,MM'; -- Will consider only provided sizes
Case-2
DECLARE @ProdId varchar(10) = 'Prod1',
@color varchar(max) = 'Black,Blue,bbc' -- Will consider only provided colors
@size varchar(max) = NULL, -- Will make it optional and logic will work on all available sizes for product
Case-3
DECLARE @ProdId varchar(10) = 'Prod1',
@color varchar(max) = NULL, -- Will make it optional and logic will work on all available color for Product.
@size varchar(max) = NULL, -- Will make it optional and logic will work on all available sizes for product
October 1, 2015 at 9:33 am
thbaig (10/1/2015)
This is the only thing left.Case-1
DECLARE @ProdId varchar(10) = 'Prod1',
@color varchar(max) = NULL, -- Will make it optional and logic will work on all available color for Product.
@size varchar(max) = 'L,S,M,MM'; -- Will consider only provided sizes
Case-2
DECLARE @ProdId varchar(10) = 'Prod1',
@color varchar(max) = 'Black,Blue,bbc' -- Will consider only provided colors
@size varchar(max) = NULL, -- Will make it optional and logic will work on all available sizes for product
Case-3
DECLARE @ProdId varchar(10) = 'Prod1',
@color varchar(max) = NULL, -- Will make it optional and logic will work on all available color for Product.
@size varchar(max) = NULL, -- Will make it optional and logic will work on all available sizes for product
Here is how I would deal with this. I would create a main procedure that receives all your parameters. Then a procedure for each possible path. So for example in Case-1 you would call the procedure that can handle that situation. The code would be very similar to when all parameters are passed except that you would just use a cross join to the ProductColor instead of splitting the parameter. Repeat this for each of your 3 new cases. Case-4 would be the code I already posted. Then you just need a procedure as the traffic controller. Sure you could do that all in a single procedure but you are going to have some serious performance problems. This article from Gail explains the performance benefits of this approach. http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply