Evaluation - 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 2005 Forums

Transact-SQL (2005)

Evaluation

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-07-11 : 08:55:50

USE [RDMData]

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ts_GetMenuItem_Lookup_ButtonListTemp]')
AND type in (N'P'))
BEGIN
DROP PROCEDURE [DBO].[ts_GetMenuItem_Lookup_ButtonListTemp]
END
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
Author : Aravind
Date of Creation : 20-06-2007
-----------------------------------------------------------------------------
Modified : DBE DATE

-----------------------------------------------------------------------------
Input : (i) StoreId
(2)
-----------------------------------------------------------------------------
OutPut :
-----------------------------------------------------------------------------
Tables User : (1) BUTTON
(2) MenuItem
-----------------------------------------------------------------------------
Purpose : To List All the Buttons of KeyType - 1 , 2 , 7

-----------------------------------------------------------------------------
To Run :-
-- Exec ts_GetMenuItem_Lookup_ButtonListTemp 2 ,0
-----------------------------------------------------------------------------
*/

CREATE PROCEDURE [dbo].[ts_GetMenuItem_Lookup_ButtonListTemp](
@p_StoreId INT,
@p_Compare BIT
)
AS
BEGIN
SET NOCOUNT ON
-- Error Handling Declarations Start
DECLARE @l_ProcName VARCHAR(100)
DECLARE @l_ErrState INT
DECLARE @l_RowCount INT
DECLARE @l_ErrCode INT
-- Error Handling Declarations End

-- Procedure Variable declarations Start
DECLARE @l_GroupCount INT
DECLARE @l_Count INT
DECLARE @l_loop INT
DECLARE @l_KeyNo INT
DECLARE @l_PageNo INT
DECLARE @l_StoreCount INT
DECLARE @l_Diff VARCHAR(5)

DECLARE @HeaderDetails TABLE
(
PageNumber INT ,
KeyNumber INT
)

DECLARE @TempButton TABLE
( RowNum INT IDENTITY (1,1) NOT NULL,
ButtonId INT,
PageNumber INT,
KeyNumber INT,
ButtonLine1 VARCHAR(8),
ButtonLine2 VARCHAR(8),
ButtonLine3 VARCHAR(8),
InteriorRows TINYINT,
InteriorColumns TINYINT,
TextLines TINYINT,
ButtonStatus BIT,
ButtonColor INT,
KeyType TINYINT,
StoreId INT,
ItemId INT,
ItemIndex INT,
ItemName VARCHAR(255),
[Difference] VARCHAR(3),
PendingChanges VARCHAR(3)
)

DECLARE @TempButtonAll TABLE
( RowNum INT IDENTITY (1,1) NOT NULL,
ButtonId INT,
PageNumber INT,
KeyNumber INT,
ButtonLine1 VARCHAR(8),
ButtonLine2 VARCHAR(8),
ButtonLine3 VARCHAR(8),
InteriorRows TINYINT,
InteriorColumns TINYINT,
TextLines TINYINT,
ButtonStatus BIT,
ButtonColor INT,
KeyType TINYINT,
StoreId INT,
ItemId INT,
ItemIndex INT,
ItemName VARCHAR(255)

)
-- Procedure Variable declarations End
--- Initialize Variables

SET @l_Count = 0
SET @l_loop = 0
SET @l_KeyNo = 0
SET @l_PageNo = 0
SET @l_StoreCount = 0
SET @l_Diff = ''
SET @l_GroupCount = 0

--- Initialize Variables Ends
BEGIN TRY
-- Error Handling variable initializations Start
SET @l_ProcName = 'ts_GetMenuItem_Lookup_ButtonListTemp'
SET @l_ErrState = 0
SET @l_ErrCode = 0

-- Error Handling variable initializations End

IF @p_StoreId IS NULL
BEGIN
SELECT @l_ErrCode = 1
SET @l_ErrState = 1
GOTO ENDPGM
END
INSERT INTO @TempButton
( ButtonId ,
PageNumber , KeyNumber , ButtonLine1 ,
ButtonLine2 , ButtonLine3 , InteriorRows ,
InteriorColumns , TextLines , ButtonStatus ,
ButtonColor , KeyType , StoreId ,
ITemId , ItemIndex , ItemName ,
[Difference] , PendingChanges )
SELECT
b.ButtonID ,
b.PageNumber , b.KeyNumber ,
b.ButtonLine1 , b.ButtonLine2 , b.ButtonLine3 ,
b.InteriorRows , b.InteriorColumns , b.TextLines ,
b.ButtonStatus , b.ButtonColor , b.KeyType ,
b.StoreId , COALESCE(b.ItemID,'') AS ItemId ,
CASE b.KeyType WHEN 6
THEN COALESCE(f.FunctionIndex,'')
ELSE COALESCE(m.MenuItemIndex,'') END AS ItemIndex,
CASE b.KeyType WHEN 6
THEN COALESCE(f.PrintName , '')
ELSE COALESCE(m.PrintName,'') END AS ItemName ,
'' AS [Difference] ,
'' AS [PendingChanages]

FROM
[dbo].[Button] b
LEFT OUTER JOIN
[dbo].[MenuItem] m
ON
b.ItemId = m.MenuItemId
AND b.StoreId = m.StoreId

LEFT OUTER JOIN
[dbo].[Functions] f
ON
b.ItemId = f.FunctionID
AND b.StoreID = f.StoreId

WHERE
b.KEYTYPE IN (1,2,6,7)
AND b.StoreId = @p_StoreId
AND b.PageNumber BETWEEN 0 AND 9
OR b.PAgeNumber BETWEEN 18 AND 27
ORDER BY
b.PageNumber , b.KeyType

-----Adding Data from DataChanges Tables

INSERT INTO @HeaderDetails
SELECT DISTINCT
D.PageNumber , D.KeyNumber
FROM
( SELECT H.DataChangesId , H.IsApplied,
H.ModuleNumber , H.StoreId,
D.RowIdentifier , D.RowIdentifierValue,
cast(SUBSTRING(IsNull(D.RowIdentifierValue,''),1,IsNull(CHARINDEX('~',IsNull(D.RowIdentifierValue,''),1),0)-1) as INT) aS PageNumber,
CAST(SUBSTRING(ISNull(D.RowIdentifierValue,''),IsNull(CHARINDEX('~',ISNull(D.RowIdentifierValue,''),1),0)+1,
LEN(ISNull(D.RowIdentifierValue,''))- ISNull(CHARINDEX('~',ISNull(D.RowIdentifierValue,''),1),0)
)as INT) as KeyNumber
FROM DATACHANGESDETAIL D
JOIN DATACHANGESHEADER H
ON D.DataChangesId = H.DataChangesId
WHERE H.ModuleNumber = 451
AND H.IsApplied = 0
And H.StoreId = @p_StoreId
)D

-----Adding Data from DataChanges Tables Ens

--- Count the Total Row of Base RecordSet

SELECT @l_Count = MAX(RowNum) FROM @TempButton

IF @p_Compare = 1 ---- Comparison needed
BEGIN
-- Insert the data relates to all stores even if the storeid is a valid Store Group ID

INSERT INTO @TempButtonAll
( ButtonId ,
PageNumber , KeyNumber , ButtonLine1 ,
ButtonLine2 , ButtonLine3 , InteriorRows ,
InteriorColumns , TextLines , ButtonStatus ,
ButtonColor , KeyType , StoreId ,
ItemId , ItemIndex , ItemName )
SELECT
b.ButtonId ,
b.PageNumber , b.KeyNumber ,
b.ButtonLine1 , b.ButtonLine2 , b.ButtonLine3 ,
b.InteriorRows , b.InteriorColumns , b.TextLines ,
b.ButtonStatus , b.ButtonColor , b.KeyType ,
b.StoreId , COALESCE(b.ItemID,'') AS ItemId ,
CASE b.KeyType WHEN 6
THEN COALESCE(f.FunctionIndex,'')
ELSE COALESCE(m.MenuItemIndex,'') END AS ItemIndex,
CASE b.KeyType WHEN 6
THEN COALESCE(f.PrintName , '')
ELSE COALESCE(m.PrintName,'') END AS ItemName
FROM
[dbo].[Button] b
LEFT OUTER JOIN
[dbo].[MenuItem] m
ON
b.ItemId = m.MenuItemId
AND b.StoreId = m.StoreId

LEFT OUTER JOIN
[dbo].[Functions] f
ON
b.ItemId = f.FunctionID
AND b.StoreID = f.StoreId

WHERE
b.KEYTYPE IN (1,2,6,7)
AND (b.PageNumber BETWEEN 0 AND 9
OR b.PAgeNumber BETWEEN 18 AND 27)
AND b.StoreId IN ( SELECT StoreId FROM StoreGroups WHERE StoreGroupId = @p_StoreId And StoreId <> @p_StoreId)
ORDER BY
b.PageNumber , b.KeyType , b.StoreId
---- Insertion Ends

---- Get the number of stores that come under the given groupstore

SELECT @l_StoreCount = COUNT(StoreId) -1 FROM StoreGroups WHERE StoreGroupId = @p_StoreId

IF @l_GroupCount > 0
BEGIN
--- Sub Store Exist
WHILE @l_loop <= @l_Count
BEGIN
SELECT
@l_KeyNo = gt.KeyNumber ,
@l_PageNo = gt.PageNumber,
@l_Diff = (CASE
WHEN gt.ButtonLine1 = bt.ButtonLine1 AND
gt.ButtonLine2 = bt.ButtonLine2 AND
gt.ButtonLine3 = bt.ButtonLine3 AND
gt.InteriorRows = bt.InteriorRows AND
gt.InteriorColumns = bt.InteriorColumns AND
gt.TextLines = bt.TextLines AND
gt.ButtonStatus = bt.ButtonStatus AND
gt.ButtonColor = bt.ButtonColor AND
gt.KeyType = bt.KeyType AND
gt.ItemId = bt.ItemId
THEN 'No'
ELSE 'Yes'
END)
FROM
@TempButtonAll gt
JOIN
@TempButton bt
ON
gt.PageNumber = bt.PageNumber AND
gt.KeyNumber = bt.KeyNumber
WHERE
--gt.StoreId IN(SELECT StoreId FROM StoreGroups WHERE StoreGroupId = @p_StoreId And StoreId <> @p_StoreId)
--AND
bt.RowNum = @l_loop

--- Checks if same record exists in each stores under the group else difference exists
--- This makes to update the base table for future process

IF NOT EXISTS (SELECT COUNT(KeyNumber) FROM @TempButtonAll WHERE PageNumber = @l_PageNo AND KeyNumber = @l_KeyNo
HAVING COUNT(KeyNumber) = @l_StoreCount )
BEGIN
--- Difference -- ie not all stores contain same record under a store group

UPDATE @TempButton
SET [Difference] = 'Yes'
WHERE PageNumber = @l_PageNo AND
KeyNumber = @l_KeyNo AND
RowNum = @l_loop
END
ELSE
BEGIN
-- No Difference
UPDATE @TempButton
SET [Difference] = 'No'
WHERE PageNumber = @l_PageNo AND
KeyNumber = @l_KeyNo AND
RowNum = @l_loop
END
-- Flush out the processed Row and its relating record in various stores under a store group

DELETE FROM @TempButtonAll WHERE KeyNumber = @l_KeyNo AND PageNumber = @l_PageNo

SET @l_loop = @l_loop + 1

END ---- While Ends

END ---@l_GroupCount
ELSE
BEGIN
--- No Stores for this StoreGroup

UPDATE @TempButton
SET [Difference] = 'No'

END

--SELECT * FROM @TempButtonAll
--SELECT * FROM @TempButton

-- Get bUTTONS of the given group

SELECT
ButtonId , B.PageNumber , B.KeyNumber , ButtonLine1 ,
ButtonLine2 , ButtonLine3 , InteriorRows , InteriorColumns ,
TextLines , ButtonStatus , ButtonColor , KeyType ,
B.StoreId , ItemId , ItemIndex , ItemName,
[Difference] ,
CASE WHEN B.PageNumber = D.PageNumber And B.KeyNumber = D.KeyNumber
THEN 'Yes'
ELSE 'No' END AS PendingChanges

FROM
@TempButton B
LEFT OUTER JOIN
@HeaderDetails D
ON B.PageNumber = D.PageNumber
And B.KeyNumber = D.KeyNumber
WHERE
B.StoreId = @p_StoreId
ORDER BY
B.PageNumber , B.KeyNumber

-- Get Buttons which are not in group,but in DataChanges

