Dynamic SQL Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL Problem

Author

Topic

dotnetallday
Starting Member

16 Posts

Posted - 2005-12-07 : 01:11:09

I have a dynamic SQL stored procedure. It's not, however, returning the correct recordset I want. First a brief rundown of how the db is architected. This query goes against a few tables:

products
productid [pk]
manufacturerid [fk]

skus
skuid [pk]
productid [fk]

categories
categoryid [pk]

products_categories
productid [fk]
categoryid [fk]

manufacturers
manufacturer [pk]

I guess that's as simple as I can make it. SKUs are grouped into Products and the Products are then related to Categories and Manufacturers.

Here's my problem. I have 1629 Products. I have 3448 SKUs. I have 2050 Categories (yeah, it's a lot). I have 21 Manufacturers. Using my SPROC below, I get back 10,039 results back.

I only want the first SKUID for a given ProductID (my db allows multiple SKUs / Product). The first part of my query works correctly and returns 1629 Products for a keyword of "". It's when I then select from my temp table that I get all my results. What am I doing wrong? I'm sure it's in one of the joins but I can't seem to figure it out.

I know this is a huge question, but I appreciate any help you guys can give me. I haven't had an issue go unresolved here yet.

Thanks!

--------------
CREATE PROC dbo.Catalog_SearchSKUsByParam
(
@Keywords varchar(64),
@CategoryID int,
@ManufacturerID int,
@PriceLow money,
@PriceHigh money,
@AvailabilityID int,
@MemberID int,
@SKUTypeID int
)
AS

SET NOCOUNT ON

DECLARE @SQL varchar (4096)

-- Create Temp Table...
CREATE TABLE #tblSearch( ProductID int )

INSERT #tblSearch

SELECT DISTINCT(tblCatalog_Products.ProductID) AS ProductID
FROM tblCatalog_Products_Categories
INNER JOIN tblCatalog_Products ON tblCatalog_Products_Categories.ProductID = tblCatalog_Products.ProductID
INNER JOIN tblCatalog_Categories ON tblCatalog_Products_Categories.CategoryID = tblCatalog_Categories.CategoryID
LEFT OUTER JOIN tblCatalog_Manufacturers ON tblCatalog_Products.ManufacturerID = tblCatalog_Manufacturers.ManufacturerID
RIGHT OUTER JOIN tblCatalog_SKUs
INNER JOIN tblSettings_AvailabilityTypes ON tblCatalog_SKUs.AvailabilityID = tblSettings_AvailabilityTypes.AvailabilityID
LEFT OUTER JOIN tblCatalog_SKUs_Features ON tblCatalog_SKUs.SKUID = tblCatalog_SKUs_Features.SKUID ON tblCatalog_Products.ProductID = tblCatalog_SKUs.ProductID
WHERE (
(tblCatalog_SKUs.Name LIKE '%' + @Keywords + '%') OR
(tblCatalog_SKUs.Description LIKE '%' + @Keywords + '%') OR
(tblCatalog_SKUs.InternalSKU LIKE '%' + @Keywords + '%') OR
(tblCatalog_SKUs.ManufacturerSKU LIKE '%' + @Keywords + '%') OR
(tblCatalog_Products.Name LIKE '%' + @Keywords + '%') OR
(tblCatalog_Products.Summary LIKE '%' + @Keywords + '%') OR
(tblCatalog_SKUs_Features.Feature LIKE '%' + @Keywords + '%') OR
(tblCatalog_Categories.Name LIKE '%' + @Keywords + '%') OR
(tblCatalog_Manufacturers.Name LIKE '%' + @Keywords + '%')
)
AND tblCatalog_Products.Disabled = 0
AND tblCatalog_SKUs.Disabled = 0

GROUP BY tblCatalog_Products.ProductID

