Forums

Hi

I have a stored procedure (given below) :

CREATE PROC dbo.GetOrderList3
(
	@OrderList varchar(500)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TempList table
	(
		OrderID int
	)

	DECLARE @OrderID varchar(10), @Pos int

	SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
	SET @Pos = CHARINDEX(',', @OrderList, 1)

	IF REPLACE(@OrderList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
			IF @OrderID <> ''
			BEGIN
				INSERT INTO @TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
			END
			SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
			SET @Pos = CHARINDEX(',', @OrderList, 1)

		END
	END	

	SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
	FROM 	dbo.Orders AS o
		JOIN 
		@TempList t
		ON o.OrderID = t.OrderID
		
END
GO

GRANT EXEC ON dbo.GetOrderList3 TO WebUser

GO

I cant seem to get it to work correctly within the install script. I guess I cant get the escape characters for ' right.

If I run the script using the Host -> SQL page, I can get it to work correctly.

I tried reading up about escape sequence in SQL server and doing accordingly but still no joy.

Any help greatly appreciated

Thanks

If you are having issues with the script running via the install package but it works under the SQL you want to re-save your SqlDataProvider file to use Unicode....once you do that it will work!!