Professional Web Applications Themes

Stored procedure problem - Coldfusion - Advanced Techniques

Hi everybody, I am now using <cfstoredproc> to call the stored proc in MSSQL in retrieving the data. I am trying to do some looping like this: <cfloop index="ID" from="1" to="3" step="1"> <cfstoredproc procedure="dt_sp1" datasource="#Application.datasource#"> <cfprocparam type="IN" CFSQLType="cf_sql_integer" dbvarname="ID" value="#ID#"> <cfprocresult name="sp_sp1"> </cfstoredproc> <cfstoredproc procedure="dt_sp2" datasource="#Application.datasource#"> <cfprocparam type="IN" CFSQLType="cf_sql_integer" dbvarname="ID" value="#sp1.ID#"> <cfprocresult name="sp_sp2"> </cfstoredproc> </cfloop> I tried to insert sp1.ID into sp2 to do the looping but it showed an error that CFSQLTYPE in dt_sp2 is invalid. I have tried some other types but the problem persisted. Is that mean I cannot do looping in this way or I have ...

  1. #1

    Default Stored procedure problem

    Hi everybody,

    I am now using <cfstoredproc> to call the stored proc in MSSQL in retrieving
    the data. I am trying to do some looping like this:

    <cfloop index="ID" from="1" to="3" step="1">

    <cfstoredproc procedure="dt_sp1" datasource="#Application.datasource#">
    <cfprocparam type="IN" CFSQLType="cf_sql_integer" dbvarname="ID" value="#ID#">
    <cfprocresult name="sp_sp1">
    </cfstoredproc>

    <cfstoredproc procedure="dt_sp2" datasource="#Application.datasource#">
    <cfprocparam type="IN" CFSQLType="cf_sql_integer" dbvarname="ID"
    value="#sp1.ID#">
    <cfprocresult name="sp_sp2">
    </cfstoredproc>

    </cfloop>

    I tried to insert sp1.ID into sp2 to do the looping but it showed an error
    that CFSQLTYPE in dt_sp2 is invalid. I have tried some other types but the
    problem persisted. Is that mean I cannot do looping in this way or I have done
    sth wrong?




    ?? Guest

  2. #2

    Default Re: Stored procedure problem

    ?? wrote: 

    If your procresult from the first proc is sp_sp1, and it has in it a
    field called ID, then you'd access that as sp_sp1.ID, not sp1.ID.


    --
    Matt Woodward
    com
    Team Macromedia - ColdFusion
    mpwoodward Guest

  3. #3

    Default Re: Stored procedure problem

    Well.

    You're looping through the stored procedure calls three times. Every call
    after the first would overwrite the named results of the previous call.

    The value attribute in the second call should be sp_sp1.ID, not sp1.ID, since
    sp_sp1 is the name of your first result set.

    What are you trying to accomplish with this code?

    philh Guest

  4. #4

    Default Re: Stored procedure problem

    I would also look into using Type="InOut" in your first cfprocparam tag to get
    a more reliable value passed back from your procedure. Especially if the first
    procedure is just inserting or updating, then there's no need to pass back an
    entire recordset just to reference the ID you need. Let me know if you'd like
    an example.

    robbieMack Guest

  5. #5

    Default Re: Stored procedure problem

    "sp1.ID" is a typo. I actually use "sp_sp1.ID" but still not work. Any idea?

    robbieMack, would you mind gove me an example about "inout"?
    ?? Guest

  6. #6

    Default Re: Stored procedure problem

    In the CF Code :
    <cfstoredproc...
    <cfprocparam type="INOut" CFSQLType="cf_sql_integer" dbvarname="ID"
    value="#ID#" variable="myReturnedID">
    </cfstoredproc...

    In the stored proc:
    (header, where you declare input parameters)
    ID int OUTPUT
    (setting the ID to the ID of a table)
    SET ID = IDENT_CURRENT('dbo.myTable')

    The sql var,ID, will return whatever value it has by the end of the procedure
    and pass the value into a variable called 'myReturnedID' (you can call this
    whatever you want). Note that in the procparam tag, myReturnedID is NOT in
    pounds, because you're just giving the name of the variable that you want CF to
    store the value in.

    One important note here...I would steer away from calling vars 'ID'. I think
    that's a reserved word in SQL and may cause problems.




    robbieMack Guest

  7. #7

    Default Re: Stored procedure problem

    I don't really undersatnd this line: SET ID = IDENT_CURRENT('dbo.myTable')

    What is the "dbo.myTable" represent? Should I just replace sth to
    "dbo.myTable" in order to make it works? How about the IDENT_CURRENT?

    ?? Guest

  8. #8

    Default Re: Stored procedure problem

    SET ID = IDENT_CURRENT('dbo.myTable')

    Returns the last identity value generated for a specified table in any session and any scope.


    vkunirs Guest

  9. #9

    Default Re: Stored procedure problem

    robbieMack wrote: 

    are you sure you want IDENT_CURRENT()? that returns the last identity
    value on that table for *any* scope (if another process is banging on
    that table things could get interesting). maybe SCOPE_IDENTITY() is more
    appropriate?

    PaulH Guest

  10. #10

    Default Re: Stored procedure problem

    I don't know the different actually. I just want to get a different sp_sp1.ID in each loop and insert into sp_sp2 to retrieve the data I want
    ?? Guest

  11. #11

    Default Re: Stored procedure problem

    Just threw the IDENT_CURRENT() piece in there as a typical example of using
    return vars. You can get your identity any way you want. You may not even be
    after an identity, and ID is just some other var you've constructed.

    PaulH...Good point about the IDENT_CURRENT(). If surrounded by a transaction
    though, it should be safe? if not, please let me know!!!



    robbieMack Guest

  12. #12

    Default Re: Stored procedure problem

    robbieMack wrote: 

    depends on the lock you get but i'd not use a table based one unless
    that's exactly what i wanted.
    PaulH Guest

Similar Threads

  1. Problem in Stored procedure
    By Rajan_cfm in forum Coldfusion Database Access
    Replies: 4
    Last Post: March 9th, 09:30 PM
  2. stored procedure problem
    By Leon Shaw in forum Dreamweaver AppDev
    Replies: 42
    Last Post: November 7th, 05:13 PM
  3. ADO/Stored Procedure Problem
    By James Arnall in forum ASP Database
    Replies: 9
    Last Post: August 13th, 08:37 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