Testing whether a SQL statement is valid - SQL Server 2000

[InvisibleDuncan]

I have a bit of an odd problem. Within our SQL Server 2000 database, we have a table that holds default values for given IDs. However, in some circumstances, the default value held in the table will actually be a SQL command. For example:

DatePart(YYYY, GetDate())

Currently, this is all handled within the VB application that calls the SP that looks at this table; within a Try Catch block, it tries to run the SQL command, and if it returns a value rather than an error, it uses the returned value rather than literally putting the text of "DatePart(YYYY, GetDate())" as the value.

The VB code looks like this, where RunSQLGetString is a function that runs the SQL against the database:

                        Dim DefaultValue As String
                        Try
                            DefaultValue = RunSQLGetString("Select " & Attribute.DefaultValue)
                        Catch ex As Exception
                            DefaultValue = Attribute.DefaultValue
                        End Try

I would rather handle this in the SQL stored procedure, but I'm not sure of the best way to go about it. I really don't like the idea of using error handling for coding purposes, and besides, as it's SQL 2000, I'm limited to using @@Error:

    Select @Default = IsNull(DefaultValue,'') From Attributes Where AttrID = @AttrID
    If IsNull (@Default, '') <> ''
        Begin
            Insert Into #Converted
            Execute ('Select ' + @Default)
            If @@Error = 0
                Begin
                    Set @Default = (Select Top 1 Converted From #Converted)
                End
            Select  @AttrValue  =   @Default
        End

Can anyone see a tidier way around this?

[szlamany]

If you use SELECT like that what is the difference between

SELECT 123

and

SELECT DatePart(yyyy,GetDate())

Won't it always work now - regardless??

OR are you saying you might have XYZ and

Select XYZ

Does not work?

Are you stuck with the default data the way it is - or could you maybe change DatePart(yyyy,GetDate()) to =DatePart(yyyy,GetDate()) and then check for the string starting with the "="?? That would remove your reliance on a error telling you how to proceed (which is very odd way of doing something I must say )

[techgnome]

WAy I read it is that
SELECT 123 - would be valid
SELECT DatePart(yyyy,GetDate()) -- also valid....
but
SELECT DatePart(yyyy,G3tDate())
wouldn't be? sounds like the value being selected could be a value or an expression... we do that same kind of thing in our app too... you can set the property of an item, like the Visible property, to True, False or an expression... as long as that expression resolves to True or False. But we also do things like that for captions and other things.

At any rate, I don't really see an issue... I'd probably would encapsulate that into a scalar function...

	DECLARE @AttrValue int
	DECLARE @Default nvarchar(max)
	DECLARE @SQL nvarchar(max)
	DECLARE @PARAMS nvarchar(max)

    Select @Default = IsNull(DefaultValue,'') From Attributes Where AttrID = @AttrID
	set @SQL = 'SELECT @AttrValue = ' + @DEFAULT 
	set @PARAMS = '@AttrValue int'
	exec sp_executesql @query = @SQL, @PARAMS = @PARAMS, AttrValue = AttrValue output

	-- Return the result of the function
	RETURN @AttrValue

Assumed that @AttrID was a parameter passed in...

adjust as needed....
I *think* that will work... it's untested.

-tg

[InvisibleDuncan]

Thanks for the responses, guys. I should have been clearer in my original post, but yes: the values could be "DatePart(yyyy,GetDate())", "123", "XYZ" or even "Fred was here". It's options 3 & 4 that break it.

szlamany - I wish I could change the way it's set up, but I'm unfortunately stuck with the data as it is to avoid the potential of breaking it on the VB side.

TG: Your suggestion (with a minor syntax tweak) works for the values that resolve properly (i.e. examples 1 & 2 above), but falls over with the text fields. Are you suggesting that I wrap this in a function that I expect to fail, and handle it in the calling procedure?

[FunkyDexter]

Apologies if I'm a little off topic here and possibly teaching you to suck eggs but this sounds like you're trying to implement Key Value Pairs in a relational database. If that's the case and if you're still in a position to head this off at the pass then I'd recommend that you don't do this. I've spent the last 3 years trying to implement this satisfactoraly in a home spun CRM/Marketting package I've been working on and I can tell you from bitter experience that it's a total nightmare and the worst thing is you won't understand why until quite a long way into the project when you start working with large scale data retrieval and updating. Here's a blog that explains it pretty well and also gives some decent alternatives:-
http://www.sturnus.co.uk/performance...me-value-pair/

If you are too late and are stuck with this then I'd recommend that you don't start trying to move the data access logic into sprocs. Once youre in the world of KVP you're no longer dealing with relational data and SQL just isn't very good at dealing with it. Instead I'd recommnd that you keep a data access layer written in code (I made a pretty good stab at it using a c# layer) but be prepared for alot of pain because you can no longer rely on all the good DBMS ACID stuff that we all take for granted - you can't even rely on locking to make an update atomic so you have to write the lot yourself.

Personally I've just started experimenting with a NoSQL aproach using MongoDB on the basis that KVP is fundamental to the way it works anyway so it should be much better at coping. I haven't really got far enough to know how viable it is as an aproach yet though.

Of course, if this is a complete red herring and what you're doing is nothing to do with KVP then great and forget I spoke. The only useful thing I could add to what Steve and TG have already said is a bit philosphical: there is a difference between error handling and exception handling. An error is something that shouldn't happen, an exception is something that you'd expect to happen but is unusual. I would argue that what you've done in your original post is actually exception handling and perfectly acceptable; you're dealing with a situation you are fully expecting to occur from time to time. It's just unfortunate that the flag in SQLServer is called @@erroor which tends to lock you into an "Error Handling" way of thinking.

[InvisibleDuncan]

Thanks for the advice, Funky. As it happens, this isn’t a KVP implementation. The default value that I’m concerned with is just one of many fields on this table; it’s simply that it’s the one that’s causing me problems. This is also a very old, very large system where I can’t get in and redesign things; if I could, I’d probably split it out into two fields – one for a default calculation and one for a default literal – and work around it that way.

The actual problem I have is that I need to add a couple of new modules that both refer to this default setting, and I don’t want to have to add that VB check in each time. I’d much rather have the database do it and then pass me the correct value so that I can use my standard data access layer without that extra step of seeing whether it needs to be converted.

I see what you mean about allowing for exceptional events, and I might just be being precious about it. There’s also a practical issue, though – the errors get passed back up to the VB.Net code. Mind you, I think that might be because I’ve messed up the structure of my error handling in the SP

[FunkyDexter]

this isn’t a KVP implementation

Then forget I even brought it up

Looking at your original problem, I really can't think of any way of doing it that would be more efficient than the "catch a fail" method you originally suggested.