1. SQL / Говнокод #19041

    +4

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    BEGIN
    	SELECT count(DISTINCT CLCNC)
    	INTO P_CLI
    	FROM P040
    	WHERE NRTM = P_NRTM0 AND ESCN= P_ESCN;	
    EXCEPTION
    				WHEN NO_DATA_FOUND
    				THEN
    					P_CLI := 0;
    				WHEN TOO_MANY_ROWS
    				THEN
    					P_CLI := 0;
    END;

    — Держите.
    — Зачем?
    — Ну, как говорится, на всякий пожарный случай.
    — С войны не держал боевого оружия.
    — Ну, это не боевое, а скорее психологическое. При случае можно пугнуть, подать сигнал. Заряжен холостыми.
    — Дайте один боевой!
    — Зачем?
    — На всякий пожарный.

    OAS_GUEST, 18 Ноября 2015

    Комментарии (4)
  2. SQL / Говнокод #18993

    +7

    1. 1
    SELECT ROWNUM INTO cnt FROM t00 WHERE  ROWNUM IN (SELECT ROWNUM FROM p00 WHERE ROWNUM = 1);

    нездоровая любовь к ROWNUM

    OAS_GUEST, 09 Ноября 2015

    Комментарии (1)
  3. SQL / Говнокод #18992

    −1

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    PROCEDURE ...
    ...
    IS
    ....
        CURSOR c3 (numero NUMBER) IS
                    SELECT count(DISTINCT prsv) cont
                    FROM sr200 WHERE  NR = NUMERO;
        rec3 c3%ROWTYPE;
    ...
    BEGIN
      .....
        OPEN c3(PROC);
            LOOP FETCH c3 INTO rec3;
        EXIT WHEN c3%NOTFOUND;
        END LOOP;
        CLOSE c3;
    
        CONT := nvl(rec3.cont, 0);
    
       FOR I IN 1 .. CONT
            LOOP CAB := CAB || chr(9);
        IF i = CONT
        THEN
            EXIT;
        END IF;
        END LOOP;
    ....
    END;

    нунахера?? )

    OAS_GUEST, 09 Ноября 2015

    Комментарии (3)
  4. SQL / Говнокод #18919

    +1

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    9. 9
    select 
      bla bla bla
    into 
      dummy_id
    from 
      dummy
    where bla bla bla and
      nvl(sysdate, p_valid_till) between valid_from and
      nvl(valid_till, sysdate);

    NDR, 27 Октября 2015

    Комментарии (2)
  5. SQL / Говнокод #18763

    +5

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    70. 70
    71. 71
    72. 72
    73. 73
    74. 74
    75. 75
    76. 76
    77. 77
    78. 78
    79. 79
    80. 80
    81. 81
    82. 82
    83. 83
    84. 84
    85. 85
    86. 86
    87. 87
    88. 88
    89. 89
    90. 90
    91. 91
    92. 92
    DECLARE @type tinyint
    SET @type = Case
    	when @WtpID = '2CADA6AF-C99C-44DE-BE67-5361CF6E0785' then 0
    	when @wtpID = 'AFD4A776-6714-409B-AA79-D570FF456C02' then 1
    	when @wtpID = 'BE2804C1-BDB0-4B03-B3C0-77104F6203DD' then 2
    	when @wtpID in ('8687AFE1-8049-4440-8E24-4FC639402313',
    		'BDE881B9-7C7C-40DF-BC19-EA85D3F5F15E',
    		'56143A9A-B151-41BA-8413-D5934FD4CA1B')	 then 3 + Case when @IsExam=1 then 1 else 0 end	
    	when @wtpID in ('D24F7A71-AC73-4596-95A6-8BE6CE35A2FB',
    		'CA29E728-44E9-4844-8712-C4024876D6C6')
    			then 5 + Case when @IsExam=1 then 1 else 0 end
    	end
    print @type
    SET @codeMark = [dbo].[GetCodeByMark](@type, @Mark, 3)
    SET @SresPresent = Case when @Mark in ('2','3','4','5') then 1 else 0 end
    
    DECLARE @r TABLE(ScaID uniqueidentifier, PhfRN tinyint)
    INSERT INTO @r(ScaID, PhfRN)
    SELECT MIN(Cast(ScaID as varchar(50))), PhfRN
    	FROM STUDControlActions
    		join PhFormsControl on PhfID = ScaPhfID
    	WHERE ScaDbgID = @DbgID and ScaWtpID = '56E43643-D0B6-45C6-B53B-09C1149D3671' and PhfAttR=1
    	GROUP BY PhfRN
    -- select * from @r
    
    DECLARE @rCount tinyint = (SELECT COUNT(PhfRN) FROM @r)
    DECLARE @rn tinyint = 1
    print 'np:'
    print @np
    
    DECLARE @cmd varchar(500)=''
    
    IF @np=0
    BEGIN
    
    if @TemplateType = 3	
     BEGIN   
    	SET @itogBall = Case when @itogBallT is null then null when ISNUMERIC(@itogBallT)=1 then CAST(@itogBallT as INT) 
    				when @itogBallT in ('*','x','х') then -9 when @itogBallT = 'z' then -13 else null end
    	SET @SresMark = null	
    	IF @codeMark < 0 SET @SresItogMark = @codeMark
    	ELSE SET @SresItogMark = @itogBall
    	SET @cmd = '[stud].[STUD_ResultsAdd] @SresID = ' + Cast(@SresID as varchar(50)) + ', '+
    			'@ScaID = '+Cast(@ScaID as varchar(50))+', @StudNom = ' + @StudNom + ','+ 
    			'@SresPresent = ' + IsNull(Cast(@SresPresent as varchar(10)),'null') + ', @SresMark = ' + IsNull(Cast(@SresMark as varchar(10)),'null')+ ',' + 
    			'@SresTheme = null, @SresComment = null, @SID = ' + Cast(@SID as varchar(10)) + ', @vEditor = 1, @SresItogMark = ' + Cast(@SresItogMark as varchar(10)) + 
    			', @SresSresID = null, @sNID = null'
    	print @cmd		
    	EXEC [stud].[STUD_ResultsAdd] @SresID = @SresID, @ScaID = @ScaID, @StudNom = @StudNom, 
    			@SresPresent = @SresPresent, @SresMark = @SresMark, 
    			@SresTheme = null, @SresComment = null,
    			@SID = @SID, @vEditor = 1, @SresItogMark = @SresItogMark, @SresSresID = null, @sNID = null	
    	IF @type in (0,1)
    	 BEGIN
    		SET @SresMark = Case when @codeMark < 0 then @codeMark else @itogBall end
    		SET @SresItogMark = Case 
    						when @codeMark < 0 then @codeMark 
    						when IsNull(@itogBall,-1)>=60 then 1 
    						when IsNull(@itogBall,-1)>=0 and IsNull(@itogBall,-1)<60 then 0 
    						else null end
    		SET @SresPresent = Case when IsNull(@itogBall,-1)>=0 then 1 else 0 end
    		
    		SELECT @ScaID = null, @SresID = null, @WtpID = 'BE2804C1-BDB0-4B03-B3C0-77104F6203DD'
    		SELECT @ScaID = ScaID FROM STUDControlActions WHERE ScaDbgID = @DbgID and ScaWtpID = @WtpID
    		IF @ScaID is null RAISERROR('Запись об аттестации разделов отсутствует!', 16, 1);
    		SELECT @SresID = SresID FROM STUDResults WHERE SresScaID = @ScaID and SresSID = @SID
    		
    		SET @cmd = '[stud].[STUD_ResultsAdd] @SresID = ' + Cast(@SresID as varchar(50)) + ', '+
    			'@ScaID = '+Cast(@ScaID as varchar(50))+', @StudNom = ' + @StudNom + ','+ 
    			'@SresPresent = ' + Cast(@SresPresent as varchar(10)) + ', @SresMark = ' + Cast(@SresMark as varchar(10))+ ',' + 
    			'@SresTheme = null, @SresComment = null, @SID = ' + Cast(@SID as varchar(10)) + ', @vEditor = 1, @SresItogMark = ' + Cast(@SresItogMark as varchar(10)) + 
    			', @SresSresID = null, @sNID = null'
    		print @cmd
    		
    		EXEC [stud].[STUD_ResultsAdd] @SresID = @SresID, @ScaID = @ScaID, @StudNom = @StudNom, 
    				@SresPresent = @SresPresent, @SresMark = @SresMark, 
    				@SresTheme = null, @SresComment = null,
    				@SID = @SID, @vEditor = 1, @SresItogMark = @SresItogMark, @SresSresID = null, @sNID = null
    
    		SET @SresMark = Case when @codeMark = -9 then -9
    							when @codeMark in (-12,-13) then 0
    							when IsNull(@itogBall,-1)>=0 then @itogBall 
    							else null end
    		SET @SresItogMark = null
    		SET @SresPresent = Case when IsNull(@itogBall,-1)>=0 then 1 else 0 end
    		
    		SELECT @ScaID = null, @SresID = null, @WtpID = '56E43643-D0B6-45C6-B53B-09C1149D3671'
    		SELECT @ScaID = ScaID FROM STUDControlActions 
    			join PhFormsControl on PhfID = ScaPhfID
    			WHERE ScaDbgID = @DbgID and ScaWtpID = @WtpID and PhfAttR=1 and PhfRN=1 
    		IF @ScaID is null RAISERROR('Запись для 1 раздела отсутствует!', 16, 1);
    		SELECT @SresID = SresID FROM STUDResults WHERE SresScaID = @ScaID and SresSID = @SID

    Показательный фрагмент из хранимой процедуры (общей длиной > 500 строк) MS SQL Server 2008 с боевой системы, которую приходится поддерживать. Хранимок в подобном стиле в проекте - несколько сотен. Немногочисленные комментарии выпилены, т.к. ими можно пренебречь.

    Удивительно, но пользователи постоянно жалуются, что что-то не работает как надо...

    kramolnic, 25 Сентября 2015

    Комментарии (17)
  6. SQL / Говнокод #18679

    +2

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    /*----------------------------------------------------------------*/
    /*  CONCAT                                                        */
    /*----------------------------------------------------------------*/
      FUNCTION concat(r1  IN RAW DEFAULT NULL,
                      r2  IN RAW DEFAULT NULL,
                      r3  IN RAW DEFAULT NULL,
                      r4  IN RAW DEFAULT NULL,
                      r5  IN RAW DEFAULT NULL,
                      r6  IN RAW DEFAULT NULL,
                      r7  IN RAW DEFAULT NULL,
                      r8  IN RAW DEFAULT NULL,
                      r9  IN RAW DEFAULT NULL,
                      r10 IN RAW DEFAULT NULL,
                      r11 IN RAW DEFAULT NULL,
                      r12 IN RAW DEFAULT NULL) RETURN RAW;

    Oracle пакет utl_raw.
    Жаль не 42.

    smart-indus, 06 Сентября 2015

    Комментарии (1)
  7. SQL / Говнокод #18660

    0

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    CREATE PROCEDURE [dbo].[getNextBatchId]
    AS
    BEGIN
    	DECLARE @NewSeqValue int
    	SET NOCOUNT ON
    	INSERT INTO dbo.BatchId_SSQ_SequenceGenerator ([NextBatchId]) VALUES ('a')
    
    	SET @NewSeqValue = scope_identity()
    
    	DELETE FROM dbo.BatchId_SSQ_SequenceGenerator WITH (READPAST)
    	SELECT @NewSeqValue as NextBatchId
    END;
    
    GO

    dbo.BatchId_SSQ_SequenceGenerator состоит из двух колонок - собственно инкрементного SeqId (int) и NextBatchId (nvarchar(1), null)

    intfolk, 01 Сентября 2015

    Комментарии (0)
  8. SQL / Говнокод #18644

    +6

    1. 1
    SELECT * FROM goods ORDER BY rand() LIMIT 10;

    В одном крупном московском интернет-магазине встала задача на главной странице сайта выдавать 10 случайных товарок. Как это было сделано, смотрите выше.
    Сайт несколько прилёг.

    Solo, 27 Августа 2015

    Комментарии (31)
  9. SQL / Говнокод #18553

    +1001

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    declare @col int
    set @col = 0
    
    while (select count(distinct id) from #tovar) > @col
    begin
    set @col = (select count(distinct id) from #tovar)
    	insert into #tovar (ParentID, ID, ISFOLDER, Tov) select parentID, ID, ISFOLDER, DESCR from SC23(nolock) where ISMARK = 0 and PARENTID in (select id from #tovar) 
    end

    dsfix, 30 Июля 2015

    Комментарии (0)
  10. SQL / Говнокод #18490

    +142

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    SELECT     top 20 *,
    		CAST(DATEPART(yy, rs_message_sendtime) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m, 
                          rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(m, 
                          rs_message_sendtime) AS varchar(4)) END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_sendtime) AS varchar(4)))) 
                          = 1 THEN '0' + CAST(DATEPART(d, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_sendtime) AS varchar(4)) END) 
                          + 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_sendtime) 
                          AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_sendtime) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi, 
                          rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(mi, 
                          rs_message_sendtime) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4))) 
                          = 1 THEN '0' + CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4)) END) 
                          AS sendtime, 
    					  CAST(DATEPART(yy, rs_message_valid_from) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m, rs_message_valid_from) 
                          AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(m, rs_message_valid_from) 
                          AS varchar(4)) END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_valid_from) AS varchar(4)))) = 1 THEN '0' + CAST(DATEPART(d, 
                          rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_valid_from) AS varchar(4)) END) 
                          + 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_valid_from) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_valid_from) 
                          AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_valid_from) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi, 
                          rs_message_valid_from) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(mi, 
                          rs_message_valid_from) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4))) 
                          = 1 THEN '0' + CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4)) END) 
                          AS validfrom, 
    					  CAST(DATEPART(yy, rs_message_valid_to) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m, rs_message_valid_to) 
                          AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(m, rs_message_valid_to) AS varchar(4)) 
                          END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_valid_to) AS varchar(4)))) = 1 THEN '0' + CAST(DATEPART(d, rs_message_valid_to) 
                          AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_valid_to) AS varchar(4)) END) + 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_valid_to) 
                          AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_valid_to) 
                          AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi, rs_message_valid_to) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi, 
                          rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(mi, rs_message_valid_to) AS varchar(4)) END) 
                          + ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_valid_to) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(ss, rs_message_valid_to) 
                          AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_valid_to) AS varchar(4)) END) AS validto
    					  
    FROM         rs_message_outbox_ready INNER JOIN
                          rs_customer_feed ON rs_customer_feed.rs_customer_feed_id = rs_message_outbox_ready.rs_customer_feed_id INNER JOIN
                          rs_customer ON rs_message_outbox_ready.rs_customer_id = rs_customer.rs_customer_id
    	WHERE     
    	(rs_message_outbox_ready.rs_message_sendtime <= GETDATE()) AND
    	(rs_message_outbox_ready.rs_message_type = 2) AND 
    	(rs_customer_feed.rs_customer_feed_status = 1) AND 
    	(rs_customer.rs_customer_status = 1) AND 
    	(rs_customer_feed.rs_customer_feed_category = 2) AND 
    	(rs_message_outbox_ready.rs_message_status = 5) AND
    	(rs_customer_feed.rs_customer_feed_xml=1)

    Переводим дату из одного формата в другой :)

    kropotor, 17 Июля 2015

    Комментарии (0)