Simple Server Script Thoughts

Server scripts are handy things.

We use them frequently, often for any type of interaction between the data and the UI that you can think of and toss into starlims.  Need to commit some new information to your database tables?  Run a server script with some SQL insert/update statements.  Pull information?  The same, just with Select statements.

Server scripts are wonderful and handy but require a little care and feeding when they are set up.  People tend to fall into two groups when they use them:  either set them up as single activity, e.g. the purpose of the server script is to handle a single update/select/insert/create or a singular activity: a couple of SQL commands centered around processing data to come to a conclusion --like performing checks based on provided data and then performing an update or series of updates; or to build complex multifunctional scripts, often with DoProc() statements to handle process flow.

Neither approach is good/bad or even better than the other: it really depends on your programming goal and philosophy.  I can't say I've noticed any performance impacts taking one approach over the other, though I do find the highly complex server scripts to be harder to code review and debug.

So, in the path of building your server scripts, let me put out a few things for you to keep in mind.

Don't make them brittle

Changes in your tables can break even the most well-written scripts.  Even small changes have large impacts, like setting a column to suddenly be non-nullable or to have a different value.  You can code for some of these potential issues by making great use of parameters.  In fact, for simple updates or data pulls via select statements, I highly suggest you create a flexible server script which accepts an array of column names, an array of values, and a tablename.  Having a script like this in hand allows you to utilize it to handle any single table and juggle updates, selects or insert statements.   You can code to read the values in the arrays of each and then build the SQL statement appropriately.  If changes to the tables are a concern, use some preliminary scripting to check:

arrCols := sqlExecute("SELECT * FROM sys.columns WHERE object_id = object_id('dbo.Tablename')");

That will give you the list of columns in the table.  Don't forget to use your table's name and not the placeholder I put in there. 

If you think nullable might be an issue for a particular column, you could query to find out:

arrIsNullable := SqlExecute("select IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo' AND TABLE_NAME='Tablename' and COLUMN_NAME='Columnname'");

Obviously you need to plug in your table and column.

Think Security

It doesn't show up out of nowhere.  Even though you are programing in system and not wide open to the world, you need to keep in mind what your server scripts are updating, especially if you have layers of admins who are potentially coding.  It may not be in your best interest to let everyone code to access the underlying tables.  You could enforce some protection by creating views that contain the columns you only want exposed and have those accessed instead.  Also, resist concatenating things together.  It can leave you open to SQL injection attacks.  Use parameters instead.

Write Maintainable Code

Focus on writing code that will age well and be understandable a year after you crafted it.  Worry less about about performance and focus on well factored code.  If performance is a requirement or a problem, analyze your code to find the trouble spots and optimize them.

Also, remember when I mentioned you might considering using a flexible server script and passing columns by parameters?  Keep in mind that only 15% of an application's life is spent in development and the rest is in maintenance.  That means you need to make your job easier since the code you right today is going to be around plaguing you for years to come.  Using parameters instead of passing columns and values means when you update your table with something new, you only need to adjust code in one area instead of re-writing in multiple ones.