Professional Web Applications Themes

Simple Stored Procedure Error - Coldfusion Database Access

CREATE PROCEDURE sp_magazine_sections magazine_id INT, magazine_section_id INT as IF magazine_section_id is not null BEGIN SELECT TblMagazine_Sections.magazine_section_name, TblMagazine_Sections.magazine_section_id, TblMagazine_Sections_LK.magazine_section_id_fk, TblMagazine_Sections_LK.magazine_id_fk, TblMagazine_Sections_LK.magazine_section_photo , TblMagazine_Sections_LK.magazine_section_desc FROM TblMagazine_Sections_LK INNER JOIN TblMagazine_Sections ON TblMagazine_Sections_LK.magazine_section_id_fk = TblMagazine_Sections.magazine_section_id WHERE TblMagazine_Sections_LK.magazine_id_fk = magazine_id AND TblMagazine_Sections_LK.magazine_section_id_fk =magazine_section_id ORDER BY magazine_section_order END ELSE BEGIN SELECT TblMagazine_Sections.magazine_section_name, TblMagazine_Sections.magazine_section_id, TblMagazine_Sections_LK.magazine_section_id_fk, TblMagazine_Sections_LK.magazine_id_fk, TblMagazine_Sections_LK.magazine_section_photo , TblMagazine_Sections_LK.magazine_section_desc FROM TblMagazine_Sections_LK INNER JOIN TblMagazine_Sections ON TblMagazine_Sections_LK.magazine_section_id_fk = TblMagazine_Sections.magazine_section_id WHERE TblMagazine_Sections_LK.magazine_id_fk = magazine_id ORDER BY magazine_section_order END...

  1. #1

    Default Simple Stored Procedure Error

    CREATE PROCEDURE sp_magazine_sections

    magazine_id INT, magazine_section_id INT

    as


    IF magazine_section_id is not null
    BEGIN
    SELECT TblMagazine_Sections.magazine_section_name,
    TblMagazine_Sections.magazine_section_id,
    TblMagazine_Sections_LK.magazine_section_id_fk,
    TblMagazine_Sections_LK.magazine_id_fk,
    TblMagazine_Sections_LK.magazine_section_photo ,
    TblMagazine_Sections_LK.magazine_section_desc
    FROM TblMagazine_Sections_LK INNER JOIN
    TblMagazine_Sections ON
    TblMagazine_Sections_LK.magazine_section_id_fk =
    TblMagazine_Sections.magazine_section_id
    WHERE TblMagazine_Sections_LK.magazine_id_fk = magazine_id AND
    TblMagazine_Sections_LK.magazine_section_id_fk =magazine_section_id
    ORDER BY magazine_section_order
    END

    ELSE
    BEGIN
    SELECT TblMagazine_Sections.magazine_section_name,
    TblMagazine_Sections.magazine_section_id,
    TblMagazine_Sections_LK.magazine_section_id_fk,
    TblMagazine_Sections_LK.magazine_id_fk,
    TblMagazine_Sections_LK.magazine_section_photo ,
    TblMagazine_Sections_LK.magazine_section_desc
    FROM TblMagazine_Sections_LK INNER JOIN
    TblMagazine_Sections ON
    TblMagazine_Sections_LK.magazine_section_id_fk =
    TblMagazine_Sections.magazine_section_id
    WHERE TblMagazine_Sections_LK.magazine_id_fk = magazine_id
    ORDER BY magazine_section_order
    END

    ism Guest

  2. #2

    Default Re: Simple Stored Procedure Error

    What does your CFSTOREDPROC call look like in CF? (CFPROCPARAM for magazine_section_id, etc.)

    Phil
    paross1 Guest

  3. #3

    Default Re: Simple Stored Procedure Error

    And just as a practice, you may wish to consider renaming your stored
    procedures to not begin with sp_. Usually, sp_ stored procedures are system
    stored procedures and using an alternate naming convention will help in the
    long run. I agree with paross1 that it looks like the issue is in the
    CFSTOREDPROC call.

    sdwebguy99 Guest

Similar Threads

  1. Oracle Stored procedure error from CF
    By peace4theapes in forum Coldfusion Database Access
    Replies: 13
    Last Post: November 16th, 11:28 PM
  2. Stored procedure error
    By HariMaki in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: December 3rd, 12:59 PM
  3. simple stored procedure
    By dave in forum ASP Database
    Replies: 2
    Last Post: April 6th, 04:17 PM
  4. C stored procedure SQL error.
    By Craig in forum IBM DB2
    Replies: 2
    Last Post: July 11th, 04:44 PM
  5. Stored Procedure Servers Error
    By Adam Campbell in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 02:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139