SQL: dbo.parseString() - varchar() to table variable

[toha]

[kod]SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



ALTER FUNCTION parseString
(
@sIds AS VARCHAR(8000)
)
RETURNS @Ret TABLE(IDs INT)
AS
BEGIN

DECLARE @FirstPos INT
DECLARE @FirstPos1 INT
DECLARE @SecondPos INT

IF ( datalength( @sIds ) > 0 )
BEGIN
SET @FirstPos = 1

SELECT @SecondPos = charinde',', @sIds, @FirstPos)

WHILE ( @SecondPos > 0 )
BEGIN
INSERT INTO @Ret
VALUES( cast( substring( @sIds, @FirstPos, @SecondPos - @FirstPos ) AS INT) )

SET @FirstPos = @SecondPos + 1
SET @SecondPos = charinde ',', @sIds, @FirstPos )
END

INSERT INTO @Ret
VALUES( cast( right( @sIds, datalength( @sIds ) - @FirstPos + 1 ) AS INT ) )
END

RETURN
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[/kod]

[Travoni]

Trixade till denna så att den skulle passa mySql. Därmot så kan inte funktioner i mysql levereras i table format så man får anropa proceduren "explicit".

PROCEDURE `parseString`(sIds varchar(1000))
BEGIN 
DECLARE FirstPos INT;
DECLARE FirstPos1 INT;
DECLARE SecondPos INT; 
SET FirstPos=1;
	IF (LENGTH(sIds) > 0) THEN
        DROP TABLE IF EXISTS TempTable;
        CREATE TEMPORARY TABLE TempTable(id int) ENGINE=MEMORY;
		SET SecondPos = LOCATE(',',sIds,FirstPos);
		WHILE (SecondPos>0) DO
			INSERT INTO TempTable
			VALUES(cast(substring(sIds, FirstPos, (SecondPos - FirstPos)) as unsigned INT));
			set FirstPos=SecondPos+1;
			SET SecondPos=LOCATE(',',sIds,FirstPos);
		END WHILE;
		INSERT INTO TempTable
		VALUES(cast(right(sIds, LENGTH(sIds) - FirstPos + 1 ) as unsigned INT));
	END IF;
END;

CALL parseString('1,3,5,6,7,89,10,11,17,20')

SELECT
 User.*
FROM
 User
 INNER JOIN
 TempTable as tmp
 ON
 tmp.id = User.id