CREATE FUNCTION [dbo].[myApp_CreateIdTable]
	(
	@Ids nvarchar(max),
	@separator char(1)
	)
RETURNS @tbl_Ids TABLE
	(
	IdVal int NOT NULL
	)
AS
	BEGIN
		-- check @Ids string ends with separator character
		IF NOT RIGHT(@Ids, 1) = @separator
			BEGIN
				SET @Ids = @Ids + @separator
			END
		DECLARE @separator_position INT
		DECLARE @array_value nvarchar(10)
		WHILE PATINDEX('%' + @separator + '%' , @Ids) <> 0
			BEGIN -- feed array of integers into tempTable
				SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @Ids)
				SELECT @array_value = LEFT(@Ids, @separator_position - 1)
				INSERT INTO @tbl_Ids (IdVal) VALUES (@array_value)
				SELECT @Ids = STUFF(@Ids, 1, @separator_position, '')
			END
		RETURN
	END

-- create and populate sample table
CREATE TABLE #a(i INT NOT NULL, alpha AS CHAR(64+i))
;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<100)
INSERT #a SELECT n FROM n

-- set up variable (parameter) and append ID values as binary(4)
DECLARE @param VARBINARY(MAX)=0x;
SET @param=@param + CAST(4 AS BINARY(4))
SET @param=@param + CAST(23 AS BINARY(4))
SET @param=@param + CAST(19 AS BINARY(4))
SET @param=@param + CAST(47 AS BINARY(4))
SET @param=@param + CAST(87 AS BINARY(4))

-- parse fixed-length binary values and convert to int, then join to table
;WITH	n(n) AS (SELECT 1 UNION ALL SELECT n+4 FROM n WHERE n<DATALENGTH(@param)-4),
	i(i) AS (SELECT CAST(SUBSTRING(@param,n,4) AS INT) FROM n)
SELECT a.* FROM i 
INNER JOIN #a a ON i.i=a.i

quote:

---

Your method looks interesting, but surely getting the application to convert all the records to a byte array would surely outweigh the speed gains from the function you provided, right??

---

quote:

---

I will certainly modify my procedure to use the 'ALL' technique, but unfortunately I then end up heading down a catch-all query path, which I really wanted to avoid (as ALL would require a CustomerID lookup instead of userID lookup, and I don't fancy duplicating this query for all combinations of parameter values...).

Unless of course there's a better way...

---

quote:

---

Originally posted by R

His example seems limited to varchar(8000) and my T-SQL isn't hot enough to expand on it (other than the guess I made above)...

---