Bookmark and Share

Wednesday, February 15, 2006

SQL2000: gestire input variabile su Stored Procedure

Ecco una bellissima funzione fornita dal mio amico Gibyx:
Data una stringa contenente un numero variabile di valori, es:
"1;2;3;4;5;6;" oppure "AD;ER;FG;GG;HH;JJ"
restituisce una tabella che può essere utilizzata in sub query /join/etc..

[+/-] CREATE FUNCTION dbo.SplitIntList....



CREATE FUNCTION dbo.SplitIntList
(
@PropertyIDValues NVARCHAR(1024)
)
RETURNS @ResultTable TABLE(IDValue int)
AS
BEGIN
DECLARE @SingleColumn NVARCHAR(12)
DECLARE @SplitChar CHAR(1)
DECLARE @LENInputString INT
DECLARE @x INT
DECLARE @y INT

SET @SplitChar = ';'
IF (RIGHT(@PropertyIDValues, 1) <> @SplitChar)
BEGIN
SET @PropertyIDValues = @PropertyIDValues + @SplitChar
END
SET @LENInputString = LEN(@PropertyIDValues)
SET @x = 0
WHILE (@x < @LENInputString) BEGIN SET @SingleColumn = SUBSTRING(@PropertyIDValues, @x, 1) IF ((@SingleColumn <> @SplitChar) AND (@PropertyIDValues <> ' '))
BEGIN
SET @y = @x + 1
WHILE ((@y < @LENInputString + 1) AND (SUBSTRING(@PropertyIDValues, @y, 1) <> @SplitChar))
BEGIN
SET @SingleColumn = @SingleColumn + SUBSTRING(@PropertyIDValues, @y, 1)
SET @y = @y + 1
END
SET @x = @y
END
IF ((@SingleColumn <> @SplitChar) AND (@PropertyIDValues <> ' '))
BEGIN
SET @SingleColumn = LTRIM(RTRIM(@SingleColumn))
INSERT INTO @ResultTable (IDValue) VALUES (CAST(@SingleColumn AS INT))
END
SET @x = @x + 1
END
RETURN
END

ESEMPI:
select * from splitIntList('1;10;11;12;13;') where idvalue >10
IDValue
-----------
11
12
13
(3 row(s) affected)

select * from splitStringList('GP;HB;TE;TC;MX;') where idvalue like 'G%'
IDValue
----------
GP
(1 row(s) affected)