Calling a SP in CF

[rojas1mg]

Hello all. I have written the following SP in SQL and am trying to call it in CF.

CREATE PROCEDURE sp_oreoriExists(
   @ore_ori varchar(100))
AS

DECLARE @retcode int

SET @retcode = 0

IF EXISTS(
      SELECT ore_ori FROM unfunded
      WHERE ore_ori = 1
   )
   SET @retcode = 1

RETURN @retcode
GO

Here's how I'm trying to call it:

<!---This query will run the Stored Procedure (sp_oreoriExists)--->
<cfstoredproc datasource="unfunded" procedure="sp_oreoriExists" returncode="YES">
<!---cfprocresult name="guest_exists"--->
<cfprocparam type="In" cfsqltype="CF_SQL_SMALLINT" dbvarname="@ore_ori" value="1" null="No">
	</cfstoredproc>
	<cfif CFSTOREDPROC.STATUSCODE eq 1>
	We're sorry, but currently there are no ORE/ORI related unfunded requirements.
	<cfelse>
blahblahblah.....

Here's the bizarre error I'm getting:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Formal parameter '@ore_ori' was defined as OUTPUT but the actual parameter not declared OUTPUT.

Help please.....

rojas1mg - - - I love Tek-Tips and all members who reply.

 

[ecobb]

I'm not exactly sure what you're trying to do, but it looks like you want to pass the "ore_ori" variable to the procedure, run a query, and return the value of the "retcode" variable. If so, try this for your procedure:

CREATE PROCEDURE sp_oreoriExists(
   @ore_ori varchar(100)
   @retcode int OUT)
AS

IF EXISTS(SELECT ore_ori FROM unfunded
           WHERE ore_ori = @ore_ori)
  BEGIN
     SET @retcode = 1
  END
ELSE
  BEGIN
     SET @retcode = 0
  END
END

GO

And this for your CF tag:

<cfstoredproc datasource="unfunded" procedure="sp_oreoriExists">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@ore_ori" value="1" null="No">
<cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" variable="retcode" dbvarname="@retcode " null="No">
</cfstoredproc>

<cfif retcode eq 1>
    .....
    <cfelse>
    .....
</cfif>

On a side note, you really shouldn't name your Stored Procedures starting with a "sp_". That's what SQL Server's internal Stored Procedures (in the Master Database) are named. When SQL Server sees "sp_", I believe it first tries to execute the procedure in the Master Database, then it searches the other databases for it if it doesn't find it.



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher