Challenging (for me) Stored Procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Challenging (for me) Stored Procedure

I’m trying to find all the products which X and Y fall within different ranges. Its hard to explain, i’ll just get into it: Sizes
————-
SizeID
topY
bottomY
leftX
RightX
Products
————
ProductID
prodX
prodY The problem is that I want to check to see if the product X and Y fit in any/all of the different sizes that I pass the procedure. So I would pass the stored procedure SearchXY a common delimited varchar of SizeIDs, such as "2,3,4". From there I need it to check for the products that all fit within just those sizeIDs. Example tables: Size Rows
———-
SizeID: 1, topY: 2, bottomY: 3, leftX: 3, RightX:2
SizeID: 2, topY: 1, bottomY: 7, leftX: 3, RightX:4
SizeID: 3, topY: 2, bottomY: 2, leftX: 5, RightX:8
SizeID: 4, topY: 3, bottomY: 3, leftX: 4, RightX:8
SizeID: 5, topY: 1, bottomY: 5, leftX: 4, RightX:7
Product Rows
————–
ProductID: 1, prodX: 3, prodY: 9
ProductID: 2, prodX: 2, prodY: 6
ProductID: 3, prodX: 4, prodY: 7
ProductID: 4, prodX: 3, prodY: 5 So I want to find just the products that fit in the ranges of the SizeIDs that I pass it. I could pass it 1 to 5 different sizeIDs, and have the database return what products are in those selected sizes. Thanks!
Can you explain this further? I don’t get the problem. v/r Gooser
The explain is not clear for me. what is prodx and prody represent?
quote:product X and Y fit in any/all of the different sizes
what do you mean by "fit in " all "different sizes"?
How do you calculate the size with the parameters – topY, bottomY, leftX, RightX? can you explain us your requirement in business needs .i.e. for what product are you writing this code? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
I am going to pass the stored procedure a comma delimited list of SizeIDs. For example, I will pass the parameter "2,3,4". From that paramter, I want the procedure to return all the of products, which fit in those sizes. Size Rows
———-
SizeID: 1, topY: 2, bottomY: 3, leftX: 3, RightX:2
SizeID: 2, topY: 1, bottomY: 7, leftX: 3, RightX:4
SizeID: 3, topY: 2, bottomY: 2, leftX: 5, RightX:8
SizeID: 4, topY: 3, bottomY: 3, leftX: 4, RightX:8
SizeID: 5, topY: 1, bottomY: 5, leftX: 4, RightX:7
Product Rows
————–
ProductID: 1, prodX: 3, prodY: 9
ProductID: 2, prodX: 2, prodY: 6
ProductID: 3, prodX: 4, prodY: 7
ProductID: 4, prodX: 3, prodY: 5
Something along the lines of:
Product.prodY < Size.topY AND Product.prodY > Size.bottomY AND
Product.prodX < Size.rightX AND Product.prodX > Size.leftX
But for every SizeID in the size table, which we passed in the parameter. And of that list of sizes the product can be, I need all the products that fit in that list. I guess this is a tough one to explain. Thanks.
You can write a query based on two tables without a JOIN, where the joining is done in the WHERE statement, like this: SELECT Product.*
FROM Product, Size
WHERE Product.prodY < Size.topY AND Product.prodY > Size.bottomY AND
Product.prodX < Size.rightX AND Product.prodX > Size.leftX … or with a JOIN that uses logical operators other than =, like this: SELECT Product.*
FROM Product INNER JOIN Size
ON Product.prodY < Size.topY AND Product.prodY > Size.bottomY AND
Product.prodX < Size.rightX AND Product.prodX > Size.leftX

IF this is your data: Size Rows
———-
SizeID: 1, topY: 2, bottomY: 3, leftX: 3, RightX:2
SizeID: 2, topY: 1, bottomY: 7, leftX: 3, RightX:4
SizeID: 3, topY: 2, bottomY: 2, leftX: 5, RightX:8
SizeID: 4, topY: 3, bottomY: 3, leftX: 4, RightX:8
SizeID: 5, topY: 1, bottomY: 5, leftX: 4, RightX:7 There are some real problems with your data, or you need to more clearly define ‘top’ and ‘bottom’ as well as ‘left’ and ‘right’. I am assuming that this is an X,Y Cartesian coordinate system, correct? Well,…
for SizeID 1 the LeftX is to the right of the RightX,
for SizeID 2 the topY is below the bottomY,
for SizeID 3 the bottom and top are at the same level,
for SizeID 4 the bottom and top are at the same level, and
for SizeID 5 the data is okay, but none of the products fall within it. Can you provide us with a healthier dataset? I nearly have a query done. Please provide better data, because I want to see if my solution will work properly, or not. ‘Why do today
that which might not need to be done tomorrow?’ –me
v/r Gooser
The size rows was just random numbers I quickly came up with, not actual data. Here is a better data, and yes its based off of X,Y coordinates.
SizeID: 1, topY: 2, bottomY: -3, leftX: -3, RightX:2
SizeID: 2, topY: 1, bottomY: -7, leftX: 3, RightX:4
SizeID: 3, topY: 2, bottomY: -2, leftX: 5, RightX:8
SizeID: 4, topY: 5, bottomY: 3, leftX: 4, RightX:8
SizeID: 5, topY: 2, bottomY: -5, leftX: 1, RightX:7
ProductID: 1, prodY: 2, prodX: 1
ProductID: 2, prodY: 6, prodX: 2
ProductID: 3, prodY: 1, prodX: 4
ProductID 1 fits in SizeID 1 and 5
ProductID 2 fits in nothing
ProductID 3 fits in SizeID 2 and 5
That should clear it up.
Ah, yes… Yet another place to use the ubiquitous COALESCE function! Here is the stored procedure in it’s entirety. /******************************************
* Stored Procedure: Product
* Created by: Stephen Lauzon
* Created on: 12 July 2006
* Created to: Select all products that fit the passed in 1-5 sizes
* Updated by:
* Updated on:
* Updated to:
******************************************/ /* EXEC dbo.product_selectBySizeIDList 1, 2, 3, 4, 7 */ CREATE PROC dbo.Product_SelectBySizeIDList
@sizeId1 int = NULL
, @sizeId2 int = NULL
, @sizeId3 int = NULL
, @sizeId4 int = NULL
, @sizeId5 int = NULL AS SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON SELECT pr.productid
, sz.sizeid
FROM product AS pr
JOIN ( SELECT sizeid
, topy
, bottomy
, leftx
, rightx
FROM size
WHERE sizeid = COALESCE(@sizeId1, NULL)
OR sizeid = COALESCE(@sizeId2, NULL)
OR sizeid = COALESCE(@sizeId3, NULL)
OR sizeid = COALESCE(@sizeId4, NULL)
OR sizeid = COALESCE(@sizeId5, NULL)
) AS sz
ON ( pr.prodx >= sz.leftx AND pr.prodx <= sz.rightx )
AND ( pr.prody >= sz.bottomy AND pr.prody <= sz.topy )
p.s.- Please mention me in your comments. v/r Gooser
]]>