Simple Stored Proc Question

[bzydaddy]

I have a simple stored procedure that returns text.

CREATE PROCEDURE sactest AS
PRINT 'SAC was here'
GO

Question: How do I capture the result of thise procedure in my program?

<CFSTOREDPROC PROCEDURE="sactest" DATASOURCE="dbname" debug="yes"
returncode="yes">

</CFSTOREDPROC>

[mxstu]

You could return the results in either an output parameter or a resultset .
Return codes are typically used for returning a numeric status indicating the
success or failure of the stored procecure (0 - Success, Otherwise failure).
Attached are (3) simple examples of using ms sql stored procedures.





--- examples of ms sql stored procedures
CREATE PROCEDURE testOutputParam @outParam varchar(100) OUTPUT
AS
BEGIN
SET @outParam = 'The time is: '+ cast(getDate() as varchar(25))
END
GO

CREATE PROCEDURE testResultSet
AS
BEGIN
SELECT getDate() AS TimeEntered
END
GO

CREATE PROCEDURE testReturnCode
AS
BEGIN
RETURN 999
END
GO

<!--- using output variable --->
<cfstoredproc procedure="testOutputParam" datasource="#yourDSN#"
returncode="yes">
<cfprocparam type="out" dbvarname="@outParam" cfsqltype="cf_sql_varchar"
variable="theOutputVariable">
</cfstoredproc>
<cfoutput><b>output variable:</b> #theOutputVariable#</cfoutput><hr>

<!--- using resultset --->
<cfstoredproc procedure="testResultSet" datasource="#yourDSN#"
returncode="yes">
<cfprocresult name="theResultSet">
</cfstoredproc>
<b>resultset:</b> <cfdump var="#theResultSet#"><hr>

<!--- using return code --->
<cfstoredproc procedure="testReturnCode" datasource="#yourDSN#"
returncode="yes">
</cfstoredproc>
<cfoutput><b>returncode:</b> #CFSTOREDPROC.STATUSCODE#</cfoutput><hr>

[bzydaddy]

Thanks. very helpful.