cBBgBWg6

1SET @ImagePath = @ImagePath + ''
2SET @ImageSize = 200
3
4CREATE TABLE Temp
5(
6    ID          INT IDENTITY(1, 1),
7    Tamano      FLOAT,
8    TempFiles   VARBINARY(MAX)
9)
10
11DECLARE @FileNames TABLE 
12        (
13            Name VARCHAR(MAX)
14        )
15
16DECLARE @ShellCommand VARCHAR(MAX) = LOWER('exec xp_cmdshell ''dir ' + REPLACE(@ImagePath, '''', '''''') + '*.' + @FileExtension + '/b''')
17
18INSERT @FileNames 
19EXEC(@ShellCommand)
20
21SET @Temp = '';
22WITH Pruebas AS (SELECT Name = REPLACE(@ImagePath + Name, '''', '''''') FROM @FileNames WHERE Name IS NOT NULL)
23SELECT
24    @Temp = @Temp + 'INSERT INTO Temp(Tamano, TempFiles) VALUES ('''',
25                                                              (SELECT * 
26                                                               FROM OPENROWSET(BULK ''' + p.Name + N''', single_blob) Imagenes
27                                                               ))'
28FROM
29    Pruebas p
30
31EXEC(@Temp)
32
33SET @Limite = (SELECT COUNT(*) FROM Temp)
34
35
36
37WHILE @Counter <= @Limite
38BEGIN
39    SET @TamImagen = (SELECT (DATALENGTH(TempFiles) / 1024.0 / 1024.0) FROM Temp WHERE ID = @Counter)
40    --UPDATE Temp SET Tamano = @TamImagen WHERE ID = @Counter
41    --SET @Tam = (SELECT (DATALENGTH(TempFiles) / 1024.0 / 1024.0) FROM Temp WHERE ID = @Counter)
42    SELECT @TamImagen
43    --SELECT @Tam
44    --IF NOT EXISTS (SELECT ap.RID FROM AnexosPOS AS ap WHERE ap.RID = @Counter)
45        IF ((CHARINDEX('.jpg', @ShellCommand)) > 0 OR (CHARINDEX('.png', @ShellCommand) > 0) OR (CHARINDEX('.bmp', @ShellCommand) > 0)
46             OR (CHARINDEX('.jpeg', @ShellCommand) > 0) OR (CHARINDEX('.gif', @ShellCommand) > 0))
47            IF (@TamImagen < @ImageSize)
48            BEGIN
49                SET @SQL = '';
50
51                WITH EscapedNameCTE AS (SELECT Name = REPLACE(@ImagePath + Name, '''', '''''') FROM @FileNames WHERE Name IS NOT NULL)
52                SELECT
53                    @SQL = @SQL +  'INSERT INTO AnexosPOS (Cuenta, Tipo, Extension, Tamano, Imagen) 
54                                    VALUES (''' + @Cuenta + ''', ''' + @Tipo + ''', ''' + @FileExtension + ''', ''' + CAST(@TamImagen AS VARCHAR(100)) + ''',
55                                    (SELECT * 
56                                     FROM OPENROWSET(BULK ''' + E.Name + N''', single_blob) Imagenes
57                                     ))'
58                FROM
59                    EscapedNameCTE E;
60            END
61            ELSE
62                SELECT @SizeMessage
63        ELSE
64            SELECT @FormatMessage
65
66    SET @Counter = @Counter + 1
67END
68
69EXEC(@SQL)
70TRUNCATE TABLE Temp
71DROP TABLE Temp