Convert String to a Table using CTE
I have found many solutions to pass a list parameter from ColdFusion to a stored procedure,
and this one is a very simple and efficient way to convert a string to a table. Thanks to Amit Gaur posting to SQL ServerCentral.
I modified his original function which returns only integers (forcing you to pass only a list of integers).
Suppose you pass the string ’2A354,FB452,8896′ in the argument @itemList as a list of item codes you want to retrieve from your inventory table.
In your stored procedure you would have this query:
1 2 3
SELECT item, description, price FROM inventory WHERE item IN (SELECT item FROM strToTable(@itemList))
Here is the function strToTable():
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Amit Gaur -- Create date: July 25th 2008 -- Description: Convert a string to a table -- ============================================= CREATE FUNCTION [dbo].[strToTable] ( @array varchar(max), @del char(1) ) RETURNS @listTable TABLE ( item int ) AS BEGIN WITH rep (item,list) AS ( SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item, SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list UNION ALL SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item, SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list FROM rep WHERE LEN(rep.list) > 0 ) INSERT INTO @listTable SELECT item FROM rep RETURN END GO
jQuery_Mobile First Look
hehe
blogged about the same few time ago here: http://blog.1smartsolution.com//index.cfm/action:posts.entry/id:233/Using-CTE-To-Transform-A-List-Of-IDs-To-A-Table