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
 Transact-SQL (2000)
 Need Help...conditional parameters

Author  Topic 

sjamesje
Starting Member

7 Posts
Posted - 2004-04-14 : 17:58:17
I am trying to write a query that will accept conditional parameters, where if there is data in one parameter,but not in the other, that it will fill the result set with the specific result set passed to it, but all of the available data from the other result set.

For example, I have two parameters, Store # and Category ID. Store ID is 7700 and Category ID is 02. When I execute my query, I get all items for Store # 7700 in category 02. If however, I want to get a result set from store # 7700 but from all categories, I set 7700 as my first parameter and I leave the category ID blank, I get a strange mix off all stores and categories. What is the best way to go about this? Here is my query...any help that you can offer would be appreciated, thanks in advance!
---------------------------------------------------------------------
/*CREATE PROCEDURE MPSFINAL (@STORE_ID VARCHAR(10),
@CATEGORY_ID VARCHAR(10),
@START_DATE DATETIME,
@END_DATE DATETIME)
*/

BEGIN
--SET THE DATE FORMAT TO IGNORE THE TIME
SET DATEFORMAT MDY

DECLARE @STORE_ID VARCHAR(10)
DECLARE @CATEGORY_ID VARCHAR(10)
DECLARE @START_DATE DATETIME
DECLARE @END_DATE DATETIME
SET @STORE_ID = '7700'
SET @CATEGORY_ID = '02'
SET @START_DATE = '1/10/2004'
SET @END_DATE = '1/10/2004'

SET @START_DATE=CAST(DATEPART(MM, @START_DATE)AS VARCHAR(2))+'/'+
CAST(DATEPART(DD, @START_DATE)AS VARCHAR(2))+'/'+
CAST(DATEPART(YY, @START_DATE)AS VARCHAR(4))
SET @END_DATE=CAST(DATEPART(MM, @END_DATE)AS VARCHAR(2))+'/'+
CAST(DATEPART(DD, @END_DATE)AS VARCHAR(2))+'/'+
CAST(DATEPART(YY, @END_DATE)AS VARCHAR(4))
IF (@STORE_ID <>'') AND (@CATEGORY_ID <>'')
BEGIN

SELECT COALESCE (srprd.STORE_ID, srpackxref.STORE_ID)
AS STORE_NUMBER,
COALESCE (srpackxref.DBR_DATE,
srprd.DBR_DATE) AS DBR_DATE,
srpackxref.CATEGORY_ID + srprd.CATEGORY_ID AS CATEGORY,
COALESCE (srpackxref.UPC_CODE, srprd.UPC_CODE)
AS UPC_CODE,
srpackxref.DESCRIPTION + srprd.PRODUCT_DESC AS DESCRIPTION,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER)
AS MULTIPLIER, COALESCE (srpackxref.QTY_SOLD,
srprd.QTY_SOLD / srprd.MULTIPLIER) AS DEALS_SOLD,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER)
* COALESCE (srpackxref.QTY_SOLD,
srprd.QTY_SOLD / srprd.MULTIPLIER) AS SINGLES_SOLD
FROM srpackxref FULL OUTER JOIN
srprd ON srpackxref.MULTIPLIER = srprd.MULTIPLIER AND
srpackxref.PRODUCT_NUM = srprd.PRODUCT_NUM AND
srpackxref.STORE_ID = srprd.STORE_ID AND
srpackxref.DBR_DATE = srprd.DBR_DATE
WHERE (COALESCE (srprd.STORE_ID, srpackxref.STORE_ID)
= @STORE_ID) AND (COALESCE (srpackxref.DBR_DATE,
srprd.DBR_DATE) >= @START_DATE) AND (COALESCE (srpackxref.DBR_DATE,
srprd.DBR_DATE) <= @END_DATE) AND (srpackxref.CATEGORY_ID + srprd.CATEGORY_ID = @CATEGORY_ID)
GROUP BY COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER), COALESCE (srprd.STORE_ID, srpackxref.STORE_ID),
COALESCE (srpackxref.DBR_DATE, srprd.DBR_DATE), srpackxref.CATEGORY_ID + srprd.CATEGORY_ID,
COALESCE (srpackxref.UPC_CODE, srprd.UPC_CODE), srpackxref.DESCRIPTION + srprd.PRODUCT_DESC,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER), COALESCE (srpackxref.QTY_SOLD,
srprd.QTY_SOLD / srprd.MULTIPLIER), (COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER)
* COALESCE (srpackxref.QTY_SOLD,
srprd.QTY_SOLD / srprd.MULTIPLIER))
END
ELSE
SELECT COALESCE (srprd.STORE_ID, srpackxref.STORE_ID)
AS STORE_NUMBER,
COALESCE (srpackxref.DBR_DATE,
srprd.DBR_DATE) AS DBR_DATE,
srpackxref.CATEGORY_ID + srprd.CATEGORY_ID AS CATEGORY,
COALESCE (srpackxref.UPC_CODE, srprd.UPC_CODE)
AS UPC_CODE,
srpackxref.DESCRIPTION + srprd.PRODUCT_DESC AS DESCRIPTION,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER)
AS MULTIPLIER, COALESCE (srpackxref.QTY_SOLD,
srprd.QTY_SOLD / srprd.MULTIPLIER) AS DEALS_SOLD,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER)
* COALESCE (srpackxref.QTY_SOLD,
srprd.QTY_SOLD / srprd.MULTIPLIER) AS SINGLES_SOLD
FROM srpackxref FULL OUTER JOIN
srprd ON srpackxref.MULTIPLIER = srprd.MULTIPLIER AND
srpackxref.PRODUCT_NUM = srprd.PRODUCT_NUM AND
srpackxref.STORE_ID = srprd.STORE_ID AND
srpackxref.DBR_DATE = srprd.DBR_DATE
WHERE (COALESCE (srpackxref.DBR_DATE,
srprd.DBR_DATE) >= @START_DATE) AND (COALESCE (srpackxref.DBR_DATE,
srprd.DBR_DATE) <= @END_DATE)
GROUP BY COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER), COALESCE (srprd.STORE_ID, srpackxref.STORE_ID),
COALESCE (srpackxref.DBR_DATE, srprd.DBR_DATE), srpackxref.CATEGORY_ID + srprd.CATEGORY_ID,
COALESCE (srpackxref.UPC_CODE, srprd.UPC_CODE), srpackxref.DESCRIPTION + srprd.PRODUCT_DESC,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER), COALESCE (srpackxref.QTY_SOLD,
srprd.QTY_SOLD / srprd.MULTIPLIER), (COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER)
* COALESCE (srpackxref.QTY_SOLD,
srprd.QTY_SOLD / srprd.MULTIPLIER))
END

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-04-14 : 21:20:27
CREATE PROCEDURE MPSFINAL (@STORE_ID VARCHAR(10),
@CATEGORY_ID VARCHAR(10),
@START_DATE DATETIME,
@END_DATE DATETIME)
AS

