Folosind una dintre funcţiile din articolul menţionat de Diana, putem introduce cele 92 de culori într-o tabelă:
CREATE FUNCTION iter_charlist_to_tbl
(@list nvarchar(MAX),
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000) NOT NULL,
nstr nvarchar(2000) NOT NULL) AS
BEGIN
DECLARE @endpos int,
@startpos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @startpos = 0
SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2, @tmpstr)
WHILE @endpos > 0
BEGIN
SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
@endpos - @startpos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @startpos = @endpos
SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2,
@tmpstr, @startpos + 1)
END
SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
END
INSERT @tbl(str, nstr)
VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
RETURN
END
GO
CREATE TABLE Culori (
ID int PRIMARY KEY,
Culoare varchar(20) NOT NULL UNIQUE
)
GO
DECLARE @LISTA AS VARCHAR(MAX);
SET @LISTA ='almond,antique,aquamarine,azure,beige,bisque,black,blanched,blue,blush,brown,burlywood,burnished,chartreuse,chiffon,chocolate,coral,cornflower,cornsilk,cream,cyan,dark,deep,dim,dodger,drab,firebrick,floral,forest,frosted,gainsboro,ghost,goldenrod,green,grey,honeydew,hot,indian,ivory,khaki,lace,lavender,lawn,lemon,light,lime,linen,magenta,maroon,medium,metallic,midnight,mint,misty,moccasin,navajo,navy,olive,orange,orchid,pale,papaya,peach,peru,pink,plum,powder,puff,purple,red,rose,rosy,royal,saddle,salmon,sandy,seashell,sienna,sky,slate,smoke,snow,spring,steel,tan,thistle,tomato,turquoise,violet,wheat,white,yellow';
INSERT INTO Culori (ID, Culoare)
SELECT listpos, str FROM dbo.iter_charlist_to_tbl(@Lista, ',')
GO
Apoi, putem genera cele 200.000 de combinaţii astfel:
CREATE TABLE Combinatii (
ID int IDENTITY PRIMARY KEY,
Combinatie varchar(104) NOT NULL
)
GO
SET NOCOUNT ON
DECLARE @N INT
SET @N=200000
WHILE @N>0 BEGIN
INSERT INTO Combinatii (Combinatie)
/* SELECT Culoare1+' '+Culoare2+' '+Culoare3+' '+Culoare4+' '+Culoare5
FROM (SELECT TOP 1 Culoare AS Culoare1 FROM Culori ORDER BY NEWID()) Q1
CROSS JOIN (SELECT TOP 1 Culoare AS Culoare2 FROM Culori ORDER BY NEWID()) Q2
CROSS JOIN (SELECT TOP 1 Culoare AS Culoare3 FROM Culori ORDER BY NEWID()) Q3
CROSS JOIN (SELECT TOP 1 Culoare AS Culoare4 FROM Culori ORDER BY NEWID()) Q4
CROSS JOIN (SELECT TOP 1 Culoare AS Culoare5 FROM Culori ORDER BY NEWID()) Q5
*/
SELECT CONVERT(varchar(104), (
SELECT TOP 5 Culoare+' ' AS "*" FROM Culori ORDER BY NEWID() FOR XML PATH (''), TYPE
))
SET @N=@N-1
END
SET NOCOUNT OFF
GO
SELECT * FROM Combinatii
Probabil va dura cam un minut execuţia acestui cod (pentru a genera 200.000 de combinaţii).
Răzvan