stored procedure in coldfusion

Posted on 2007-10-10
Medium Priority
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 52

Accepted Solution

_agx_ earned 2000 total points
ID: 20051666
>  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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

864 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