stored procedure in coldfusion

Posted on 2007-10-10
Last Modified: 2013-12-24
I have a stored procedure which supposed to retrieve the max number in sortorder column and add 1 to it while inserting. Insted I get always 1 inserted
CREATE PROCEDURE [dbo].[addCategory]  
@category varchar (100),
@schoolcode varchar (100),
@hidden bit



declare @sort int
select  @sort=isNull( max (CategorySort),0) from trnAcademicProgramCategory where schoolcode='@schoolcode'

insert into trnAcademicProgramCategory (category, SchoolCode, CategorySort,hidden) values ( @category , @schoolcode , @sort +1 , @hidden)


SP call
<cfif isDefined("form.add")>
            <div id="hidden">
                  <label for="categoryname">Category name</label>
                <input type="text" name="categoryname" id="categoryname">
                 <div id="hidden">
                  <label id="label" for="hidden">Show/Hide:</label>
                <label for="hidden1">Yes</label>
                <span class="schoolcode1"><input type="radio" id="hidden1" name="hidden" value="0" >
                 <label for="hidden">No</label>
                <input type="radio" id="hidden2" name="hidden" value="1"></span>
            <input type="submit" value="Save Category" name="btnsubmit3" id="btnsubmit3">
            <cfparam name="form.hidden" default="0">
            <cfif isDefined("form.btnsubmit3")>
                   <cfstoredproc procedure="addCategory" datasource="HotBanana_Manager">
                            <cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#form.categoryname#">        
                      <cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#session.schoolcode#">
                              <cfprocparam type="in" cfsqltype="cf_sql_bit" value="#form.hidden#">
Question by:erikTsomik
    1 Comment
    LVL 51

    Accepted Solution

    >  Insted I get always 1 inserted

    Probably because you've got quotes around the variable name. So its searching for records where the
    schoolcode equals the literal string:  '@schoolcode'

    But you could combine those two statements into one:

    INSERT INTO trnAcademicProgramCategory (category, SchoolCode, CategorySort,hidden)
    SELECT       @category, @schoolcode, IsNull(MAX(CategorySort), 0) + 1, @hidden
    FROM      trnAcademicProgramCategory
    WHERE   schoolcode = @schoolcode


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Salary Amount Format 13 51
    Custom headers 9 25
    Problem to echo 6 31
    monitor queries that use too much tempdb log 20 20
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
    The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now