Today's Query n Question:
-----------------------------------
I have 2 table's Productmaster n second is best dealmaster. Making of best deal products follow the logic of making bunch [group] of 2 or three product. I am saving the record in best deal separtly but maintaing information about productid by seperation of (",").
i.e 1,2,6,12,76,100 [ProductId in group in bestdeal product table]
Now I want to find the best deal product matching with main product like as I am showing any product and if any bestdeal exits related to this product id then it should be fatched...
Note: This query covers the topic of string based looping, i.e : loop depend on string in SQL Server and Cursor example.
Product Table :
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[nil_product_master](
[productid] [int] IDENTITY(1,1) NOT NULL,
[categoryid] [int] NOT NULL,
[othercatflag] [int] NOT NULL,
[productcode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[productname] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[productdesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ourprice] [float] NOT NULL,
[retailprice] [float] NOT NULL,
[b2bprice] [float] NULL,
[minb2bqty] [int] NULL,
[tax] [float] NULL,
[weight] [float] NULL,
[deliverytime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[stock] [int] NOT NULL,
[vendorid] [int] NOT NULL,
[discount] [float] NULL,
[metapagetitle] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[metakeyword] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[metadesc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[rank] [int] NOT NULL,
[producttype] [int] NOT NULL,
[matchingproduct] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[festival] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hits] [int] NULL,
[isbestseller] [int] NOT NULL,
[isfeatured] [int] NOT NULL,
[isspecial] [int] NOT NULL,
[status] [int] NOT NULL,
[createddate] [datetime] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[productid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
BestDeal Product Table :
USE [YourDataBase]
GO
/****** Object: Table [dbo].[nil_bestdeal_master] Script Date: 04/13/2009 17:15:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[nil_bestdeal_master](
[bestdealid] [int] IDENTITY(1,1) NOT NULL,
[products] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[totalprice] [float] NOT NULL,
[dealprice] [float] NOT NULL,
[bestdealimg] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[startdate] [datetime] NOT NULL,
[enddate] [datetime] NOT NULL,
[description] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[status] [int] NOT NULL,
[title] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MstBestDeal] PRIMARY KEY CLUSTERED
(
[bestdealid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
In the above table column products hold the productid such as : 2,5,8,21,10
Now the Solution is to make procedure below:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE profront_matchingbestdealproduct_sel
@CurrentDate DATETIME,
@CATEGORYNAME VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CATEGORYTID INT
DECLARE @IDIN VARCHAR(100)
DECLARE @DEALID INT
SELECT @CATEGORYTID=CATEGORYID FROM NIL_CATEGORY_MASTER WHERE CATNAME=@CATEGORYNAME
DECLARE db_cursor CURSOR FOR
SELECT BESTDEALID
FROM NIL_BESTDEAL_MASTER
WHERE STATUS=1
AND ENDDATE >=@CurrentDate
SET @IDIN=''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DEALID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @PRODUCTID VARCHAR(20)
DECLARE @STRCOUNT INT
DECLARE @CATID INT
DECLARE @FLAG INT
DECLARE @PID VARCHAR(20)
DECLARE @INDEX INT
SELECT @PRODUCTID =PRODUCTS FROM NIL_BESTDEAL_MASTER WHERE BESTDEALID=@DEALID AND STATUS=1
SET @FLAG=0
WHILE (max(len(ltrim(rtrim(@PRODUCTID))))>=0)
BEGIN
BEGIN
SET @PID=SUBSTRING(@PRODUCTID,0,charindex(',',@PRODUCTID))
SET @INDEX=charindex(',',@PRODUCTID)
SELECT @CATID=CATEGORYID FROM NIL_PRODUCT_MASTER WHERE PRODUCTID=@PID
IF(@CATEGORYTID=@CATID)
SET @FLAG=1
SET @STRCOUNT=max(len(ltrim(rtrim(@PRODUCTID))))
SET @PRODUCTID=SUBSTRING(@PRODUCTID,3,@STRCOUNT)
SET @STRCOUNT=max(len(ltrim(rtrim(@PRODUCTID))))
END
IF(@STRCOUNT<=0)
BREAK
ELSE
CONTINUE
END
IF(@FLAG=1)
SET @IDIN=@IDIN+','+CAST(@DEALID AS VARCHAR)
FETCH NEXT FROM db_cursor INTO @DEALID
END
CLOSE db_cursor
DEALLOCATE db_cursor
DECLARE @CNT INT
DECLARE @SQL VARCHAR(2000)
SET @CNT=max(len(ltrim(rtrim(@IDIN))))
SET @IDIN=SUBSTRING(@IDIN,2,@CNT)
IF(@IDIN='')
SET @IDIN='0'
SET @SQL='SELECT TOP 4 * FROM NIL_BESTDEAL_MASTER WHERE BESTDEALID IN('+@IDIN+')'
EXEC(@SQL)
END
