SQL Server 2008 Table-Valued Parameters and NULL

May 4 2009
Comments (0)

So it turns out table-valued parameters can be optional with no problem. But it’s non-standard syntax. Normally, you’d have this:

create procedure myProc @Param int = null

The "= null" tells us it’s optional.

With table-valued ones, you have to specify “readonly” in any case (not related to optionality) and they are always also optional ... without specifying "= null". Like this:

create procedure myProc @TblValParam MyTableType readonly

This works and the @TblValParam does not have to be referenced during the call. It seems, conceptually, that the “table value” is always implicitly there, but if not passed in will just contain zero rows. You can still "select *" from it and get the header. Makes sense.

Treating it like a normal parameter, like this:

create procedure myProc @TblValParam MyTableType = null readonly

... gets you this fun error:

   Operand type clash: void type is incompatible with MyTableType

Fun.

I love when Microsoft products are internally inconsistent. (And by "love" I mean “hate with a passion.”)

MS SQL Server

Testing for Failure in Database Unit Tests

May 28 2008
Comments (0)

(Using Visual Studio Team Suite edition for Database Professionals. A.k.a. “data-dude” because someone at Microsoft forgot to drink their creative juice that morning.)

Although I enjoy the unit-testing features of Visual Studio "Database Professional" edition (or whatever they're calling it these days), it is a ghastly oversight that you can't easily test for a procedure producing a specific expected error result.

Instead, if the database code itself experiences an error, the whole test blows chunks—that is, it fails. No matter if it got exactly the error you expected it to. I concede it's (all too) common to have little/poor/no error handling in stored procedures—but come on Microsoft! Let's encourage better practices.

I would expect to have one of the "Test Conditions" be for checking that a specific error code was produced. This would be about the 2nd most useful one (after checking for a Scalar value, which handles just about everything anyway).

(Yeah, I know, it's "extensible" and I'm free to spend a bunch of time developing my own Test Condition and plugging in it and sharing that code with everyone on the planet. But seriously? Who buys this latest MS excuse for not thinking about how to build what the community needs in the first place?)

<sigh>

So an aha! moment hit the other night which led me to this simple workaround. Let's use the trusty Scalar Value test condition combined with a TRY/CATCH block within the SQL code to test itself. Like this:

begin try
    exec @rc = dbo.SomeProcedure @Param='some value';
    select @@error as ErrorNbr;
end try

begin catch
    select @@error as ErrorNbr;
end catch

Then, we just wire up a Scalar Value to test the 1st column / 1st row for the specific Error Number you expect this to generate.

(Don't get me started on using ordinal row/column numbers for this—wow, MS has completely lost sight of what relational DBs are supposed to be!)

On my current project, we're returning a specifically formatted XML string from database procedure errors, which include a number of fixed attributes we then parse on the client. Works great. But it has a side effect of all custom-raised errors being #50000. So in this case I'm using a slightly more complex way of testing for expected errors in the CATCH part:

begin try
    exec @rc = dbo.SomeProcedure @Param='some value';
    select @@error as ErrorNbr;
end try

begin catch
    declare @errmsg xml; set @errmsg = cast(error_message() as xml);
    select @errmsg.value('(//@ErrorNbr)[1]', 'int') as ErrorNbr
end catch

Happy error hunting!

Software Engineering, MS SQL Server ,

Cast ‘True’ as Bit

May 7 2008
Comments (0)

Okay, I'm a bit slow on the uptake. It took the SQLServerCentral.com question of the day to point this out to me...

Apparently MS made the values "true" and "false" implicitly convertible to BIT (as 1 and 0 respectively) starting with SQL 2005. For many years I've defined variables in stored procedure scripts that emulate the constants TRUE and FALSE... Something like this:

declare @TRUE bit; set @TRUE = 1;
declare @FALSE bit; set @FALSE = 0;

But now, instead, I can just directly reference 'true' or 'false' where necessary, such as in a where clause. For example:

declare @t table (ID int not null, IsFlagged bit not null);
insert @t
      select 1, 0
union select 2, 1
union select 3, 1
;
select count(*) from @t where IsFlagged = 'true';

Or, more plainly:

select cast('true' as bit) as Result;

Of course, this isn't true Boolean type support. And don't get me started on the truly relational equivalents of TRUE and FALSE. But at least it's a step closer. Certainly less typing.

Also note: it does not work to convert directly to INT types though, only the BIT type. Even though BITs are theoretically a sub-type of the general Integer variable class.

MS SQL Server