BEGIN
--SET THE DATE FORMAT TO IGNORE THE TIME
SET DATEFORMAT MDY

DECLARE @STORE_ID VARCHAR(10)
DECLARE @CATEGORY_ID VARCHAR(10)
DECLARE @START_DATE DATETIME
DECLARE @END_DATE DATETIME

SET @STORE_ID = '7700'
SET @CATEGORY_ID = '02'
SET @START_DATE = '1/10/2004'
SET @END_DATE = '1/10/2004'

SET @START_DATE=CAST(DATEPART(MM, @START_DATE)AS VARCHAR(2))+'/'+
CAST(DATEPART(DD, @START_DATE)AS VARCHAR(2))+'/'+
CAST(DATEPART(YY, @START_DATE)AS VARCHAR(4))

SET @END_DATE=CAST(DATEPART(MM, @END_DATE)AS VARCHAR(2))+'/'+
CAST(DATEPART(DD, @END_DATE)AS VARCHAR(2))+'/'+
CAST(DATEPART(YY, @END_DATE)AS VARCHAR(4))

IF (@STORE_ID <>'') AND (@CATEGORY_ID <>'')
BEGIN

SELECT
COALESCE (srprd.STORE_ID, srpackxref.STORE_ID) AS STORE_NUMBER,
COALESCE (srpackxref.DBR_DATE, srprd.DBR_DATE) AS DBR_DATE,
srpackxref.CATEGORY_ID + srprd.CATEGORY_ID AS CATEGORY,
COALESCE (srpackxref.UPC_CODE, srprd.UPC_CODE) AS UPC_CODE,
srpackxref.DESCRIPTION + srprd.PRODUCT_DESC AS DESCRIPTION,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER) AS MULTIPLIER,
COALESCE (srpackxref.QTY_SOLD, srprd.QTY_SOLD / srprd.MULTIPLIER) AS DEALS_SOLD,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER) * COALESCE (srpackxref.QTY_SOLD, srprd.QTY_SOLD / srprd.MULTIPLIER) AS SINGLES_SOLD
FROM
srpackxref
FULL OUTER JOIN srprd ON srpackxref.MULTIPLIER = srprd.MULTIPLIER
AND srpackxref.PRODUCT_NUM = srprd.PRODUCT_NUM
AND srpackxref.STORE_ID = srprd.STORE_ID
AND srpackxref.DBR_DATE = srprd.DBR_DATE
WHERE
(@STORE_ID = '' OR (COALESCE (srprd.STORE_ID, srpackxref.STORE_ID) = @STORE_ID))
AND (COALESCE (srpackxref.DBR_DATE, srprd.DBR_DATE) >= @START_DATE)
AND (COALESCE (srpackxref.DBR_DATE, srprd.DBR_DATE) <= @END_DATE)
AND (@CATEGORY = '' OR srpackxref.CATEGORY_ID + srprd.CATEGORY_ID = @CATEGORY_ID))
GROUP BY
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER),
COALESCE (srprd.STORE_ID, srpackxref.STORE_ID),
COALESCE (srpackxref.DBR_DATE, srprd.DBR_DATE),
srpackxref.CATEGORY_ID + srprd.CATEGORY_ID,
COALESCE (srpackxref.UPC_CODE, srprd.UPC_CODE),
srpackxref.DESCRIPTION + srprd.PRODUCT_DESC,
COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER),
COALESCE (srpackxref.QTY_SOLD, srprd.QTY_SOLD / srprd.MULTIPLIER),
(COALESCE (srpackxref.MULTIPLIER, srprd.MULTIPLIER) * COALESCE (srpackxref.QTY_SOLD, srprd.QTY_SOLD / srprd.MULTIPLIER))

This might be some of the most screwed up code I've ever seen in my life. What in the world are you doing with this????

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

sjamesje
Starting Member

7 Posts
Posted - 2004-04-15 : 10:29:35
HaHaHa!!! I agree, it IS the most screwed up code I've ever written! But it's the only thing that me and my team was able to come up with to pull the data from this craptastic database that we've been blessed with! This database is nowhere NEAR normalized...
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site