IF NOT EXISTS (SELECT * FROM sysobjects WHERE type IN ('IF', 'TF') AND name = 'Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable')
BEGIN
EXECUTE ('CREATE FUNCTION dbo.[Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable]() RETURNS @TempReportDataGetTable TABLE (id int) AS BEGIN RETURN; END')
END
GO
--Input Parameter @list is a string with MOM object's Rowid list,
--Like '121,123,124,125,126'
--Output a table which has Object's RowID as its rows.
ALTER FUNCTION [dbo].[Microsoft_SystemCenter_VirtualMachineManager_2016_Function_ObjectRowIDList_ToTable] (@list NVARCHAR(MAX))
RETURNS @tbl TABLE (number INT NOT NULL) AS
BEGIN
DECLARE @pos INT,
@nextpos INT,
@valuelen INT
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = CHARINDEX(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (CONVERT(INT, SUBSTRING(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
Home