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

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