OPENXML Error - 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)

OPENXML Error

Author

Topic

superfly1405
Starting Member

2 Posts

Posted - 2007-08-29 : 13:09:35

Hello,
here is my stored proc. and I get the error of
Msg 8179, Level 16, State 5, Procedure usp_GlobalTest, Line 34
Could not find prepared statement with handle 0.

Not Sure what is wrong. I have done this all day and am still unable to figure it out.

***************************

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_GlobalTest]
@DivisionCD varchar(15) = NULL,
@NetworkCD varchar(15) = NULL,
@SellingTitleIdsXML varchar(8000) = NULL ,
@MeasurementPeriodIdsXML varchar(8000) = NULL,
@InventoryTypeIdsXML varchar(8000) = NULL,
@ReserveIdsXML varchar(8000) = NULL
AS
BEGIN
BEGIN TRANSACTION

SET NOCOUNT ON;

DECLARE @sellingTitleIdsXML_doc int
DECLARE @measurementPeriodIdsXML_doc int
DECLARE @inventoryTypeIdsXML_doc int

IF @SellingTitleIdsXML IS NOT NULL
EXEC sp_xml_preparedocument @sellingTitleIdsXML_doc OUTPUT, @SellingTitleIdsXML

IF @MeasurementPeriodIdsXML IS NOT NULL
EXEC sp_xml_preparedocument @measurementPeriodIdsXML_doc OUTPUT, @MeasurementPeriodIdsXML

IF @InventoryTypeIdsXML IS NOT NULL
EXEC sp_xml_preparedocument @inventoryTypeIdsXML_doc OUTPUT, @InventoryTypeIdsXML

SELECT DISTINCT a.ReserveId, a.ReserveName,b.SellingTitleId,b.InventoryTypeId,c.InventoryTypeName, 'InventoryParentType' = 'SellingTitle'
FROM tblReserve a INNER JOIN tblInventoryReserve b ON (a.ReserveId = b.ReserveId)
INNER JOIN tblInventoryType c ON (b.InventoryTypeId = c.InventoryTypeId) INNER JOIN tblSellingTitle d ON b.SellingTitleId = d.SellingTitleId
WHERE
(d.DivisionCD = @DivisionCD OR @DivisionCD IS NULL)
AND
(d.NetworkCD = @NetworkCD OR @NetworkCD IS NULL)
AND
(b.SellingTitleId IN
(
SELECT [SearchValue] FROM OPENXML (@sellingTitleIdsXML_doc, '/root/SearchValues', 1) WITH ([SearchValue] int)
) OR @sellingTitleIdsXML_doc <=0) ---OR @SellingTitleIdsXML IS NULL)
---OR @SellingTitleIdsXML IS NULL)
AND (b.MeasurementPeriodId IN
(
SELECT [SearchValue] FROM OPENXML (@measurementPeriodIdsXML_doc, '/root/SearchValues', 1)
WITH ([SearchValue] int)
) OR @measurementPeriodIdsXML_doc <=0) --OR @MeasurementPeriodIdsXML IS NULL)
AND --(a.InventoryTypeID = @InventoryTypeId or @InventoryTypeId IS NULL)
(b.InventoryTypeID IN
(
SELECT [SearchValue] FROM OPENXML (@inventoryTypeIdsXML_doc, '/root/SearchValues', 1)
WITH ([SearchValue] int)
) OR @InventoryTypeIdsXML IS NULL)
/*AND
(a.ReserveId IN
(
SELECT [SearchValue] FROM OPENXML (@reserveIdsXML_doc, '/root/SearchValues', 1)
WITH ([SearchValue] int)
) OR @ReserveIdsXML IS NULL)*/
ORDER By b.SellingTitleId

IF @SellingTitleIDsXML IS NOT NULL
EXEC sp_xml_removedocument @sellingTitleIdsXML_doc

IF @MeasurementPeriodIdsXML IS NOT NULL
EXEC sp_xml_removedocument @measurementPeriodIdsXML_doc

IF @InventoryTypeIdsXML IS NOT NULL
EXEC sp_xml_removedocument @inventoryTypeIdsXML_doc

/*IF @ReserveIdsXML IS NOT NULL
EXEC sp_xml_removedocument @reserveIdsXML_doc
*/

COMMIT TRANSACTION
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 13:30:48

At least one of the XML parameters is NULL. Use temporary tables to avoid this when checking later in the WHERE.

ALTER PROCEDURE [dbo].[usp_GlobalTest]
(
	@DivisionCD varchar(15) = NULL,
	@NetworkCD varchar(15) = NULL,
	@SellingTitleIdsXML varchar(8000) = NULL , 
	@MeasurementPeriodIdsXML varchar(8000) = NULL,
	@InventoryTypeIdsXML varchar(8000) = NULL,
	@ReserveIdsXML varchar(8000) = NULL
)
AS

SET NOCOUNT ON

BEGIN TRANSACTION

DECLARE @Handle INT

DECLARE	@Selling TABLE (SearchValue INT)
DECLARE	@Measurement TABLE (SearchValue INT)
DECLARE	@Inventory TABLE (SearchValue INT)

IF @SellingTitleIdsXML IS NOT NULL
	BEGIN
		EXEC sp_xml_preparedocument @Handle OUTPUT, @SellingTitleIdsXML

		INSERT	@Selling		
		SELECT	[SearchValue]
		FROM	OPENXML(@Handle, '/root/SearchValues', 1)
		WITH	(
				[SearchValue] int
			)
		
		EXEC sp_xml_removedocument @Handle
	END

IF @MeasurementPeriodIdsXML IS NOT NULL
	BEGIN
		EXEC sp_xml_preparedocument @Handle OUTPUT, @MeasurementPeriodIdsXML 

		INSERT	@Measurement
		SELECT	[SearchValue]
		FROM	OPENXML(@Handle, '/root/SearchValues', 1)
		WITH	(
				[SearchValue] int
			)

		EXEC sp_xml_removedocument @Handle
	END

IF @InventoryTypeIdsXML IS NOT NULL
	BEGIN
		EXEC sp_xml_preparedocument @Handle OUTPUT, @InventoryTypeIdsXML 

		INSERT	@Inventory
		SELECT	[SearchValue]
		FROM	OPENXML(@Handle, '/root/SearchValues', 1)
		WITH	(
				[SearchValue] int
			)

		EXEC sp_xml_removedocument @Handle
	END

SELECT DISTINCT	a.ReserveId,
		a.ReserveName,
		b.SellingTitleId,
		b.InventoryTypeId,
		c.InventoryTypeName,
		'SellingTitle' AS InventoryParentType
FROM		tblReserve AS a
INNER JOIN	tblInventoryReserve AS b ON b.ReserveId = a.ReserveId
INNER JOIN	tblInventoryType AS c ON c.InventoryTypeId = b.InventoryTypeId
INNER JOIN	tblSellingTitle AS d ON d.SellingTitleId = b.SellingTitleId
WHERE		COALESCE(@DivisionCD, d.DivisionCD) = d.DivisionCD 
		AND COALESCE(@NetworkCD, d.NetworkCD) = d.NetworkCD
		AND	(
				(
					EXISTS (SELECT * FROM @Selling AS s WHERE s.SearchValue = b.SellingTitleId)
					OR @SellingTitleIdsXML IS NULL
				)
				OR
				(
					EXISTS (SELECT * FROM @Measurement AS m WHERE m.SearchValue = b.MeasurementPeriodId)
					OR @MeasurementPeriodIdsXML IS NULL
				)
				OR
				(
					EXISTS (SELECT * FROM @Inventory AS i WHERE i.SearchValue = b.InventoryTypeID)
					OR @InventoryTypeIdsXML IS NULL
				)
			)
ORDER By	b.SellingTitleId

COMMIT TRANSACTION

E 12°55'05.25"
N 56°04'39.16"

superfly1405
Starting Member

2 Posts

Posted - 2007-08-30 : 09:43:31

Works like a charm. Thanks very mucho!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 09:48:37

I think the WHERE part should look like

WHERE		COALESCE(@DivisionCD, d.DivisionCD) = d.DivisionCD 
		AND COALESCE(@NetworkCD, d.NetworkCD) = d.NetworkCD
		AND	(
				EXISTS (SELECT * FROM @Selling AS s WHERE s.SearchValue = b.SellingTitleId)
				OR @SellingTitleIdsXML IS NULL
			)
		AND
			(
				EXISTS (SELECT * FROM @Measurement AS m WHERE m.SearchValue = b.MeasurementPeriodId)
				OR @MeasurementPeriodIdsXML IS NULL
			)
		AND
			(
				EXISTS (SELECT * FROM @Inventory AS i WHERE i.SearchValue = b.InventoryTypeID)
				OR @InventoryTypeIdsXML IS NULL
			)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources