Fog Creek Software
Discussion Board




T-SQL Stored Procedures

A long time ago I used to work with Oracle, although I have used SQL Server pretty much all the time for the past few years.

One thing I miss from Oracle is the ability to create Packages of stored procedures, and to be able to create constants within the package, which have shared scope across the Package.

I wish SQL Server could do this too, as I don't think there is an elagant way to create shared constants, although I know there are some inelegant ways to do it ;-)

Nemesis
Monday, August 09, 2004

Create a cursor of properties for the 'Package', either statically as a table or when the 'Package' is first called.

Simon Lucy
Monday, August 09, 2004

"Create a cursor of properties for the 'Package', either statically as a table or when the 'Package' is first called. "

..and that probably qualifies for one of the inelegant solutions he mentions....

Nah
Monday, August 09, 2004

You can use a DTS Package and global variables.

Wayne
Monday, August 09, 2004

Out of honest curiosity, how maintainable are package constants? Are they the type of thing that could fall prey to "change the constant for this one SP, and break three others", or are the types of constants they're used for not the kind that generally change?

Philo

Philo
Tuesday, August 10, 2004

Philo, did you ever (in your previous non-MS-advocate life) use Oracle SPs ?

If you had, I reckon you'd think it was a good thing, on balance, to be able to:

* Clean up a bewildering mass of standalone functions and procedures
* Remove hard-coded literals from your application
* Enforce information hiding
* Ease management/permissioning effort

To me, the hard-coding of literals is one of the most ugly things about T-SQL and I can't think of an elegant solution. I do, however, remember my time working with Oracle and then it was a breeze.

Your comments about how they might be used are strictly true, but not helpful, bordering on FUD even. You could equally argue that languages shouldn't support the while construct, as someone might do "while(true) {}".

The point is that if you have these tools available, then you can decide how to use them. It doesn't mean you will automatically abuse them.

With T-SQL, we don't have an elegant choice, so we have to use kludges that are equally as dangerous as the worse case scenario you are suggesting with the Oracle packages.

I guess this is all academic with Yukon saviour here soon-ish, but in the meantime, it would be nice now. Actually, it would have been nice to include in the big re-write that happened to create SQL 6.5.

Nemesis
Tuesday, August 10, 2004

Have you considered writing your SQL code using constants and preprocessing them using a scripting language? It only takes a few lines of Perl, Ruby or your favorite scripting language to do the job. Alternately you could use awk or sed. It's even possible to make this work using the C-preprocessor, which is something that I did for another project (although that was more to allow constant sharing with a C-program -- quite inelegant, really).

Good luck!

WebCoyote
Tuesday, August 10, 2004

I'm intrigued as to why using a table or cursor for such things would be inelegant.

Simon Lucy
Tuesday, August 10, 2004

"I'm intrigued as to why using a table or cursor for such things would be inelegant."

My biggest objection to using a table is that you'd have to know which row/column you wanted in order to access the required "constant". To know this, surely you'd have to have a hard-coded literal string.

This indirection doesn't help.

Perhaps I'm missing something here. Would you care to elaborate Simon.

Nemesis
Tuesday, August 10, 2004

"My biggest objection to using a table is that you'd have to know which row/column you wanted in order to access the required "constant"."

?  If you have a package variable, then do you not have to "hard-code" the name of the variable? I hardly see the difference between

SET @value = package.magic_variable

and

SET @value = dbo.fn_dbrules('magic_variable')

Dennis Forbes
Tuesday, August 10, 2004

Dare I even bring up the subject of exception handling?

Data Miner
Wednesday, August 11, 2004

With a package variable, the compiler will catch any misspellings immediately.

T. Norman
Wednesday, August 11, 2004

"Dare I even bring up the subject of exception handling?"

You mean as a perceived weakness of SQL Server? It is a little late given that SQL Server 2005 is at a beta 2 stage, and will be released early next year. It features try/catch blocks.

Dennis Forbes
Wednesday, August 11, 2004

You don't need hard coded literal strings, you can use two columns if you keep everything in text.

Name and Value.

If you want actual typed values then its Name, Type followed by a column for each possibly type.

Simon Lucy
Thursday, August 12, 2004

Yeah, great, but how do you specify which "row" in the table you want. Obviously, via the "Name" column.

Surely, you need something like:

select @MyVariable = Value
from MyTableOfValues
where Name = 'HardCodedLiteralString'

If you put 'HardCodedLiteralSting' instead, you would never know there was a problem, until runtime. Even then, depending upon your implementation you may not realise there was a bug.

There is no good solution to this.

Nemesis
Saturday, August 14, 2004

*  Recent Topics

*  Fog Creek Home