Organizing SQL Procedures and Functions like Oracle Packages (ish)

Oracle's package syntax is a nice way of packaging sets of functions/ procedures etc, however when a package is updated, that brings everything down. If you have a cached JDBC connection, that causes even more problems as you have to recycle the connections.

On SQL, there is no package, and thus you could end up with hundreds if not thousands of little procedures and functions.

A good trick is to use schemas to organize them: this example will return 2 queries; could also use input output parameters

create schema [myutils];

create procedure [ myutils ].[aFewQueries]
@someid int output
,@someid2 int output

as


begin
--  set nocount on; added to prevent extra result sets from interfering with SELECT statements.


set nocount on;

-- Insert statements for procedure here
select * from table1 where id = @someid;

select * from  table2_other where  id = @someid ; 

select  @someid2  = 5;

end

go

In ColdFusion you could neatly call this like so:

<cfstoredproc procedure="myutils.aFewQueries " datasource="aDatasource" result="stRes">
<cfprocparam cfsqltype="cf_sql_integer" variable="someid" type="inout" value="1" >
<cfprocparam cfsqltype="cf_sql_integer" variable="someid2" type="out">

 <cfprocresult name="qTable1" resultset="1">
<cfprocresult name="qTable2" resultset="2">
</cfstoredproc>

Then have easy access to variable someid, someid2 and queries qTable1, qTable2

I use the someid as an inout variable as I like being able to send in empty variables for "save" type of procedures (if no id, then insert, else update etc).


Of course the good thing about this is it hides the underlying structure (not in this example obviously) of the data, and users can have access JUST to the relevant procedures and approved queries.

Comments

Post a Comment

Popular posts from this blog

Global SQL Procedure, System Objects and sp_ms_marksystemobject

You may come across a need to have a database of utils full of generic procedures which work on indvidual databases (say client databases). You can't pass the database name into the procedure as a parameter and say "use @dbname" in the procedure and dynamic sql sucks. One workaround is to create the procedure in the master database and then mark it as a system object. eg use [master] create procedure sp_doThis // note the sp_ prefix is required begin // etc etc end go exec sp_ms_marksystemobject 'sp_doThis' // second note, this procedure is undocumented, so I wouldn't be relying on this for life or death. use [myotherdb] exec sp_doThis go All done!
Read more

cf_sql_timestamp vs cf_sql_date vs getdate()

If there's one thing I don't like it is people confusing a date with a timestamp, and how a lazy bit of development can ruin a load of data. Here is a simple query inserting data to a timestamp I found in a system where all the created dates were truncated because of the incorrect syntax. Bad one which was in use: insert into testdate values (<cfqueryparam cfsqltype="cf_sql_date" value="#createodbcdatetime(now())#">); Result :  2015-09-29 00:00:00.000 Best One (easiest to read) insert into testdate values(getdate() ); Result : 2015-09-29 10:10:09.880 Not great (no binding) insert into testdate values(#createodbcdatetime(now())#); Result :  2015-09-29 10:14:44.000 What the bad one should have been insert into testdate values(<cfqueryparam cfsqltype=" cf_sql_timestamp " value="#createodbcdatetime(now())#">)
Read more

Lucee 4.5.2 cfpdfparam difference with Adobe ColdFusion

Suppose the following on Lucee 4.5.2: <cfset x = 1> <cfdocument format="pdf" pagetype="A4" name="myVar#x#"> <cfdocumentsection> Hi there </cfdocumentsection> </cfdocument> <cfpdf action="merge" destination="RAM:///myPDF.pdf" overwrite="yes">     <cfpdfparam source="#evaluate("myVar#x#")#" /> </cfpdf> <cfcontent file="RAM:///myPDF.pdf" type="application/pdf"> then to check it also runs on Adobe Cf; you'd actually end up with: "ByteArray objects cannot be converted to strings."   The original code for Adobe CF ran with the paramater like so:  <cfpdfparam source="myVar#x#" /> Both sets of documentation list source defined as follows: "Source PDF file to merge. You can specify a PDF variable, a cfdocument variable, or the pathname to a file." This would indicate it is just...
Read more