Simulating TRY_CONVERT() in SQL Server 2008 – Part 2

Problem

In my last tip, I talked about ways to simulate the function TRY_CONVERT() when you’re on an older version of SQL Server. In that tip I showed some scalar-valued functions, each targeting a very specific data type, that could be used when working against a set. But I thought I’d also share a way to dynamically handle conversions to a wide variety of types – the catch being that it can’t be done inside a function (“dynamically” is the hint there).

Solution

First, let’s assume you have some entity-attribute-value (EAV) setup, like a configuration table.

CREATE TABLE dbo.Configurations
(
  Setting sysname PRIMARY KEY,
  DataType varchar(32) NOT NULL,
  Value nvarchar(4000) NOT NULL
);
INSERT dbo.Configurations(Setting,DataType,Value) VALUES
(N'ServerName',      'sysname', N'HONEYBADGER'),
(N'AppTimeout',      'int',     900),
(N'FounderBirthday', 'date',    '19740201');

Now, let’s assume you have an application that absolutely needs a strongly-typed column in the output, and expects to call a stored procedure and get the single configuration value – with the right type – in the only output column. Without that requirement, you could just do this:

CREATE PROCEDURE dbo.GetConfigurationValue
  @Setting sysname
AS
BEGIN
  SET NOCOUNT ON;
  SELECT Value 
    FROM dbo.Configurations
    WHERE Setting = @Setting;
END
GO

But, as developers have told me, the receiving code can’t just swap out that data type on the fly. There was definitely some talk about reflection and strong typing, and my eyes glazed over. The problem they needed to solve is that Value needed to be dynamically typed to the right data type, so when they were asking for AppTimeout, they would get an int, and when they were asking for FounderBirthday, they would get a date.

There are a couple of ways to accomplish this without TRY_CONVERT() and, in fact, even with TRY_CONVERT(), it would have to be dynamic, since the first argument can’t be a variable or parameter.

Simulating TRY_CONVERT() – Dynamic SQL Option #1

To facilitate roughly what TRY_CONVERT() does, the point is to generate a column with the right data type, but also to avoid raising an exception if the value stored doesn’t match the defined data type. You can accomplish this by using dynamic SQL and wrapping an explicit attempt to convert a variable in <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql" target="_blank" rel="noopener"> TRY / CATCH</a> (introduced in SQL Server 2008).

CREATE PROCEDURE dbo.GetSetting_Option1
  @Setting sysname
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @DataType varchar(32), 
          @Value    nvarchar(4000), 
          @sql      nvarchar(max);
  SELECT @DataType = DataType, @Value = Value
    FROM dbo.Configurations
    WHERE Setting = @Setting;
  -- try assigning to a variable of the right data type
  -- if it works, select it
  SET @sql = N'DECLARE @t ' + @DataType + N' = @v;
      SELECT Value = @t;';
  BEGIN TRY
    EXEC sys.sp_executesql @sql, N'@v nvarchar(4000)', @Value;
  END TRY
  BEGIN CATCH
    -- if it doesn't work, strongly type NULL
    SET @sql = N'SELECT Value = CONVERT(' + @DataType + N', NULL);';
    EXEC sys.sp_executesql @sql;
  END CATCH
END
GO

Simulating TRY_CONVERT() – Dynamic SQL Option #2 – SQL Server 2012+

This one assumes you are on a more modern version of SQL Server, and can use TRY_CONVERT(). It simply builds the dynamic SQL statement to select from the table by first selecting from the table:

CREATE PROCEDURE dbo.GetSetting_Option2
  @Setting sysname
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql nvarchar(max);
  
  SELECT @sql = N'SELECT Value = 
      TRY_CONVERT(' + DataType + N', Value)
    FROM dbo.Configurations
    WHERE Setting = @Setting;'
  FROM dbo.Configurations
  WHERE Setting = @Setting;
  EXEC sys.sp_executesql @sql, N'@Setting sysname', @Setting;
END
GO

That is just to demonstrate that even with newer functionality there still needs to be some dynamic elements built in in order to meet the application requirements of strongly-typed output columns.

Testing

You can test how either solution works by inserting a “broken” value in the Configurations table:

INSERT dbo.Configurations(Setting, DataType, Value)
  VALUES(N'Broken', 'int', N'foo');

And then just try:

EXEC dbo.GetSetting_Option1 @Setting = N'Broken';
EXEC dbo.GetSetting_Option1 @Setting = N'FounderBirthday';
EXEC dbo.GetSetting_Option2 @Setting = N'Broken';
EXEC dbo.GetSetting_Option2 @Setting = N'FounderBirthday';

Next Steps

Read on for related tips and other resources:

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *