Stored Procedure Recordset

[Blue Chrome]

I am using MS SQL and ColdFusion in a series of pages. I had everything working great using the server behaviors in Dreamweaver, but my forum browsing has convinced me to switch to Stored Procedures. I am just trying to get a basic recordset to return to the page (rs_Entities with fields Entity_ID and Name_Long). Here is my stored procedure (which executes correctly in MS SQL):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: Todd Kirby
-- Create date: 10/10/2008
-- Description: Entities list
-- =============================================
ALTER PROCEDURE [dbo].[spLGD_Sel_DATAEntities_Recordset]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
Name_Long,
Entity_ID
FROM
dbo.TblDATA_Entities
WHERE
Name_Long <> '""'
ORDER BY
Name_Long ASC
END;

and here is my CF calling it:

<cfstoredproc procedure="spLGD_Sel_DATAEntities_Recordset" datasource="LocalDebt">
<cfprocparam type="out" value="#Name_Long#" cfsqltype="cf_sql_varchar">
<cfprocparam type="out" value="#Entity_ID#" cfsqltype="cf_sql_integer">
<cfprocresult name="rs_Entities">
</cfstoredproc>

But when I try to run the page, I keep getting an error "Variable NAME_LONG is undefined".

Any help would be appreciated.

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

Your resultset will not be exposed as output parameters. Omit the cfprocparam tags in your code.

[Blue Chrome]

Thanks for helping a newbie out guys, eliminating the cfprocparam tags fixed the problem. I'll be back to this forum soon I'm sure.

[JR__Bob__Dobbs-qSBHQ2]

Blue Chrome,

You might find the attached script helpful. I use this in SQL Management Studio to generate the ColdFusion tags used to call stored procedures. Note that you will need to add cfprocresult tags manually

[Blue Chrome]

Bob - Thanks, but that's a little advanced for me right now. I'll hold onto the script for later.

[Newsgroup_User]

Blue Chrome wrote:
><cfprocparam type="out" value="#Name_Long#" cfsqltype="cf_sql_varchar">
><cfprocparam type="out" value="#Entity_ID#" cfsqltype="cf_sql_integer">
><cfprocresult name="rs_Entities">


Value="#Name_Long#" is trying to make use of a ColdFusion variable named
'Name_long' and pass it into an out parameter.

I don't think you want to be doing that, but I'm not sure I know what
you do need to be doing.

My first guess is that you don't want any <cfprocparam ...> tags because
I do not see and parameters being passed out of your proceedure.

My second guess would be that if you do want <cfproceparam...> tags then
you want to be using the variable="Name_Long" to define the ColdFusion
variable to receive the value of the parameter rather then the
value="#Name_Long#" which would be used to send a value into the stored
procedure.