-- Set Dynamic SQL...
SET @SQL = 'SELECT tblCatalog_SKUs.SKUID,
tblCatalog_SKUs.ProductID,
tblCatalog_SKUs.InternalSKU,
tblCatalog_SKUs.ManufacturerSKU,
tblCatalog_SKUs.Inventory,
tblCatalog_SKUs.Weight,
tblCatalog_SKUs.ShippingWeight,
tblCatalog_SKUs.HandlingCharge,
tblCatalog_SKUs.MSRP,
dbo.GetSkuPrice(tblCatalog_SKUs.SKUID, ' + CAST(@MemberID As varchar) + ') AS Price,
tblCatalog_SKUs.Sale,
(tblCatalog_SKUs.Price - tblCatalog_SKUs.Sale) AS Savings,
tblCatalog_Products.Name,
tblCatalog_Products.Summary,
(SELECT Image1
FROM tblCatalog_SKUs_Images
WHERE SKUID = tblCatalog_SKUs.SKUID) AS Image1,
tblCatalog_Products_Categories.CategoryID,
ISNULL(tblCatalog_Manufacturers.ManufacturerID, 0) AS ManufacturerID,
tblSettings_AvailabilityTypes.Name AS Availability
FROM #tblSearch
INNER JOIN tblCatalog_Products ON #tblSearch.ProductID = tblCatalog_Products.ProductID
INNER JOIN tblCatalog_SKUs ON tblCatalog_Products.ProductID = tblCatalog_SKUs.ProductID
LEFT OUTER JOIN tblCatalog_Manufacturers ON tblCatalog_Products.ManufacturerID = tblCatalog_Manufacturers.ManufacturerID
INNER JOIN tblCatalog_Products_Categories ON tblCatalog_Products.ProductID = tblCatalog_Products_Categories.ProductID
INNER JOIN tblCatalog_Categories ON tblCatalog_Products_Categories.CategoryID = tblCatalog_Categories.CategoryID
INNER JOIN tblSettings_AvailabilityTypes ON tblCatalog_SKUs.AvailabilityID = tblSettings_AvailabilityTypes.AvailabilityID
WHERE tblCatalog_Products.Disabled = 0
AND tblCatalog_SKUs.Disabled = 0
AND tblCatalog_SKUs.SKUID = (
SELECT MIN(SKUID)
FROM tblCatalog_SKUs
WHERE tblCatalog_SKUs.ProductID = tblCatalog_Products.ProductID)'

-- Add Filters Based On Parameters...
DECLARE @ParamCategory varchar (128),
@ParamManufacturer varchar (128),
@ParamPriceLow varchar (128),
@ParamPriceHigh varchar (128),
@ParamAvailability varchar (128),
@ParamSkuType varchar (128)

SET @ParamCategory = ' '
SET @ParamManufacturer = ' '
SET @ParamPriceLow = ' '
SET @ParamPriceHigh = ' '
SET @ParamAvailability = ' '
SET @ParamSkuType = ' '

IF @CategoryID > 0
SET @ParamCategory = ' AND tblCatalog_Products_Categories.CategoryID = ' + CAST(@CategoryID As varchar)
IF @ManufacturerID > 0
SET @ParamManufacturer = ' AND tblCatalog_Manufacturers.ManufacturerID = ' + CAST(@ManufacturerID As varchar)
IF @PriceLow > 0
SET @ParamPriceLow = ' AND tblCatalog_SKUs.Price >= ' + CAST(@PriceLow AS varchar)
IF @PriceHigh > 0
SET @ParamPriceHigh = ' AND tblCatalog_SKUs.Price <= ' + CAST(@PriceHigh AS varchar)
IF @AvailabilityID > 0
SET @ParamAvailability = ' AND tblCatalog_SKUs.AvailabilityID = ' + CAST(@AvailabilityID As varchar)
IF @SKUTypeID > 0
SET @ParamSkuType = ' AND tblCatalog_SKUs.SKUTypeID = ' + CAST(@SKUTypeID As varchar)

-- Concatenate the SQL Query...
SET @SQL = @SQL + @ParamCategory + @ParamManufacturer + @ParamPriceLow + @ParamPriceHigh + @ParamAvailability + @ParamSkuType + '
ORDER BY tblCatalog_Products.Name ASC'

-- Execute the SQL Query...
EXEC (@SQL)

-- Drop Temp Table...
DROP TABLE #tblSearch
GO

Jason A. Kiesel
Kiesel Media Group, Inc.
http://www.kmg.net
http://hosting.kmg.net

*ASP.NET Web Hosting Starting @ $9.99 / Month

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-12-07 : 06:01:12

Hi,
may suggest to read :

http://www.sommarskog.se/dynamic_sql.html

:-)
Regards

Andy Davis
Activecrypt Team
--------------------------------------------
SQL Server Encryption Software
http://www.activecrypt.com

tfertil
Starting Member

4 Posts

Posted - 2005-12-07 : 08:04:45

Wow! it's a long sentence...

First: when you get duplicated or incorrect row quantities, allways suspect your JOINS. Try testing manually your joins, something like

SELECT * FROM <your table> INNER JOIN <first join>

then

SELECT * FROM <your table> INNER JOIN <first join> INNER JOIN <second join> ... and so on..

In your case: you said you have multiple SKU per product... but the second part of you SQL contains:

INNER JOIN tblCatalog_SKUs ON tblCatalog_Products.ProductID = tblCatalog_SKUs.ProductID

Then, your temp table can contain the correct number of rows, but then your getting one row per each SKU/ProductID, not for each record in your temp table. If you want only ONE SKU per producto, you must correct this join to get only one record or somehow remove it and obtain the field you're interested with another SELECT

quote:
Originally posted by activecrypt

Hi,
may suggest to read :

http://www.sommarskog.se/dynamic_sql.html

:-)
Regards

Andy Davis
Activecrypt Team
--------------------------------------------
SQL Server Encryption Software
http://www.activecrypt.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources