Joe
If I'm not mistaken you are only generating one recordset
(ie doing one SELECT) if the default record gets created,
whereas if it doesn't, you are generating 2 because of your
placeholder query.
Nick
-----Original Message-----
From: Joe Sheble aka Wizaerd [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 10, 2001 5:03 PM
To: CF-Talk
Subject: Different Stored Proc Help
I ahve a stored procedure that checks to see if any records exisit, and if
not creates a default one. The it polls the database and creates basically
an empty query. The problem is if this new default record gets created, my
main resultset variable does not contain a valid query. Whereas if the
default record doesn't get created, my query variable is populated
appropriately.
Any ideas?
Here's the cfstoredproc call:
<CFSTOREDPROC PROCEDURE="zp_GetDCPs" DATASOURCE="#DSN_Name#"
USERNAME="#DSN_Username#" PASSWORD="#DSN_Password#">
<CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_VARCHAR"
DBVARNAME="@IDNumber" VALUE="#SESSION.CurUser.IDNumber#">
<CFPROCRESULT NAME = "qMain" RESULTSET="2">
</CFSTOREDPROC>
and here's the stored proc itself:
CREATE PROCEDURE zp_GetDCPs ( @IDNumber varchar(20) )
AS
DECLARE @cHold varchar(1)
SET @cHold = '0'
IF NOT EXISTS( SELECT DCPNumber FROM dcp_Header WHERE IDNumber = @IDNumber )
BEGIN
SET @cHold = '1'
INSERT INTO dcp_Header( IDNumber, DCPDate, BeenApproved )
VALUES( @IDNumber, getdate(), 0 )
END
IF @cHold = '0'
BEGIN
-- merely a resultset placeholder
SELECT IDNumber FROM dcp_Header WHERE IDNumber = @IDNumber
END
-- current employees DCP's
SELECT dcp_Employees.IDNumber, dcp_Employees.FirstName,
dcp_Employees.LastName, dcp_Header.DCPNumber, dcp_Header.CurPosition,
dcp_Header.DCPDate, dcp_Header.BeenApproved
FROM dcp_Employees LEFT JOIN dcp_Header ON dcp_Employees.IDNumber =
dcp_Header.IDNumber
WHERE dcp_Employees.IDNumber = @IDNumber
ORDER BY dcp_Employees.LastName, dcp_Header.DCPDate DESC
Joseph E. Sheble
[EMAIL PROTECTED]
========================
Wizaerd's Realm
http://www.wizaerd.com
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