SELECT DISTINCT PageNumber , KeyNumber
FROM @HeaderDetails
WHERE PageNumber NOT IN (SELECT PageNumber FROM @TempButton WHERE StoreId = @p_StoreId )
OR
( PageNumber IN (SELECT PageNumber FROM @TempButton WHERE StoreId = @p_StoreId) AND
KeyNumber NOT IN (SELECT KeyNumber FROM @TempButton WHERE StoreId = @p_StoreId)
)

------ Get Buttons which are not in group but in any of the stores under the group

SELECT
PageNumber ,KeyNumber
FROM
( SELECT DISTINCT PageNumber , KeyNumber
FROM @TempButton
WHERE StoreId IN (SELECT StoreId FROM StoreGroups WHERE StoreGroupID = @p_StoreId)) ST
WHERE
PageNumber NOT IN (SELECT PageNumber FROM @TempButton WHERE StoreId = @p_StoreId)
OR
( PageNumber IN (SELECT PageNumber FROM @TempButton WHERE StoreId = @p_StoreId) AND
KeyNumber NOT IN (SELECT KeyNumber FROM @TempButton WHERE StoreId = @p_StoreId )
)
ORDER BY
PageNumber ,KeyNumber

END ---- COMPARE
ELSE IF @p_Compare = 0
BEGIN
---- WITHOUT Comparison
SELECT
ButtonId , PageNumber , KeyNumber , ButtonLine1 ,
ButtonLine2 , ButtonLine3 , InteriorRows , InteriorColumns ,
TextLines , ButtonStatus , ButtonColor , KeyType ,
StoreId , ItemId , ItemIndex , ItemName ,
'No' AS [Difference], 'No' AS PendingChanges
FROM
@TempButton B
WHERE
B.StoreId = @p_StoreId
-- Get Button which are not in group,but in DataChanges

SELECT
PageNumber , KeyNumber
FROM
@HeaderDetails
WHERE
PageNumber NOT IN (SELECT PageNumber FROM @TempButton WHERE StoreId = @p_StoreId)
OR
( PageNumber IN (SELECT PageNumber FROM @TempButton WHERE StoreId = @p_StoreId) AND
KeyNumber NOT IN (SELECT KeyNumber FROM @TempButton WHERE StoreId = @p_StoreId )
)
ORDER BY
PageNumber ,KeyNumber

---- SELECT
---- SUBSTRING(RowIdentifierValue,1,CHARINDEX('~',RowIdentifierValue)-1) AS PageNumber,
---- SUBSTRING(RowIdentifierValue,CHARINDEX('~',RowIdentifierValue)+1 , LEN(RowIdentifierValue) - CHARINDEX('~',RowIdentifierValue)) AS KeyNumber
---- FROM
---- DataChangesDetail D
---- JOIN
---- DataChangesHeader H
---- ON
---- H.DataChangesId = D.DataChangesId
---- WHERE
---- ModuleNumber = 451
---- AND IsApplied = 0
---- AND StoreId = @p_StoreId
---- AND (
---- SUBSTRING(RowIdentifierValue,1,CHARINDEX('~',RowIdentifierValue)-1) NOT IN (
---- SELECT PageNumber FROM @TempButton WHERE StoreId = @p_StoreId)
---- OR
---- SUBSTRING(RowIdentifierValue,1,CHARINDEX('~',RowIdentifierValue)-1) IN (
---- SELECT PageNumber FROM @TempButton WHERE StoreId = @p_StoreId) AND
---- SUBSTRING(RowIdentifierValue,CHARINDEX('~',RowIdentifierValue)+1 , LEN(RowIdentifierValue) - CHARINDEX('~',RowIdentifierValue)) NOT IN (
---- SELECT KeyNumber FROM @TempButton WHERE StoreId = @p_StoreId)
---- )
END

RETURN
ENDPGM:
RAISERROR ('Procedure call failed , Error Source ''%s'',Error State ''%d''',
16, @l_ErrState , @l_ProcName, @l_ErrState )
RETURN -@l_ErrCode

END TRY

BEGIN CATCH
SELECT ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END

---###########################################################

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-11 : 09:08:19

Great, thanks for posting.

- Jeff
http://weblogs.sqlteam.com/JeffS