Stored Procedures CF workflow? newbie question

[lovewebdev]

I'm trying to move from queries to Stored Procedured but having a tough time understanding the flow of stored procedures. I attached code where all I want to do is filter a table with an id from a CF url parameter (userid) and then retrieve other column values and pass it to the CF page.

I'm not getting the correct value from the db upon cfoutput. it keeps on displaying the value 1 on the page no matter which db column I try to output from the stored procedure.

[Dan_Bracuk]

What happens when you run it?

[lovewebdev]

lol good question.
I'm simply using <cfoutput>#clientusername#</cfoutput> to display one of the OUT variables on the page and I just keep getting a display of the number 1 as the result. no matter which variable I try to access. It should be text coming from the column clientusername.

[lovewebdev]

I've also tried the following code to display the value from the Stored procedure column and I get an error saying:
The value of the attribute query, which is currently "clientinfo", is invalid.

[-__cfSearching__-]

lovewebdev
> @clientusername VARCHAR OUTPUT,
> @clientpwd VARCHAR OUTPUT,

Before you throw CF into the mix, did you first verify the stored procedure actually works as expected in sql server? I noticed the varchar variables are missing a size declaration. Add a size that is large enough to hold the corresponding values from the client_info table. Then try running the stored procedure directly in sql server:



DECLARE @clientusername varchar(50)
DECLARE @clientpwd varchar(50)

EXEC getclientstuff 1, @clientusername OUT, @clientpwd OUT
--- display the results
SELECT @clientusername, @clientpwd

> <cfstoredproc procedure="getclientstuff" datasource=etc...>
> ...
> <cfprocresult name="clientinfo">
> </cfstoredproc>

I do not know about best practices, but personally I tend to use either "output" variables OR return a resultset (not both). When I have a lot of information to return, I use a resultset. For simple or single values like a code, or id value I typically use "output" variables.

[Dan_Bracuk]

The 2nd error is understandable because you can only output a query if you have a cold fusion query object, and you don't.

What happens if you cfdump clientinfo?

[lovewebdev]

Ok It worked. When I added the correct (50) to the VARCHAR variables it worked. I wasn't allowed to use DECLARE with OUTPUT so that wasn't it.

Also I can't find the point to <cfprocresult name="clientinfo"> tag like you said. I thought that created a query out of the results. How is this accessible in a CF page?

Adobe Live docs says about the tag "Associates a query object with a result set returned by a stored procedure"
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b17.htm

But whenever I use <cfoutput query="clientstuff">#column#</cfoutput> I get an error that clientstuff is undefined.

Thanks!

[JR__Bob__Dobbs-qSBHQ2]

If your stored procedure returns a resultset. The one in your first post does not:

Use <cfoutput query="clientinfo"> ... </cfoutput>. The query results are refered to by the name of the cfprocresult not the cfstoredproc.

You should use <cfoutput>#clientusername# #clientpwd#</cfoutput>

[-__cfSearching__-]

> Also I can't find the point to <cfprocresult name="clientinfo"> tag like you said.
> I thought that created a query out of the results.

It does, but only if your stored procedure returns a query/resultset. Yours does not. If you wanted to return a resultset, _instead of_ output variables, remove the OUT variables from your procedure declaration. Then do a regular SELECT inside the procedure:

CREATE PROCEDURE getclientstuff
@userid INT
AS
BEGIN
SELECT client_info.clientusername, client_info.clientpwd
FROM client_info
WHERE client_info.userid = @userid
END;

Finally use the cfprocresult tag to capture those results as query.

<cfstoredproc procedure="getclientstuff" datasource=etc...>
<cfprocparam type="IN" cfsqltype="CF_SQL_INTEGER" dbVarName="@userid" value="1" null="No">
<cfprocresult name="clientinfo">
</cfstoredproc>

Then you can use the #clientinfo# variable just like any other query object.