May 21, 2015 at 1:27 pm
I am trying to make my code more efficient because I am going to have to write about 120 different iterations of it.
I want to see if any records exist using certain criteria. If they do, I want the records. If not, I want a message telling me there are no records. Here is a sample of what I want to do:
IF OBJECT_ID('TempDB..#Product','U') IS NOT NULL DROP TABLE #Product
GO
CREATE TABLE #Product
(
ProductNVARCHAR(20),
QuantityDECIMAL(10,2),
WarehouseNVARCHAR(20)
)
INSERT INTO #Product
(Product, Quantity, Warehouse)
SELECT 'Widget1', 1, 'WH1' UNION ALL
SELECT 'Widget1', 0, 'WH2' UNION ALL
SELECT 'Widget2', 14, 'WH1' UNION ALL
SELECT 'Widget2', 5,'WH2' UNION ALL
SELECT 'Widget3', 7, 'WH1' UNION ALL
SELECT 'Widget3', 0, 'WH2'
--SELECT* FROM #Product
DECLARE @Warehouse NVARCHAR(20)
SET @Warehouse = 'WH1'
IF (SELECT SUM(Quantity)
FROM #Product
WHERE Warehouse = @Warehouse
GROUP BY Warehouse) > 10
BEGIN
SELECT Warehouse, SUM(Quantity) TotalQty
FROM #Product
WHERE Warehouse = @Warehouse
GROUP BY Warehouse
END
ELSE
BEGIN
PRINT 'There are less than 10 items in this warehouse'
END
/*
Desired Result
If @Warehouse = 'WH2'
There are less than 10 items in this warehouse
If @Warehouse = 'WH1'
WarehouseTotalQty
-----------------
WH122.00
*/
This is obviously a lot simpler than the code I am actually using. My biggest problem with this is that I essentially have to write the code for the IF statement, then rewrite it for the SELECT statement. Is there any way I can "shorthand" this?
Thanks.
Steve
May 21, 2015 at 1:41 pm
How about:
SELECT X.Warehouse,
CASE WHEN Total_Quantity > 10 THEN CONVERT(varchar(100), Total_Quantity) ELSE 'There are less than 10 items in this warehouse' END
FROM
(SELECT Warehouse, SUM(Quantity) Total_Quantity
FROM #Product
GROUP BY Warehouse) X
WHERE X.Warehouse = @Warehouse
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 21, 2015 at 1:50 pm
Sometimes I make things more difficult than they should be. Thank you for the tip.
May 21, 2015 at 3:46 pm
How about?:
SELECT p.Warehouse,
CASE WHEN SUM(Quantity) >= 10
THEN CAST(SUM(Quantity) AS varchar(30))
ELSE 'There are fewer than 10 items in this warehouse.'
END AS Quantity
FROM #Product p
WHERE p.Warehouse = @Warehouse
GROUP BY p.Warehouse
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply