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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
