T-SQL & ColdFusion MX Stored Procedure

[Dinghus]

This is so basic but it is kicking my butt.

I am doing a simple user verification and if they verify their info is passed out of the SP.

So what I can't find is how to pass the query result out to CF.

I can't even find any good tutorials on it anywhere.

Do I use a cursor? Do I use another variable and if so what type? What do I do on the CF side?

I actually have this in Oracle but now need to put it in MSSQL and it just isn't passing the info to CF from t-sql.

[TA-Selene]

Returning result sets from a TSQL stored procedure is much easier than with Oracle.

It is much better to use the CFSTOREDPROC tag to run procedures as opposed to CFQUERY.  In your stored procedure, you use a SELECT statement to get the data you want - you can return more than query result set from the stored procedure if you use the CFSTOREDPROCE tag, which is nice.

Before you close your proc tag, use the CFPROCRESULT tag to indentify the result set from the stored procedure and assign it a name in ColdFusion which you will use to output your results.  If you have more than one result set returned, you can use multiple CFPROCRESULT tags and indicate the result set number using the RESULTSET attribute.

Good luck!

[-__cfSearching__-]

It is much better to use the CFSTOREDPROC tag to run procedures as opposed to CFQUERY.

+1

As well as the documentation, you might also find this intro helpful

http://www.adobe.com/devnet/coldfusion/articles/stored_procs.html

[Dinghus]

I just found something nobody has ever mentioned before. Once I found it, I searched the internet for people talking about it and zilch.  I went through all my ColdFusion documentation and nobody talks about this.

What is it?

You can run stored procedures in CFQUERY tags.

I was getting frustrated because I wasn't sure my SP was giving the correct results even tho in MSSQL query analyzer it was saying it was fine. So I just slapped it into a CFQUERY tag (stripped down to just the queries and flow control) and it worked. Wow. It also proved I was correct that there was a problem with my SP.  (feel stooopid ... was using "= NULL" vice "IS NULL"  sigh)

This has just opened up a whole new world to me where I can get rid of tons of cf coding and put it all into 1 query.

Why hasn't anybody talked about this before? Or have they and I just can't find it?

[TA-Selene]

Yes, you can run multiple queries within the CFQUERY tag, including creating and setting variables.  It is not done very often/advised, for several reasons:

• The exact same code will run faster as a stored procedure than passed via CFQUERY because SQL Server will pre-compile the stored procedure
• You can only return one result set from CFQUERY, whereas you can have multiple result sets from one stored procedure using the CFSTOREDPROC tag
• You are putting a lot of database knowledge in your code - it might be more secure in your database

The "Best Practice" is to push as much of the processing to the database as possible.

[Dinghus]

Oh, I understand all that stuff. But it amazes me that I've been doing CF since version 4. Not casually either. Up to my neck in it. And not once have I seen that mentioned.  Why is it significant? Because it is far easier to troubleshoot a query in CF than in the database itself.  Once it is working in a CFQUERY, then move it to a SP.

But for somethings like simple login verification and logging, this could remove a lot of CF code.

[TA-Selene]

That's too funny because whenever I am having a problem with a query in CF, I always copy it and work with it directly in SQL Server because I find it much easier to debug.  To each his own, right? 

I'm guessing you prefer Pepsi over Coke, huh? 

[Dinghus]

Coke? Pepsi? Yuck.

Give me root beer anyday.

Actually coke makes my heart go nuts. Tho pepsi doesn't. Coke gives my wife migraines. Pepsi doesn't.

See now everyone thinks it's obvious that you can run these massive queries on CFQUERY but I have yet to find a reference that says you can do it.  Even Adobe documentation makes no mention of it.  And yes, it would have saved me tons of code when doing projects that I had no access to the database to create a SP.

I guess even an old cat like me can learn new things.

[ilssac]

Dinghus wrote:

You can run stored procedures in CFQUERY tags.

Umm, yes you can!  You can run any SQL in a <cfquery...> tag that your database management system understand.  All the <cfquery...> tag does in bottle up the SQL text into a little string package and hand it off to the database driver to send to the database which gets busy interupting and parsing the SQL code.  CF has no idea what the SQL does and it makes no judgements about it.  It can only handle basic record sets returned from the database.  But otherwise anything is fine.

I have been known to write my CREATE OR REPLACE STORED PROCEDURE... code in a <cfquery...> block when I did not have access to any other database editor.

[-__cfSearching__-]

I was getting frustrated because I wasn't sure my SP was giving the correct results even tho in MSSQL query analyzer it was saying it was fine. So I just slapped it into a CFQUERY tag (stripped down to just the queries and flow control) and it worked. Wow. It also proved I was correct that there was a problem with my SP.  (feel stooopid ... was using "= NULL" vice "IS NULL"  sigh)

Well, just because a statement does not generate an error does not mean it is "fine" 😉  I find it is a good idea to test the individual pieces of a stored procedure in the database, as you build it, not just the end result.  You can catch most of the common errors that way.

Assuming you have direct access to the database, that is ..