How to access a returned output from SP run in SQL Server environment

[nchen7]

I am migrating our db from Oracle (12c) to SQL Server (2019) but I am new to SQL Server, and I am on CF 2016. One stored procedure that runs just fine in Oracle is giving me trouble. It’s a SP developed by someone and I don’t have control over, but I know it basically generates a primary key from a table and returns an integer (only one number). In Oracle environment I would use:

<cfstoredproc procedure="mySP" datasource="#dsn#">

<cfprocparam cfsqltype="cf_sql_numeric" type="out" name=”next_pk”>

</cfstoredproc>

Then I can access the value of #next_pk# and insert it into a table for a new record.

In SQL Server environment, if I use the same code, it says “has too many arguments”. I saw someone said we can’t use type=”out” in SQL Server. So I use:

<cfstoredproc procedure="mySP" datasource="#dsn#">

<cfprocresult resultset="1" name="next_pk">

</cfstoredproc>

and try to output #next_pk#, it says next_pk is undefined.

If I try to output #next_pk.UseKey#, it says “UseKey” is undefined in next_pk. (“UseKey” is the name of the only output variable by the stored procedure. I remember someone said the output is like a cfquery and you can call the column directly.)

I bet this is not a new issue and I must have missed something. Can someone help me out or give me a pointer? Thanks a lot.

[Dave Watts]

Without seeing the actual SQL in both cases, it's hard to for me to give you an exact answer. So here's a less exact one. Generally, tables allow you to specify a primary key when you create them. The purpose of the primary key is to uniquely identify each record. In both Oracle and SQL Server, there are two ways you can do this. You can identify one or more "natural" fields within your data that collectively will always have a unique value or set of values. This is called for obvious reasons a natural key. Or, like nearly everybody in the world, you can create a unique value that's just made up, like an integer counter where each new record gets one higher value, and use that as your unique key. This is called a "surrogate key" because it's just a made-up value that doesn't have any meaningful relationship with the rest of the row values.

But this is handled one way in Oracle and another way in MS SQL Server. In Oracle, you have something called a "sequence", and you kind of have to work with this directly, hand-cranking your next value from the database to see what it is. In MS SQL Server, on the other hand, you can just specify that the database automatically generate this value for you, and fetch it without having to worry about the actual generation process. This auto-generated primary key is called an "identity" column in MS SQL Server.

That was a long and probably unnecessary explanation, so here's the shorter version. You probably don't even need that stored procedure if you're using MS SQL Server. Instead, you can just create new records without having the key yet, then fetch the newest key when you fetch the record. If you need to get the auto-generated primary key right away, you can fetch it with the @@identity variable right when you perform the insert, using "INSERT INTO ... SELECT @@identity" syntax.

Now, here's the bad part. If your DB person is literally copying everything about how your Oracle queries into MS SQL, you won't be able to use this handy feature. In other words, you won't have that identity column to play with. If that's the case, you should talk to your DB person to see why not.

Anyway, even if you don't have control over the content of the stored procedures you're using, you might have the ability to read the contents of them and see what's going on.

Dave Watts, Eidolon LLC

[nchen7]

Hi Dave,

Thank you for providing the useful info. My problem is that my app is an add-on app to a primary app that other team developed. And I have to use that stored procedure to insert new records into some tables the primary app inserts to as well. I know what the code of the SP but I am not allowed to share it. The SP returns an integer and I have been able to retrieve it in Oracle db without any problem by using this syntax:

<cfstoredproc procedure="mySP" datasource="#dsn#">

<cfprocparam cfsqltype="cf_sql_numeric" type="out" name=”next_pk”>

</cfstoredproc>

I am puzzled as to why CF handles it differently for different DBs (Oracle vs SQL Server), and why there is no info in CF documentation that mentions how to code it differently for these two DBs. I have been searching many CF docs and people’s posts but can’t find a solution to it. What I need is very simple: the SP returns an integer, and I want to catch that variable and use it to insert into a table as the primary key. But CF tells me it is not defined even though I use <cfprocresult resultset="1" name="next_pk">.

[BKBK]

Hi nchen7 ,

You say you're puzzled "why CF handles it differently for different DBs (Oracle vs SQL Server), and why there is no info in CF documentation that mentions how to code it differently for these two DBs."

Well, there is at least one way in which ColdFusion handles queries - every query, including stored procedures - differently for different database brands. And it is exhaustively documented: the datasource property!

I would suggest you create - in the ColdFusion Administrator - a separate SQL Server datasource. You will then have 2 variants of the stored procedure:

<cfstoredproc procedure="myOracleSP" datasource="#OracleDSN#">

<cfprocparam cfsqltype="cf_sql_numeric" type="out" name=”next_pk”>

</cfstoredproc>

<cfstoredproc procedure="mySQLServerSP" datasource="#SQLServerDSN#">

<cfprocparam cfsqltype="cf_sql_numeric" type="out" name=”next_pk”>

</cfstoredproc>

[nchen7]

Hi BKBK,

I am not sure if I totally understand what you meant by “the datasource property” and “2 variants”. My problem occurred after I switched the data source from Oracle to SQL Server. So I do have two data sources. With Oracle I have used this CF code for several years without problems:

<cfstoredproc procedure="SP_getNextPrimarykey" datasource="#dsn#">

<cfprocparam cfsqltype="cf_sql_numeric" type=”out” variable="next_pk">

</cfstoredproc>

<cfoutput>#next_pk#</cfoutput>

With SQL Server data source, when I use the same SP call above, it says “too many arguments”. If I take out “type=out” in <cfprocparam> tag, it says “#next_pk# is undefined” pointing to the output. If I use <cfprocresult> tag and set “name=next_pk”, it says  “#next_pk# is undefined” pointing to the output too.

I tried to search online CF docs to see if there is a mention regarding how to use these tags differently (Oracle vs SQL Server environment) but I haven’t found one. Again I do have two different data sources for Oracel and SQL Sever and the error occurs only in SQL Server data source.

Did you mean such different usage is documented? Can you please point me to it? I might have missed it while doing the search. Thanks.

[BKBK]

Hi nchen7 ,

Sorry, there's been a misunderstanding. It is now clear that you had already done precisely what I later suggested. That is, run separate stored procedures with separate Oracle and SQL Server datasources. This is indeed an odd state of affairs.

Let's look into it some more. But to start with, let's get something out of the way. It might or might not be relevant, but it would enable us to begin with a clear playing field. Is there any change when you replace all the curly quotes - for example, around ”out” and ”next_pk” - with ordinary quotes?

[Dave Watts]

CF handles these differently because of the databases themselves and the JDBC drivers they use. I honestly don't work with Oracle enough to feel comfortable saying "this is how it should work" but I do know that the two databases handle surrogate primary keys differently. I think you're probably going to have to work with your DB people if they absolutely need to keep using SQL Server the way they used to use Oracle.

You might try using CFPROCRESULT instead of CFPROCPARAM for your SQL Server "sequences". You might also have your DB people change your stored procedures so they work a bit more like this example:

http://dulaw.org/cfdocs/htmldocs/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7d52.html

Dave Watts, Eidolon LLC