5 Replies Latest reply on Oct 10, 2008 11:02 AM by Blue Chrome

    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.