When writing complex sproc’s in T-SQL you can sometimes run into the problem that IDEs and other tools, while trying to parse the query, are unable to realise that the query actually works.  This is because they try and interpret the query to get the metadata (the format of the result, rather than the result itself), rather than execute it – not such a bad thing since you don’t want an IDE to execute a sproc willy-nilly, which could result in all sorts of side effects.

The main place where developers will encounter this is when trying to configure a TableAdapter (or something) that uses temp tables.

Consider the following sproc:

CREATE PROCEDURE [dbo].[DoSomething]

AS

CREATE TABLE #MyTable (Id int, Name nvarchar(50))

INSERT INTO #MyTable

VALUES(1,'Name1')

SELECT * FROM #MyTable

Executing this procedure via SQL renders the expected result as follows:

(1 row(s) affected)

Id          Name

----------- --------------------------------------------------

1           Name1

(1 row(s) affected)

However if you try and create a TableAdapter in Visual Studio using the same sproc, you get an error that looks something like this:

The short solution is not to use temp tables, but rather table variables – which for lots of other reasons is arguably better.  The altered sproc would then look like this:

ALTER PROCEDURE [dbo].[DoSomething]

AS

DECLARE @MyTable table (Id int, Name nvarchar(50))

INSERT INTO @MyTable

VALUES(1,'Name1')

SELECT * FROM @MyTable

That is fine, but occasionally you may need to use temp tables and developers land up creating ‘permanent’ temp tables or some other approach.  The question remains, how can we force Visual Studio to ignore the metadata and actually execute the sproc?  In some cases temp tables may be unavoidable – particularly if you are using someone else’s sproc.  The quick solution is to use the SET FMTONLY statement which when turned off would seem to return data as well as metadata to the client.  The SQL Books say the SET FMTONLY ‘Returns only metadata to the client.’  I am making the assumption therefore that setting it to ‘OFF’ returns something else as well – I suspect it is the data.

So, to use a sproc that uses temp tables in Visual Studio, the sproc could be changed to:

ALTER PROCEDURE [dbo].[DoSomething]

AS

SET FMTONLY OFF

CREATE TABLE #MyTable (Id int, Name nvarchar(50))

INSERT INTO #MyTable

VALUES(1,'Name1')

SELECT * FROM #MyTable

… making Visual studio happy.

Interestingly, SSIS (SQL Server Integration Services) has an additional quirk.  Firstly, if you use a temp table without turning FMTONLY to OFF, and write a source query of ‘EXEC DoSomething’ you receive an error message ‘This SQL statement is not a query’ – SSIS doesn’t even parse your carefully constructed SQL.  You know that

1. It works in the query analyser
2. It is a query
3. You are more intelligent than your PC, so if you know it is a query then it must be a query.

The best way to convince SSIS that the query is actually a query is to insert the SET FMTONLY OFF line into the sproc (without changing the source query that SSIS uses – see it was a query!).  You may think that the solution is the same as with Visual Studio (use table variables), but SSIS was written by some other development team and has a different attitude and personality.

If you execute a sproc with table variables or temp tables with FMTONLY OFF you get something like the following:

If you look carefully, the ‘Executing’ successful (is a valid query) but the ‘Pre-Execute’ fails.  I tried to turn off the pre-execute as an option in the designer with no success.  The quick answer is to add SET NOCOUNT ON to the sproc, which for some reason lets the pre-execute run successfully.  I am not sure why, the SQL Books say that NOCOUNT ‘Stops the message that shows the number of rows affected by a Transact-SQL statement from being returned as part of the results’, which doesn’t seem relevant to pre-execute – but it does work.  The query would then be changed to:

ALTER PROCEDURE [dbo].[DoSomething]

AS

SET FMTONLY OFF

SET NOCOUNT ON

CREATE TABLE #MyTable (Id int, Name nvarchar(50))

INSERT INTO #MyTable

VALUES(1,'Name1')

SELECT * FROM #MyTable

Or, if using table variables:

CREATE PROCEDURE [dbo].[DoSomething1]

AS

SET NOCOUNT ON

DECLARE @MyTable table (Id int, Name nvarchar(50))

INSERT INTO @MyTable

VALUES(1,'Name1')

SELECT * FROM @MyTable

In summary:

1. Rather use table variables than temp tables
2. When using temp tables use SET FMTONLY OFF
3. When using SSIS, use SET NOCOUNT ON
4. If SSIS says your query is not a query it may be trying to fool you.

Simon Munro

I have been looking at parts of SQL Server 2005 since the June CTP and been implementing SQL 2005 in production and development environments since December last year.  The difference between investigating the features and doing an implementation is that in an actual implementation you use the most practical and useful features, rather than the cool features that you may use one day in some future architecture.

SQL Server Reporting services is cool and useful - but is a separate bundled package - so I consider it a new product, not a new feature.  Similarly with SQL Server Integration Services - a great step in the direction of visual programming and DSL's (Domain Specific Languages).  Other features are intriguing and I intend to put them to use on some future architecture; the Service Broker, Database Mirroring and CLR Integration are some of the features that pique my interest.

Currently there are two things that I used immediately and so extensively that I almost can’t imagine what it was like developing for SQL Server a few months ago – those two are Schemas and Error Handling.  I would have to say that the addition of try-catch block has made the biggest difference.

T-SQL is often viewed as a second-rate language compared to C#, Java or other modern (or at least Object Oriented) languages.  However the set-based bias of T-SQL has always meant that a lot of functionality can be developed in stored procedures – some would argue too much.  The addition of the try-catch block brings T-SQL closer to being a more elegant and usable language.  Handling errors, particularly within the scope of transactions was difficult and cumbersome prior to SQL2005 – the need to inspect @@ERROR after every statement meant that the code was either riddled with too many error checks and conditional statements to exit elegantly or had insufficient error handling that would leave data in an inconsistent state.

Although not necessarily a candidate for a ‘best practice’ award, I have a chunk of boilerplate code that I put in virtually every stored procedure – allowing me to handle errors gracefully and to roll back transactions.  This code even reports the exact line number that an error occurred (including errors that occur in triggers or nested stored procedures)

BEGIN TRANSACTION

BEGIN TRY

  -- Do stuff

END TRY

BEGIN CATCH

  IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION

  DECLARE @ErrorMessage nvarchar(4000)

  DECLARE @ErrorSeverity int

  DECLARE @ErrorState int

  SELECT

      @ErrorMessage = N'Error in '+ERROR_PROCEDURE()+N' (Line '+CAST(ERROR_LINE() AS nvarchar(20))+N') : '+ERROR_MESSAGE(),

      @ErrorSeverity = 1,

      @ErrorState = ERROR_STATE()

  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

IF @@TRANCOUNT > 0

  COMMIT TRANSACTION

It may not look like much and there are far more sophisticated uses of exception handling, but putting the above code into your stored procedures will increase the quality of your code and the integrity of your database by orders of magnitude for virtually no effort.

Simon Munro

Virtually every architect has wrestled with problem of where to put ‘Business Logic’ and as technology changes and matures I doubt there will ever be a correct answer.  The rules stating that ‘business logic’ should always go here or there are stated by people who would only be able to depict application architecture with a set of crayons and a colouring-in book.   I have seen applications where none of the 'business logic' is in the database and a whole lot of wrapper classes do everything that a database would do (such as enforce foreign key constraints) - to the other extreme where virtually the entire application is developed in the database.  As with everything in software architecture, you need to weigh the pros and cons that are specific to your application.  So some pointers:

• Define what you mean by 'Business Logic' - this means many different things to different people and it is a good idea to establish some sort of baseline.  One could argue that tables in the database, by virtue of the fact that they represent business semantics, are in fact business logic.  Generally 'Business Logic' refers to some sort of procedural programming, but you would be well advised to define, agree and document what functions your business logic performs.  You will probably find that 'business logic' spans all layers and technologies - you have to understand what type of business logic goes where.
• RDBMS's do perform some 'business logic' incredibly fast and it would be pointless to try and 'roll your own' mechanism in another layer.  Things that are best left to the database include enforcing referential integrity, ensuring primary keys are unique etc.
• Most native database languages (by this I refer to PL/SQL & T-SQL - not Java or .NET in the database) execute extremely fast and almost always faster than an external language.  There are some cases that you can see a performance benefit in external applications, but this is usually for bulk operations where plenty memory is available - where you can suck lots of data out, work with it in memory and dump records into the database in one go.  As a .NET or Java developer, you will almost always lose an argument if performance is an issue.
• Database vendors are trying to turn their databases into application servers and referring to 'the Oracle database' actually refers to more than just the RDBMS.  SQL2005 has a 'Database Engine' (the RDBMS) as well as 'Reporting Services', 'Integration Services' etc. which, although bundled with the database are not really part of the RDBMS.  So when assessing the 'extended' functionality that is offered such as providing 'Business Object Services' out of the database, think carefully that this is not the core of the database and the suitability of the database platform for providing the architectural framework needs to be assessed as if it was any other (non-database) technology.  My favourite example is a database 'feature' of being able to send email from within database stored procedures; not RDBMS functionality at all!  Obviously database vendors want to sell more product and create tie-in to their platform, particularly Oracle who uses the well establish RDBMS brand to punt the rest of their products - be wary of database vendor marketing material.
• On the newer database platforms you can run Java or .NET code that is executed by the database.  This is useful in some cases but mostly it is unnecessary because the native languages a frikkin' fast by comparison as the engine has been coded up over the years with them in mind, and the set-based paradigm that we are used to in database languages doesn't translate all that well with more object oriented constructs.  Mostly you would use this for accessing some external functionality.  Also, the database has less control over these types of applications and badly coded .NET or Java on the database server can bring it to its knees.  In SQL2005 the CLR actually runs inside the SQL memory space and processes and is managed by the database engine - allowing the database itself to prioritise, allocate memory or even kill rogue tasks in order to satisfy the real RDBMS operations that are being performed.  I know that Oracle9i spawned off JVM's that were in control of the OS, not the database - I don't know if this has changed in 10g.
• Lastly - make sure that you are consistent in where you put business logic.  Often the biggest problem is that in one module a type of business logic is in one place and in and in another module it is elsewhere making it difficult to maintain, debug and migrate.  Wherever you decide to put the business logic (or parts of it), make sure that it is consistent across the project.

This question has been around for a long time and will there will never be an answer that applies across the board.  Like everything in systems architecture, you have to look at the specifics of your project to come up with the best approach.

Simon Munro