storeproc is not inserting any data!

Question

Post reply

storeproc is not inserting any data!

ansu_411

SSC Rookie

Points: 33
More actions
January 22, 2010 at 6:42 pm

#137602

CREATE PROCEDURE SavedItem]
@MemberIDint
,@SavedItemTypeIDsmallint = NULL
,@Descriptionvarchar(200) = NULL
,@ChannelIDsmallint
,@SavedItemValuexml
,@debugbit = 0
,@SavedItemIDint = NULL OUTPUT
AS
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET QUOTED_IDENTIFIER ON
BEGIN
IF @debug = 1
BEGIN
SELECTOBJECT_NAME(@@PROCID) + 'Starting at ' + CAST(GETDATE() AS varchar)
PRINT'Passed Parameters'
SELECT@MemberID'@MemberID'
,@SavedItemTypeID'@SavedItemTypeID'
,@Description'@Description'
,@ChannelID'@ChannelID'
,@SavedItemValue'@SavedItemValue'
,@SavedItemID'@SavedItemID'
END
-- Local Variable Declarations
DECLARE@TranStartedbit
,@errmsgvarchar(8000)
,@rcint
,@SavedItemCheckSumBIGINT
,@AlertFrequency INT
,@AlertFrequencyDesc VARCHAR(100)
,@StatusID INT
DECLARE@tmpSavedItemID TABLE
(
SavedItemIDintNOT NULL
)
DECLARE@tmpSAVED_ITEM_ATTRIBUTE TABLE
(
SavedItemIDintNULL
,AttributeTypeIDsmallintNOT NULL
,SeqNumtinyintDEFAULT(0)
,AttributeValuevarchar(4000)NULL
,updflgtinyintDEFAULT(0)
)
-- Stored Procedure code.
BEGIN TRY
/* This caused a problem for people with no saved items */
--IF NOT EXISTS(SELECT1
--FROMdbo.SAVED_ITEM
--WHERESavedItemID = @SavedItemID
--ANDMemberID = @MemberID
--) AND@SavedItemID IS NOT NULL
--BEGIN
--SET@rc = -4
--
--RAISERROR('Member does not own saved item', 11, 1)
--END
SELECT@SavedItemTypeID = @SavedItemValue.value('(SavedItem/@type)[1]', 'smallint')
WHERE@SavedItemTypeID IS NULL
IF (SELECT COUNT(*) FROM dbo.SAVED_ITEM WHERE MemberID = @MemberID AND SavedItemTypeID = @SavedItemTypeID AND @SavedItemID IS NULL) >= 100
BEGIN
SET@rc = -1
RAISERROR ('SavedItemType exceeded maximum quantity (100)', 22, 1)
END
SELECT@Description = @SavedItemValue.value('(SavedItem/Description)[1]', 'varchar(300)')
WHERE@Description IS NULL
IF EXISTS
(
SELECT
1
FROM SAVED_ITEM (NOLOCK)
WHERE MemberID = @MemberID
AND @SavedItemTypeID IN (7, 10, 17, 20, 21)
AND SavedItemTypeID = @SavedItemTypeID
AND Description = @Description
AND @SavedItemID IS NULL
)
BEGIN
SET@rc = -3
RAISERROR ('SavedItem already exists for this Member with this SavedItemTypeID and Description', 11, 1)
END
IF @debug = 1
BEGIN
PRINT'After correcting null saved item type and description'
SELECT@SavedItemTypeID'@SavedItemTypeID'
,@Description'@Description'
END
/* Get Alert Frequency and Status*/
SELECT @AlertFrequencyDesc = @SavedItemValue.value('(SavedItem/AlertFrequency)[1]', 'varchar(100)')
,@AlertFrequency = @SavedItemValue.value('(SavedItem/AlertFrequency/@id)[1]', 'INT')
,@StatusID = @SavedItemValue.value('(SavedItem/@status)[1]', 'INT')
--Match Optin and Alerts for Listing Alerts.
SELECT @AlertFrequency =
CASEWHEN OptionID = 20 THEN 2
WHEN OptionID = 21 THEN 4
ELSE 1
END
FROM OPTIN o
WHERE o.MemberID = @MemberID
AND o.OptionID IN (20,21)
AND o.EndDate IS NULL
AND @SavedItemTypeID = 11
SELECT @AlertFrequency = 1
WHERE @AlertFrequency IS NULL AND @AlertFrequencyDesc IS NULL
SELECT
@AlertFrequency = zft.FrequencyTypeID
FROM zFREQUENCY_TYPE zft
WHERE @AlertFrequency IS NULL
ANDFrequencyTypeDesc = @AlertFrequencyDesc
/*Common Attributes*/
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,AttributeValue
)
SELECTSavedItemID
,CAST(AttributeTypeID AS smallint)
,AttributeValue
FROM(
SELECT@SavedItemID AS SavedItemID
,@SavedItemValue.value('(SavedItem/Note)[1]', 'varchar(4000)') AS [43]
,@SavedItemValue.value('(SavedItem/URL)[1]', 'varchar(4000)') AS [28]
) AS p
UNPIVOT
(
AttributeValue FOR AttributeTypeID IN
(
[43]
,[28]
)
) AS up
--Attributes Node
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,AttributeValue
)
SELECT
@SavedItemID
,t.c.value('(./@id)[1]', 'int')
,t.c.value('(./text())[1]', 'varchar(4000)')
FROM @SavedItemValue.nodes('SavedItem/Attributes/Attribute') t(c)
IF @debug = 1
BEGIN
PRINT'After RDC Attributes Node data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
END
/* Saved PropertyListing SavedItemTypeID = 11 */
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,AttributeValue
)
SELECTSavedItemID
,CAST(AttributeTypeID AS smallint)
,AttributeValue
FROM(
SELECT@SavedItemID AS SavedItemID
,@SavedItemValue.value('(SavedItem/PropertyListing/@id)[1]', 'varchar(4000)') AS [12]
,@SavedItemValue.value('(SavedItem/PropertyListing/@type)[1]', 'varchar(4000)') AS [13]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertyListing/@gateway)[1]', 'varchar(4000)'), '') AS [15]
,@SavedItemValue.value('(SavedItem/PropertyListing/@city)[1]', 'varchar(4000)') AS [14]
WHERE@SavedItemTypeID = 11
) AS p
UNPIVOT
(
AttributeValue FOR AttributeTypeID IN
(
[12]
,[13]
,[14]
,[15]
)
) AS up
IF @debug = 1
BEGIN
PRINT'After RDC saved Listing data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
END
/* SavedItemTypeID = 15,16 */
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,AttributeValue
)
SELECTSavedItemID
,CAST(AttributeTypeID AS smallint)
,AttributeValue
FROM(
SELECT@SavedItemID AS SavedItemID
,@SavedItemValue.value('(SavedItem/Entity/@id)[1]', 'varchar(4000)') AS [41]
,@SavedItemValue.value('(SavedItem/Entity/@type)[1]', 'varchar(4000)') AS [42]
,NULLIF(@SavedItemValue.value('(SavedItem/Entity/@gateway)[1]', 'varchar(4000)'), '') AS [15]
--,@SavedItemValue.value('(SavedItem/PropertyListing/@city)[1]', 'varchar(4000)') AS [14]
WHERE@SavedItemTypeID in (8,9,15,16,22)
) AS p
UNPIVOT
(
AttributeValue FOR AttributeTypeID IN
(
[41]
,[42]
,[15]
)
) AS up
IF @debug = 1
BEGIN
PRINT'After saved Entity data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
END
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,AttributeValue
)
SELECTSavedItemID
,CAST(AttributeTypeID AS smallint) AS AttributeTypeID
,AttributeValue
FROM(
SELECT@SavedItemID AS SavedItemID
,@SavedItemValue.value('(SavedItem/PropertySearch/@type)[1]'
, 'varchar(4000)') AS [13]
,@SavedItemValue.value('(SavedItem/PropertySearch/@rentals)[1]'
, 'varchar(4000)') AS [38]
,@SavedItemValue.value('(SavedItem/SearchMethod/@type)[1]'
, 'varchar(4000)') AS [24]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/@gateway)[1]'
, 'varchar(4000)'), '') AS [15]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/Beds)[1]'
, 'varchar(4000)'), '') AS [25]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/Baths)[1]'
, 'varchar(4000)'), '') AS [26]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/SqFt)[1]'
, 'varchar(4000)'), '') AS [27]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/MinPrice)[1]'
, 'varchar(4000)'), '') AS [18]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/MaxPrice)[1]'
, 'varchar(4000)'), '') AS [19]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/SortBy)[1]'
, 'varchar(4000)'), '') AS [20]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/SortDirection)[1]'
, 'varchar(4000)'), '') AS [39]
,NULLIF(@SavedItemValue.value('(SavedItem/PropertySearch/Foreclosure)[1]'
, 'varchar(4000)'), '') AS [52]
WHERE@SavedItemTypeID IN (7, 10, 17, 20, 21)
) AS p
UNPIVOT
(
AttributeValue FOR AttributeTypeID IN
(
[13]
,[38]
,[15]
,[18]
,[19]
,[20]
,[24]
,[25]
,[26]
,[27]
,[39]
)
) AS up
IF @debug = 1
BEGIN
PRINT'After RDC main criteria data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
SELECT@SavedItemID
,CAST(AttributeType AS smallint) AS AttributeTypeID
,RowNum AS SeqNum
,AttributeValue
FROM(
SELECTROW_NUMBER() OVER(ORDER BY [29] DESC) AS RowNum
,[23]
,[34]
,[29]
,[50]
,[51]
,[48]
FROM(
SELECT
t.c.value('(./@id)[1]', 'varchar(4000)') AS [23]
,t.c.value('(.)[1]', 'varchar(4000)') AS [34]
,t.c.value('(./@type)[1]', 'varchar(4000)') AS [29]
,t.c.value('(./@lat)[1]', 'varchar(4000)') AS [50]
,t.c.value('(./@long)[1]', 'varchar(4000)') AS [51]
,t.c.value('(./@radius)[1]', 'varchar(4000)') AS [48]
FROM@SavedItemValue.nodes('SavedItem/SearchMethod/parameter') t(c)
) AS parms
) p
UNPIVOT (AttributeValue FOR AttributeType IN
([23], [34], [29], [50], [51], [48])
) AS up
END
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
)
SELECT@SavedItemID
,CAST(AttributeType AS smallint) AS AttributeTypeID
,RowNum AS SeqNum
,AttributeValue
FROM(
SELECTROW_NUMBER() OVER(ORDER BY [29] DESC) AS RowNum
,[23]
,[34]
,[29]
,[50]
,[51]
,[48]
FROM(
SELECT
t.c.value('(./@id)[1]', 'varchar(4000)') AS [23]
,t.c.value('(.)[1]', 'varchar(4000)') AS [34]
,t.c.value('(./@type)[1]', 'varchar(4000)') AS [29]
,t.c.value('(./@lat)[1]', 'varchar(4000)') AS [50]
,t.c.value('(./@long)[1]', 'varchar(4000)') AS [51]
,t.c.value('(./@radius)[1]', 'varchar(4000)') AS [48]
FROM@SavedItemValue.nodes('SavedItem/SearchMethod/parameter') t(c)
) AS parms
) p
UNPIVOT (AttributeValue FOR AttributeType IN
([23], [34], [29], [50], [51], [48])
) AS up
IF @debug = 1 AND @SavedItemTypeID = 10
BEGIN
PRINT'After RDC search parameter data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
END
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
)
SELECT@SavedItemID
,CAST(AttributeTypeID AS smallint) AS AttributeTypeID
,SeqNum
,AttributeValue
FROM(
SELECTROW_NUMBER() OVER(ORDER BY [30], [31]) AS SeqNum
,[30]
,[31]
,[32]
FROM(
SELECTt.c.value('(./@type)[1]', 'varchar(4000)') AS [30]
,t.c.value('(./@id)[1]', 'varchar(4000)') AS [31]
,t.c.value('(.)[1]', 'varchar(4000)') AS [32]
FROM@SavedItemValue.nodes('SavedItem/PropertySearch/Feature') AS t(c)
) AS r
) AS p
UNPIVOT (
AttributeValue FOR AttributeTypeID IN
(
[30]
,[31]
,[32]
)
) AS up
IF @debug = 1 AND @SavedItemTypeID = 10
BEGIN
PRINT'After RDC search feature data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
END
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,AttributeValue
)
SELECTSavedItemID
,CAST(AttributeTypeID AS smallint)
,AttributeValue
FROM(
SELECT@SavedItemID AS SavedItemID
,@SavedItemValue.value('(SavedItem/AgentSearch/Agent/@id)[1]', 'varchar(4000)') AS [35]
,@SavedItemValue.value('(SavedItem/AgentSearch/@gateway)[1]', 'varchar(4000)') AS [15]
WHERE@SavedItemTypeID IN (7, 10, 17, 20, 21)
) AS p
UNPIVOT
(
AttributeValue FOR AttributeTypeID IN
(
[35],[15]
)
) AS up
IF @debug = 1
BEGIN
PRINT'After RDC saved Agent Search data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
END
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,AttributeValue
)
SELECTSavedItemID
,CAST(AttributeTypeID AS smallint)
,AttributeValue
FROM(
SELECT@SavedItemID AS SavedItemID
,@SavedItemValue.value('(SavedItem/OfficeSearch/Office/@id)[1]', 'varchar(4000)') AS [36]
,@SavedItemValue.value('(SavedItem/OfficeSearch/@gateway)[1]', 'varchar(4000)') AS [15]
WHERE@SavedItemTypeID IN (7, 10, 17, 20, 21)
) AS p
UNPIVOT
(
AttributeValue FOR AttributeTypeID IN
(
[36],[15]
)
) AS up
IF @debug = 1
BEGIN
PRINT'After RDC saved Office Search data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
END
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,AttributeValue
)
SELECTSavedItemID
,CAST(AttributeTypeID AS smallint)
,AttributeValue
FROM(
SELECT@SavedItemID AS SavedItemID
,@SavedItemValue.value('(SavedItem/MLSSearch/MLSListingSearch/@id)[1]', 'varchar(4000)') AS [37]
,@SavedItemValue.value('(SavedItem/MLSSearch/@gateway)[1]', 'varchar(4000)') AS [15]
WHERE@SavedItemTypeID IN (7, 10, 17, 20, 21)
) AS p
UNPIVOT
(
AttributeValue FOR AttributeTypeID IN
(
[37],[15]
)
) AS up
IF @debug = 1
BEGIN
PRINT'After RDC saved MLS ListingID Search data collection'
SELECTSavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
,updflg
FROM@tmpSAVED_ITEM_ATTRIBUTE
END
SELECT@SavedItemCheckSum = SUM(CAST(CHECKSUM(t.AttributeValue) AS bigint))
FROM@tmpSAVED_ITEM_ATTRIBUTE t
WHERE t.AttributeTypeID != 28
IF @debug = 1
BEGIN
SELECT@SavedItemCheckSum'@SavedItemCheckSum'
END
IF EXISTS(SELECT1
FROMdbo.SAVED_ITEM AS si
JOINdbo.SAVED_ITEM_ATTRIBUTE AS sia
ONsi.SavedItemID = sia.SavedItemID
WHEREsi.MemberID = @MemberID
ANDsia.AttributeTypeID = 33 and Si.statusId=1
ANDCAST(sia.AttributeValue AS bigint) = @SavedItemCheckSum
AND si.SavedItemTypeID = @SavedItemTypeID)
AND @SavedItemID IS NULL
BEGIN
SET@rc = -2
RAISERROR ('Duplicate SavedItem exists', 11, 1)
END
IF EXISTS(SELECT1
FROM@tmpSAVED_ITEM_ATTRIBUTE AS tsi
LEFT JOINdbo.zATTRIBUTE_TYPE AS zat
ONtsi.AttributeTypeID = zat.AttributeTypeID
WHEREzat.AttributeTypeID IS NULL
)
BEGIN
SET@rc = -4
RAISERROR ('Invalid AttributeTypeID specified', 11, 1)
END
INSERT INTO @tmpSAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
)
VALUES
(
@SavedItemID
,33
,0
,@SavedItemCheckSum
)
UPDATEta
SETupdflg = CASE WHEN ta.AttributeValue = a.AttributeValue THEN 2 ELSE 1 END
FROM@tmpSAVED_ITEM_ATTRIBUTE AS ta
JOINdbo.SAVED_ITEM_ATTRIBUTE AS a
ONta.SavedItemID = a.SavedItemID
ANDta.AttributeTypeID = a.AttributeTypeID
ANDta.SeqNum = a.SeqNum
IF @debug = 1
BEGIN
SELECT '@SavedItemCheckSum right before update'
SELECT* FROM @tmpSAVED_ITEM_ATTRIBUTE
SELECT *
FROM@tmpSAVED_ITEM_ATTRIBUTE AS ta
JOINdbo.SAVED_ITEM_ATTRIBUTE AS a
ONta.SavedItemID = a.SavedItemID
ANDta.AttributeTypeID = a.AttributeTypeID
ANDta.SeqNum = a.SeqNum
WHEREupdflg = 1
END
-- Check for open transactions. If none open open a explicit transaction.
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION spXPRF_SavedItem_mod
SET@TranStarted = 1
END
ELSE
BEGIN
SET@TranStarted = 0
END
UPDATEdbo.SAVED_ITEM
SETSavedItemTypeID = @SavedItemTypeID
,ChannelID = @ChannelID
,MemberID = @MemberID
,Description = @Description
,StatusID = @StatusID
,UpdateDate = GETDATE()
,AlertFrequency = @AlertFrequency
WHERESavedItemID = @SavedItemID
INSERT INTO dbo.SAVED_ITEM
(
SavedItemTypeID
,ChannelID
,MemberID
,Description
,SavedItemXML
,CreateDate
,UpdateDate
,StatusID
,AlertFrequency
)
OUTPUTINSERTED.SavedItemID INTO @tmpSavedItemID
SELECT@SavedItemTypeID
,@ChannelID
,@MemberID
,@Description
,'<SavedItem></SavedItem>'
,GETDATE()
,GETDATE()
,ISNULL(@StatusID,1)
,@AlertFrequency
WHERE@SavedItemID IS NULL
SELECTTOP 1 @SavedItemID = SavedItemID
FROM@tmpSavedItemID
WHERE@SavedItemID IS NULL
IF @debug = 1
BEGIN
SELECT@SavedItemID '@SavedItemID'
END
DELETEa
FROMdbo.SAVED_ITEM_ATTRIBUTE AS a
LEFT JOIN @tmpSAVED_ITEM_ATTRIBUTE AS ta
ONta.SavedItemID = a.SavedItemID
ANDta.AttributeTypeID = a.AttributeTypeID
ANDta.SeqNum = a.SeqNum
WHEREa.SavedItemID = @SavedItemID
ANDta.SavedItemID IS NULL
UPDATEa
SETAttributeValue = ta.AttributeValue
FROM@tmpSAVED_ITEM_ATTRIBUTE AS ta
JOINdbo.SAVED_ITEM_ATTRIBUTE AS a
ONta.SavedItemID = a.SavedItemID
ANDta.AttributeTypeID = a.AttributeTypeID
ANDta.SeqNum = a.SeqNum
WHEREupdflg = 1
INSERT INTO dbo.SAVED_ITEM_ATTRIBUTE
(
SavedItemID
,AttributeTypeID
,SeqNum
,AttributeValue
)
SELECTISNULL(SavedItemID,@SavedItemID)
,AttributeTypeID
,SeqNum
,AttributeValue
FROM@tmpSAVED_ITEM_ATTRIBUTE
WHEREupdflg = 0
ANDAttributeValue IS NOT NULL
IF @debug = 1
BEGIN
SELECT@MemberID'@MemberID'
,@SavedItemTypeID'@SavedItemTypeID'
,@Description'@Description'
,@ChannelID'@ChannelID'
,@SavedItemValue'@SavedItemValue'
,@SavedItemID'@SavedItemID'
END
-- Success Return Code
IF @TranStarted = 1
BEGIN
COMMIT TRANSACTION spXPRF_SavedItem_mod
END
IF @debug = 1
BEGIN
SELECTOBJECT_NAME(@@PROCID) + 'Ending at ' + CAST(GETDATE() AS varchar) + ' Success'
END
RETURN 0 -- Success Return
END TRY
-- Failure Return Code
BEGIN CATCH
IF @TranStarted = 1
BEGIN
ROLLBACK TRANSACTION spXPRF_SavedItem_mod
END
IF @debug = 1
BEGIN
SELECTOBJECT_NAME(@@PROCID) + 'Ending at ' + CAST(GETDATE() AS varchar) + ' Failure'
END
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
RETURN @rc -- Failure Return
END CATCH
END
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Have you tried appending Print statements at various points of the proc to determine where the proc might be failing?

is the proc returning an error code?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

That is almost 800 lines of code. Do you really expect people to debug that volume of code without access to the DB or any of the tables references, or even a clear description of what's wrong?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass